Excel Import from Text (via Microsoft Query) Date Field Problem

[mjseim]

Greetings,

I have an Excel workbook that uses a flat TXT file as it's source. I don't want to bring in all the raw data as a worksheet to save space. Instead, I'm bringing it in as PivotCache which is so wonderful and saves a great deal of space. To do so, I'm bringing in the data via "Data > Get External Data > From Other Source > Microsoft Query > Driver=TextDriver".

This method works so well. It allows me to specify file format specifications including field names, file layout, delimiter, field types. That is, except for one thing... Dates are causing me enormous headaches.

The flat file is sourced from an Oracle server. I've tried all the following methods of returning dates but none are treated as a DATE field in the PivotCache. I know this is the case because when I perfom a PivotTable MAX function on this field it returns 1/1/1900. Not good.

The methods I've tried include:

CREATEDATE
as DATEFIELD01Raw
--Example: 3/15/2006 9:37:48 PM

, TRUNC(CREATEDATE)
as DATEFIELD02TimeDropped
--Example: 3/15/2006 12:00:00 AM

, TO_CHAR(CREATEDATE,'yyyy-MM-dd')
as DATEFIELD03YYYYMMDD
--Example: 2006-03-15

, TO_CHAR(CREATEDATE,'MM/dd/yyyy')
as DATEFIELD04DDMMYYYY
--Example: 01/07/2006


Has anyone every dealth with this before? Thanks everyone.

 

[mjseim]

Akkk.... sorry everyone. I figured it out. Two things mattered:

1) The format seemed to insist upon:
, TO_CHAR(CREATEDATE,'MM/dd/yyyy') as DATEFIELD04DDMMYYYY --Example: 01/07/2006


2) I wasn't clicking MODIFY during the Text Import Driver Setup. That is, I was changing the "Guessed" value from CHAR to DATE but since I wasn't hitting "Modify" the change wasn't being saved.

With those two things figured out, it's working great.

Sorry for the bloat but hopefully this will help others out one day.

Take care.

Greetings,

I have an Excel workbook that uses a flat TXT file as it's source. I don't want to bring in all the raw data as a worksheet to save space. Instead, I'm bringing it in as PivotCache which is so wonderful and saves a great deal of space. To do so, I'm bringing in the data via "Data > Get External Data > From Other Source > Microsoft Query > Driver=TextDriver".

This method works so well. It allows me to specify file format specifications including field names, file layout, delimiter, field types. That is, except for one thing... Dates are causing me enormous headaches.

The flat file is sourced from an Oracle server. I've tried all the following methods of returning dates but none are treated as a DATE field in the PivotCache. I know this is the case because when I perfom a PivotTable MAX function on this field it returns 1/1/1900. Not good.

The methods I've tried include:

CREATEDATE
as DATEFIELD01Raw
--Example: 3/15/2006 9:37:48 PM

, TRUNC(CREATEDATE)
as DATEFIELD02TimeDropped
--Example: 3/15/2006 12:00:00 AM

, TO_CHAR(CREATEDATE,'yyyy-MM-dd')
as DATEFIELD03YYYYMMDD
--Example: 2006-03-15

, TO_CHAR(CREATEDATE,'MM/dd/yyyy')
as DATEFIELD04DDMMYYYY
--Example: 01/07/2006


Has anyone every dealth with this before? Thanks everyone.