Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
The date on excel is a numeric value.
40452 is 2010-10-01 (YYYY-MM-DD)
While loading the 40452 from the Excel file, how can I load it as a date type value?
Best Regards,
Cassiano
Use "Date(Excel_date_field) AS Date_Field" in your load script.
Hi,
This function is not working.
Attached is the QV file.
Here is the excel file that I'm using.
Here is a link about the excel serial date
http://www.codeproject.com/KB/datetime/exceldmy.aspx
I hope that this can help.
The combination of purgechar and date have worked.
date (purgechar(ExcelDate,'[]'),'DD/MM/YYYY')
Tks,
Cassiano
Hi all
I have the same problem with a crosstab, I can't make a date. Have you a solution:
I have made this script
SourceXLS:
CrossTable(periode, montant, 3)
LOAD catégorie,
[sous catégorie],
détail,
[40179.000000],
[40210.000000],
[40238.000000],
[40269.000000],
[40299.000000],
[40330.000000],
[40360.000000],
[40391.000000],
[40422.000000],
[40452.000000],
[40483.000000],
[40513.000000],
[40544.000000]
FROM
C:\Users\paul\Documents\perso\Comptes.xlsx
(ooxml, embedded labels, table is Comptes, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
));
myDataFile:
NOCONCATENATE
LOAD *
,date#(periode,'MM/DD/YYYY') as myDate
RESIDENT SourceXLS
but it doesm't succedd
Please help me
I had the same situation.
I have loaded the crosstable into a tempo table and made the purgechar...
Table1:
NOCONCATENATE
LOAD *,
date (purgechar(tempDate,'[]'),'DD/MM/YYYY') as Date
RESIDENT temp_Cross;
DROP
TABLEtemp_Cross;
DROP
FIELD
tempDate;
date(num#(tempData,'########'),'DD/MM/YYYY') as date,
the above code will work fine.