Date getting converted to 0001-01-01T00:00:00

[Anonymous]

I am loading tables using pipe delimited file and tMySQLOutputBulkExec
These tables have some date columns. Date in pipe delimited file is in the following format
""|"1"|""|""|""|""|""|""|""|"0"|"5736"|"1"|""
""|"5"|""|"01-01-1970 00:00:00"|""|""|""|""|""|"0"|"5736"|"1"|""
""|"6"|""|"01-01-1970 00:00:00"|""|""|""|""|""|"0"|"5736"|"1"|""
""|"7"|""|""|""|""|""|""|""|"0"|"5736"|"1"|""
But when it is inserted in the MySQL database with tMySQLOutputBulkExec it is loaded as
0001-01-01T00:00:00.
If I am using tMySQLOutput its working fine
but with tMySQLOutputBulkExec its always inserting a value as 0001-01-01T00:00:00. whether date is blank or has some value in the input.
Please note that I am using a java project.
Regds,
Anuradha.

[Anonymous]

Shong,
As you know I am using following flow:
t_InterbaseInput-->t_Map-->t_FileDelimitedOutput-->t_MySQLBulkExecuteOutput.
Where
t_InterbaseInput--> reads data from Interbase table with Dates as String
t_Map-->Converts String to date format -- "yyyy-MM-dd HH:mm:ss"
t_FileDelimitedOutput-->Generates a pipe delimited file with each field enclosed by "" seperated by |.
t_MySQLBulkExecuteOutput--Read input delimted file and loads data in bulk to target table.
Currently for Null Dates file is generated as
"1"|"5736"|"0"||""|||||"*"|"*** CASH SALE ***"|||||||"1"|"N"|"Admin "|"1"|"1"|||||||||||||||||||||"1"|""
"5"|"5736"|"0"||"1970-01-01"|||||"STOCK"|"****"|||||||"1"|"N"|"Admin "|"1"|"1"|||||||||||||||||||||"1"|""
Here 5th column is Date where "" in first record indicate null date value and "1970-01-01" valid date in the second record.
And with this file For input dates with null string t_MySQLBulkExecuteOutput is inserting "0001-01-01T00:00:00".
********************************************************************************
I found a solution that if in the file we pass null date as "\N" instead of "" the LOAD INFILE utility which is used by tMySQLBulkOutputExec interprets it as NULL.
I tested it by running LOAD INFILE on MySQL prompt and in the csv file I passed NULL as "\N" and it loaded data as NULL.
Command:---
LOAD DATA INFILE '/home/vanket/poc_demo/patients.csv' IGNORE INTO table I_PATIENTS FIELDS TERMINATED BY '|' ESCAPED BY '\\' ENCLOSED BY '"' LINES TERMINATED BY '\n';
the input patients.csv file was
"1"|"5736"|"0"||"\N"|||||"*"|"*** CASH SALE ***"|||||||"1"|"N"|"Admin "|"1"|"1"|||||||||||||||||||||"1"|""
"5"|"5736"|"0"||"1970-01-01"|||||"STOCK"|"****"|||||||"1"|"N"|"Admin "|"1"|"1"|||||||||||||||||||||"1"|""
Here 5th column is Date where I had passed "\N" in first record to load null value and a valid date in the second record.
***************************************************************************************
I tried to implement the same in TOS, what I am doing is , I tried passing date value as string and hardcoded it as "\N" in date column just to see all date values are getting inserted as NULL for each record.
But its giving error "Invalid escape sequence( valid ones are \n,\r etc.......
In the delimited file - Escape Char is set as -- "\\"
Also in t_MySQLBulkExecuteOutput Escape Char is set as -- "\\"

Can you please help, its really urgent as we have alot of tables with date columns.
And we have to implement this at all places.
Thanks .
Anuradha.

[Anonymous]

Waiting for reply ... from Talend Team .....

[Anonymous]

Hello Anuradha
Let's go ahead...
First, output the result to a txt file, then use tMysqlBulkExec instead of tMysqlOutputBulkExec to run the txt file. See my screenshot.
in.csv:

1;2009-02-26 01:02:33
2;
3;2009-02-27 01:02:33

expression of date column:

(row1.date.equals("")?"\"\\N\"":row1.date)

Best regards
shong

[Anonymous]

Thanks alot shong for this demo.It solved my problem that was like a bottleneck.
Thanks Again.
Anuradha.