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.
Thanks alot shong for the quick update.
The issue got resolved.
But now the problem is that, it is showing correct values for non null dates.
For input dates with null string its still inserting "0001-01-01T00:00:00".
So for this input wherin 4th column is a date column
""|"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"|""
Its inserting following in MYSQL
0001-01-01 00:00:00
1970-01-01 00:00:00
1970-01-01 00:00:00
0001-01-01 00:00:00
I could not understand why for null values it is getting converted to 0001-01-01 00:00:00?
Any other settings I need to do for null data handling.
Can you please give a demo example to load date column from delimited file to MYSQL5.0? This delimited file should have some null and some non null values as mentioned above in the input file.
Thanks & Regds,
Anuradha.
I could not understand why for null values it is getting converted to 0001-01-01 00:00:00? Any other settings I need to do for null data handling.
if you won't get the default value 0001-01-01 00:00:00 for null value,
you can add a new default date which you can consider invalid to replace 0001-01-01 00:00:00.
see my screenshot.
Best regards
Thanks shong, but the issue is I am migrating some data from Interbase to MYSQL now if something is null in Interbase I cant load it with some default value in MYSQL. I need to load it as null only.... Thats the issue.
I am new to both MySQL/Interbase and also TOS.
I had used the following flow.
t_InterbaseInput-->t_Map-->t_FileDelimitedOutput-->t_MySQLBulkExecuteOutput.
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.
Now I used Logrow after each step to identify what values are passing at each step
t_InterbaseInput--> displays null as null in LogRow output
t_Map-->displays null as null in LogRow output
t_FileDelimitedOutput-->Converts null to ""
t_MySQLBulkExecuteOutput--Reads "" in date and load it as 0001-01-01 00:00:00 .
I also used a Java row in between t_FileDelimitedOutput and t_MySQLBulkExecuteOutput and found that Javarow reads "" as null only but loads its as 0001-01-01 00:00:00 . Please find the code below
**************************************************
output_row.SSN = input_row.SSN;
output_row.ACCOUNT_NUMBER = input_row.ACCOUNT_NUMBER;
output_row.COMMENTS = input_row.COMMENTS;
if (input_row.DATE_OF_BIRTH==null)
{
output_row.DATE_OF_BIRTH = TalendDate.parseDate("yyyy-MM-dd HH:mm:ss","9999-09-09 01:01:01");
}
else
output_row.DATE_OF_BIRTH=input_row.DATE_OF_BIRTH;
**************************************************
On running this code null dates were replaced by "9999-09-09 01:01:01" but not as "null" or "".
Could you make out where I am doing something wrong??
Thanks & Regds,
Anuradha.
Could you make out where I am doing something wrong??
I see that your code in tJavaRow is right, you are using 9999-09-09 01:01:01 replace 0001-01-01 00:00:00 when the date is null.
About replace null value wtih 0001-01-01 00:00:00, I think it is due to mysql bulk action. tMysqlOutput can load it with 'null' value.
Best regards
But tMySQLOutput is too slow with large amount of data thats why I opted tMySQLBulkOutput.
Any other option to pass it as a null value.
tMySQLBulkOutput takes few secs to load 60,000 data while tMySQLOutput takes more than a minute.
