topic Re: Reading DateTime from csv file in QlikView

Reading DateTime from csv file

Wed, 25 Sep 2013 03:59:37 GMT

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

Re: Reading DateTime from csv file

tresB — Wed, 25 Sep 2013 04:44:06 GMT

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.

Re: Reading DateTime from csv file

Fri, 27 Sep 2013 04:52:38 GMT

Thanks for your help.

Cheers

Re: Reading DateTime from csv file

Fri, 27 Sep 2013 05:17:54 GMT

Hi,

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

Re: Reading DateTime from csv file

Fri, 27 Sep 2013 14:31:46 GMT

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);

Re: Reading DateTime from csv file

Clever_Anjos — Fri, 27 Sep 2013 14:37:06 GMT

LOAD

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

from file.csv

Re: Reading DateTime from csv file

rbecher — Fri, 27 Sep 2013 15:05:12 GMT

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

Re: Reading DateTime from csv file

Fri, 27 Sep 2013 16:11:45 GMT

where should I insert your code?

Re: Re: Reading DateTime from csv file

rbecher — Fri, 27 Sep 2013 18:41:56 GMT

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