topic Re: Issues converting date from a downloaded .csv in App Development

Issues converting date from a downloaded .csv

__dec___ — Thu, 15 Dec 2022 15:19:45 GMT

I am attempting to change the date format of a date in a .csv file downloaded from format: 01-Dec-2022 08:00 to 01/12/2022 08:00 (DD/MM/YYYY HH:MM)

I have tried a few different conversations but cannot convert - to /

Below is the script I am using in Qlikview:

LOAD

date#(date, 'DD/MM/YYYY HH:MM') as [Date],

rain,
temp,

wetb,
dewpt,
vappr,
rhum,
msl as 'Mean Sea Level Pressure hPa',

wdsp,

wddir
FROM
[https://cli.fusio.net/cli/climate_data/webdata/hly1875.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 17 lines);

Re: Issues converting date from a downloaded .csv

hic — Thu, 15 Dec 2022 15:42:32 GMT

Try

TimeStamp(TimeStamp#(date, 'DD-MMM-YYYY hh:mm'),'DD/MM/YYYY hh:mm') as TimeStamp,
Date(Floor(TimeStamp#(date, 'DD-MMM-YYYY hh:mm')),'DD/MM/YYYY') as Date,

Re: Issues converting date from a downloaded .csv

__dec___ — Thu, 15 Dec 2022 15:51:09 GMT

That is exactly what I needed. Thanks a lot for the help!

Re: Issues converting date from a downloaded .csv

brunobertels — Thu, 15 Dec 2022 15:57:12 GMT

Try this

timestamp#(date(date#(subfield('01-Dec-2022 08:00',' '),'DD-MMM-YYYY'),'DD/MM/YYYY')&' '&Time(time#(right('01-Dec-2022 08:00',5),'hh:mm'),'hh:mm'),'DD/MM/YYYY HH:MM')

should give :

01/12/2022 08:00

Re: Issues converting date from a downloaded .csv

Dianah — Fri, 16 Dec 2022 07:17:53 GMT

Instead, this is due to the way Excel and other spreadsheet programs open a CSV file and display the data therein. Basically, spreadsheet programs are designed to be used for calculation purposes, so they tend to apply mathematical formats to numbers when the CSV file is opened directly into the program.

Regards,

Diana