Hello Anuradha

You need to use the same pattern as target table.
yyyy-MM-dd HH:mm:ss

Best regards

         shong

Waiting for the response .....

Thanks & Regds,
Anuradha.

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.

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.

Last edited by anuradha.chaturvedi (2009-04-17 12:02:13)

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.

Last edited by anuradha.chaturvedi (2009-04-21 15:33:22)

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