Reading DateTime from csv file

[#1]

Hello, a newbie here...

I'm reading a csv file containing a column with DateTime information, but Qlik doesn't accept it as DateTime, this is an example of the data:

"Fri Aug 17 02:00:00 CEST 2012"

"Wed Aug 01 02:00:00 CEST 2012"

"Sat Aug 04 02:00:00 CEST 2012"

How can I get this data accepted as DateTime?

Thanks in advance

Cheers

Francisco

[tresesco]

May be like this?

Load

          TimeStamp(MakeDate(Right(YourField,4),Num(Month(Date#(Mid(YourField,5,3),'MMM'))),Mid(YourField,9,2)) + Time#(Mid(YourField,12,8),'hh:mm:ss')) as DateTime

from <>;

I guess some simpler expression can be worked out though.

[#3]

Thanks for your help.

Cheers

[#4]

Hi,

If you find the solution, close the discussion by marking the comment as answer.

[#5]

Well, I see that the information that I sent in the first post in this thread was not enough to obtain an answer that I can use due to my lack of experience:

Really the data in my CSV file is a crosstable:

[header 3 lines]

Hora/Fecha,"15MT3 (121)","15MT3 (122)"

Wed Aug 01 02:00:00 CEST 2012,"113.1","84.1"

Thu Aug 02 02:00:00 CEST 2012,"116.1","84.8"

Using the wizard I could unpivot and load the data quite well skipping the 3 first lines (header) and using the data in the  4th line as column names , but the first column Hora/Fecha in english (Time/Date) is used as a string, and I don't know how to modify the wizard generated script using the information that tresesco kindly provided.

The script generated by the wizard is:

SET DecimalSep='.';

SET MoneyDecimalSep='.';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

CrossTable(Inversor, kWh)

LOAD [Hora/Fecha],

     [15MT3 (121)],

     [15MT3 (122)]

FROM

(txt, utf8, embedded labels, delimiter is ',', msq, header is 3 lines);

[Clever_Anjos]

LOAD

datetime#([Hora/Fecha],'WWW MMM DD HH:MM:SS GMT YYYY') as [Hora/Fecha]

from file.csv

[rbecher]

This seems to work as long as you have only one timezone (CET/CEST):

=timestamp(timestamp#(mid(replace(replace(<Field>, 'CEST ', ''), 'CET ', ''), 5), 'MMM DD hh:mm:ss YYYY'))

If you have to handle more timezones you will need more replace() actions..

- Ralf

[#8]

where should I insert your code?

[rbecher]

In script:

MyTable:
LOAD
timestamp(timestamp#(
     mid(replace(replace(<Field>, 'CEST ', ''), 'CET ', ''), 5), 'MMM DD hh:mm:ss YYYY')) as MyField  
FROM MyFile.csv (...);

- Ralf