String to Timestamp Conversion in .CSV file as a Source

[sankawa]

hi

I am using .CSV file as a source and Oracle as Target.
Source format is dd/mm/yyyy(String) and target format is Timestamp it doesn't matter the format in target but it should be Timestamp value...
I am using Oracle Enterprice stage for Target.
I didn't specified the column size in the Source,beacouse it is a .CSV file.

This is the complete vale i specified in the transformation.
Column Name SQL-Type Length Scale Nullable Description
VALUDATION_DT Timestamp - - Yes Nullable TimeStamp

but when i am doing the same with fixed length file the following code is working,but in case os .CSV file it showing warning that Data may be lost so this methord are not updating the data in target table.


I tried in two way:---
First way is-------
I create one staging variable mydate(String) and use the rite the Query:--
SrcCorpriceSf.VALUATION_DT[7,4] : "-" : SrcCorpriceSf.VALUATION_DT[4,2] : "-" : SrcCorpriceSf.VALUATION_DT[1,2] : " 00:00:00"
and after that in VALUDATION_DT feild i wrote the following Querry:--
StringToTimestamp(mydate,"%yyyy-%mm-%dd %hh:%nn:%ss")

Second way is-----
I wrote the querry directly to VALUDATION_DT feild:--
StringToTimestamp(SrcCorpriceSf.VALUATION_DT[7,4] : "-" : SrcCorpriceSf.VALUATION_DT[4,2] : "-" : SrcCorpriceSf.VALUATION_DT[1,2],"%yyyy-%mm-%dd %hh:%nn:%ss")
But data is not loadinfg to thr target Table...

Thks & Regards,
Sandeep

[ArndW]

Sandeep, use your second method but create 3 columns going to a peek stage for your yyyy, mm and dd values to see if they are what you expect.

[sachin1]

i hope your date is in format yyyy-mm-dd, then in required target column give input as, hard coded values are just an example.I am using Oracle OCI stage and it works.

OCONV("2007-09-18","D-YMD[4,2,2]") : OCONV(00:00:00,"MTS")

[sankawa]

Hi

Thks for replyig.I already use peak stage and it showing data but when i connect it to Oracle Stage then it showing the warning message that "Data may be lost in this conversion" and din't inserting the data into target.
I also tried it with Sequential file as target then data is loadad into file but it showing warning "Exporting nullable field without null handling properties"...

How to overcome with this data lost Warning in Oracle Stage???
even if i am using Sequential file as Input instead of .CSV file the same methord is working fine...

Thks

[ArndW]

He is writing a PX job, so OCONV() won't work.

[ArndW]

Just fix and address one problem at a time. Ignore Oracle for the moment, just output to a peek stage or a test flat file.

If you output the timestamp column with your StringToTimeStamp() function does it show a correct timestamp?