Excel Source file with Date fields

sonia jacob · Post by **sonia jacob** » Fri Dec 17, 2004 3:27 pm

I have an excel source file with a Date field of the format *3/14/2001. the explanation on * is that
"Date Formats display date and time serial numbers as date values. Except for items that have an (*), applied formats do not switch date orders with the operating system."

The date value as visible in the excel source file is 12/18/2002 but when I view data in Datastage ODBC source its 2000-12-18 00:00:00. So as the explanation says I thought it was something to do with the date format set in the OS of the DS server. But my DS server also has Date set as m/dd/yyyy. So how or from where does DS pick the 'yyyy-mm-dd hh:mm:ss' format?

In another of my source excel files I have a similar date field but the only difference being the first couple of records have blank date values (the format was still *3/14/2001).

The date values as visible in the excel file 11/1/2004 but when I view data in Datastage ODBC source its blank. But when I edited the source file and put a valid date in the first record then I saw all other valid dates available in the source and in yyyy-mm-dd hh:mm:ss format.

Has anybody faced this kind of a situation

and is there a work around to it.

Or does it have to do with the fact that excel is not installed in the DS server?

Thanks
Sonia.

Note : The first file did not show any funny characteristics as in the second, when the first record (s) had balnk dates.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 17, 2004 3:53 pm

You access Excel via ODBC. The ODBC standard (published by Microsoft) specifies that dates are in ISO 8601 format (YYYY-MM-DD) and that timestamps are in ISO 8601 format (YYYY-MM-DD HH:MM:SS).

It is the ODBC driver that is reporting dates in this format. Because it is obliged to, because it follows the ODBC standard!

In Excel for Windows (and Lotus 1-2-3), days are numbered from the beginning of the century; the date serial number 1 corresponds to the date January 1, 1900. [In Excel for Macintosh it's January 1, 1904.] "Date serial number" is how dates are stored internally in Excel; whatever you see is determined by the cell formatting rules (Format > Cells > Date). Times are stored as fractional parts of a day.

You can use Excel functions DATEVALUE and TIMEVALUE to view these raw forms.

In DataStage, if converted to internal format, days are numbered from a different start point; the date serial number 1 corresponds to January 1, 1968.
But this has nothing whatsoever to do with the visible format; again you must be in control of this, using Oconv() or Iconv() functions as appropriate.

sonia jacob · Post by **sonia jacob** » Mon Dec 20, 2004 10:23 am

thanks ray.

But sadly i am still unable to get the ODBC stage to read the valid date values. Given below is the issue.

In another of my source excel files I have a similar date field but the only difference being the first couple of records have blank date values (the format was still *3/14/2001).

The date values as visible in the excel file 11/1/2004 but when I view data in Datastage ODBC source its blank. But when I edited the source file and put a valid date in the first record then I saw all other valid dates available in the source and in yyyy-mm-dd hh:mm:ss format.

As a temporary fix, I do a manual process of sorting the file based on dates in order to get the valid date values as the first set of records; so as to get the ODBC stage to return the right date values.

Is there any other way of reading the .xls file other than using ODBC stage

thanks

PilotBaha · Post by **PilotBaha** » Mon Dec 20, 2004 11:04 am

Any chance they can deliver this file to you in comma delimited text format?

mhester · Post by **mhester** » Mon Dec 20, 2004 11:10 am

Sonia,

I have written a simple VB 6.0 app that can be run as either a before job routine or as a filter to the sequential stage. All it does is dump the .xls as a csv file and then your DS job can read the file as a strictly text file. I have used this simple little app with many customers and it works perfectly and will do what you want.

If you are interested (no install, just an exe) please email me and I will send you the exe and you could give it a try.

Regards,

sonia jacob · Post by **sonia jacob** » Mon Dec 20, 2004 11:12 am

PilotBaha wrote:Any chance they can deliver this file to you in comma delimited text format?

nope.

I was hoping that they could send me the dates as varchar field format instead of date format. But my request is still under review.

ketfos · Post by **ketfos** » Mon Dec 20, 2004 11:46 am

Hi,
Take your Excel file.
Save it as csv file
Read the csv file in the data stage (input)
Use Iconv and Oconv function on the date field before writing to target table.

e.g
If Arg1 = @null
then Ans = @null
Else
Ans = Oconv(Iconv(Arg1, "d/mdy" ), "d-dmy[2,a3,4]")
End

Ketfos