Crosstable load with dates

[#1]

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

[giakoum]

SET DateFormat='DD.MM.YYYY';

include this before the load statement

[swuehl · Accepted solution]

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;