Map String date value of a Csv to a column in mySql database using Informatica Cloud

0
votes

I am doing Data Integration project to create a Data Warehouse in mySql. I have a couple of Csv/flat files to be ingested into Informatica Cloud which will populate the destination table in mySql database (The destination table is already created).

The Csv files have columns related to datetime but they are in String format (MM/DD/YYYY HH:MM) and I am not able to pass them through Informatica Cloud to the mySql datatbase tables. The column in mySql Database has format YYYY-MM-DD HH:MM:SS as the data type is datetime.

I tried different strategies of keeping the destination columns datatype as datetime and sometime varchar.

I got Following errors on different attempts with different changes.

*Transformation [Expr_DSS_0010LY0I000000000002_1] had an error evaluating output column [SystemModstamp_OUT]. Error message is [<<Expression Error>> [TO_DATE]: invalid string for converting to Date... t:TO_DATE(u:TO_CHAR(t:TO_DATE(u:'5/30/2013 12:26',u:'mm/dd/yyyy hh:mi'),u:'yyyy-mm-dd hh:mm'),u:'MM/DD/YYYY HH24:MI:SS')].

Transformation [Expr_DSS_0010LY0I000000000002_1] had an error evaluating output column [CreatedDate_OUT]. Error message is [<> [TO_DATE]: invalid string for converting to Date... t:TO_DATE(u:'6/24/2008 18:23',u:'mm/dd/yyyy hh24:mi:ss')]. Transformation [Expr_DSS_0010LY0I000000000002_1] had an error evaluating output column [CreatedDate_OUT]. Error message is [<> [TO_DATE]: invalid string for converting to Date... t:TO_DATE(u:TO_CHAR(t:TO_DATE(u:'6/24/2008 18:23',u:'mm/dd/yyyy hh24:mi'),u:'YYYY-MM-DD hh:mm:ss'),u:'MM/DD/YYYY HH24:MI')].*

I also tried the documentation related to Informatica Cloud (Csv datetime) but didn't get any concrete solution.

I will be really glad if someone can help.

Thanks in advance.

2
mysqldatetimecsvinformatica

2 Answers

2
votes

A single TO_DATE should work

TO_DATE(input_date_field,'mm/dd/yyyy hh24:mi')
1
votes

I just finished solving the exactly same issue myself. There is no good way to do this, I will explain what I did:

Basically I treated the date columns as string in power center. i.e. I changed the target definition only in PC to varchar for all the dates column.

SQ, expects everything is string for their attributes. Where as the physical definition is still datetime in mysql DB.

For me this worked. hope it works for you too. Good luck !!