Reading Date from MS-Excel

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
vbeeram
Participant
Posts: 63
Joined: Fri Apr 09, 2004 9:40 pm
Contact:

Reading Date from MS-Excel

Post by vbeeram »

Hi,

I am reading date values from Excel file(.csv) data in the mm/dd/yyyy,i used Sqquential file stage to read the data and i have to write this date into UDB DB2.here IConv and Oconv are not working as iam using Parallel jobs.
I used Varchar data type in Sequential file stage.
I used StringToDate function to convert into Date Format.
I used Date data type in DB2 stage ,but not writing into the database.
When i view the data in DB2 stage it's showing like "(3)(1968/01/03)".
It's treating 3rd Jan 1968 as (3)(1968/01/03).As Datastage takes
31 st Dec 1967 as base value with 0(zero),it's converting into
(3)(1968/01/03).

Is there any other functions to convert into date format?
It's urgent ,If anybody give reply i would appreciate them and advance thanks to them.

thanks
l_homme_de_rio
Participant
Posts: 17
Joined: Fri Jul 30, 2004 1:16 am

Post by l_homme_de_rio »

Hi,
Are you using DS Server or PX ???
All The best
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Depending on your DB2 settings you may need to use substring and concatenation techniques to re-arrange the day, month and year components of the date.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
vbeeram
Participant
Posts: 63
Joined: Fri Apr 09, 2004 9:40 pm
Contact:

Parallel jobs

Post by vbeeram »

l_homme_de_rio wrote:Hi,
Are you using DS Server or PX ???
All The best

I am using parallel Extender
richdhan
Premium Member
Premium Member
Posts: 364
Joined: Thu Feb 12, 2004 12:24 am

Post by richdhan »

Hi vbeeram,

Are you specifying the format for the incoming String value in the StringToDate function. Datastage accepts YYYY-MM-DD by default. But your string value is mm/dd/yyyy.

First in the StringToDate function use the format

StringToDate(Incoming String Value:"%mm/%dd/%yyyy")

Secondly instead of loading into DB2 load it into a Dataset and check if the conversion has happened by using the View data option.

Thirdly, if this has happened then you have to make settings in DB2.
Otherwise change the input value to represent YYYY-MM-DD and do the Step 1 using StringToDate with no formatting and do Step 2.

All the best.
--Rich
ram1899
Charter Member
Charter Member
Posts: 41
Joined: Wed Aug 04, 2004 11:46 am

Re: Reading Date from MS-Excel

Post by ram1899 »

vbeeram wrote:Hi,


I am reading date values from Excel file(.csv) data in the mm/dd/yyyy,i used Sqquential file stage to read the data and i have to write this date into UDB DB2.here IConv and Oconv are not working as iam using Parallel jobs.
I used Varchar data type in Sequential file stage.
I used StringToDate function to convert into Date Format.
I used Date data type in DB2 stage ,but not writing into the database.
When i view the data in DB2 stage it's showing like "(3)(1968/01/03)".
It's treating 3rd Jan 1968 as (3)(1968/01/03).As Datastage takes
31 st Dec 1967 as base value with 0(zero),it's converting into
(3)(1968/01/03).

Is there any other functions to convert into date format?
It's urgent ,If anybody give reply i would appreciate them and advance thanks to them.

thanks
Post Reply