Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reading DateTime from csv file

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

8 Replies
tresesco
MVP
MVP

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.

Not applicable
Author

Thanks for your help.

Cheers

Not applicable
Author

Hi,

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

Not applicable
Author

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
Employee
Employee

LOAD

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

from file.csv

rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

where should I insert your code?

rbecher
MVP
MVP

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

Astrato.io Head of R&D