Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to load data from excel by using the crostable load function. My problem is that the dates are not pulled through as dates but as numbers, How do I make it pull through as dates?
the load statement is...
FORECAST:
crosstable (Date, Units)
LOAD
*
FROM
[.\Prepped Files\Forecast\Forecast May2012.xlsx]
(ooxml, embedded labels, table is Sheet1);
I have attached a sample of the data I am tying to import.
Thanks
The CROSSTABLE Load will transform your Date headers into text values, you need a second load to re-interprete as Date numeric values:
FORECAST_TMP:
crosstable (Date, Units)
LOAD
*
FROM
[.\Forecast May2012.xlsx]
(ooxml, embedded labels, table is Sheet1);
FORECAST:
NoConcatenate LOAD
Barcode,
Date(num#(Date)) as Date,
Units
Resident FORECAST_TMP;
drop table FORECAST_TMP;
SET DateFormat='DD.MM.YYYY';
include this before the load statement
The CROSSTABLE Load will transform your Date headers into text values, you need a second load to re-interprete as Date numeric values:
FORECAST_TMP:
crosstable (Date, Units)
LOAD
*
FROM
[.\Forecast May2012.xlsx]
(ooxml, embedded labels, table is Sheet1);
FORECAST:
NoConcatenate LOAD
Barcode,
Date(num#(Date)) as Date,
Units
Resident FORECAST_TMP;
drop table FORECAST_TMP;