Talend community forum / "\N" problem Infobright loader, How can I drop this "\N"

Ahmad Tabeh · 2010-08-08 15:13:48

Tags: [CSV file, error, infobright, mysql, null]

In using tMysqlBulkExec for Infobright after tOracleInput I found problem if \N enclose with Double-quotations in output file, Infobright loader will load this column as 0 or \ instead of null but in mysql is OK!
So how I can enclose other columns with " except null columns ( ;\N; instead of ;"\N"; ) or do not produce any thing for null columns ( ;; instead of ;"\N"
I tried any compunenet but I couldent generate export file in this format.

Infobright loader need this out put for simple table ( id int, name varcahr2, type int, end date)
"1";"ATK";;"2010-01-01" or "1";"ATK";\N;"2010-01-01"
insted of
"1";"ATK";"\N";"2010-01-01" or "1";"ATK";"";"2010-01-01" ( these two rows insert 0 instead of null in table)

shong · 2010-08-09 04:53:40

Hi
Before loading the file, read the file and remove the "\N" in each row and regenerate the file again.

Code:

row1.line.subSequence(0, row1.line.indexOf("\\N")-1)+row1.line.substring(row1.line.indexOf("\\N")+3)

Best regards
Shong

Uploaded Images

atkmain · 2010-08-10 03:54:42

Hi
Thank you for your reply,

Because currently I have around 80 designed&tested jobs( They have not any problem with Mysql, only with Infobright). is it possible to inject this code to tMysqlOutputBulkExec component instead of changing all my job and add extra component for this matter?

Last edited by atkmain (2010-08-10 03:58:18)

shong · 2010-08-10 03:58:05

Hi
Can you see my screenshot? You can see I am using the tFileInputFullRow component to read the file.
There is no reject link on tMysqlOutputBulkExec.

Best regards
Shong

atkmain · 2010-08-11 07:09:59

Hi,
Yes I saw your attached pictures. As you can see in my attached picture I done with 6 component and below code to replace them

Code:

 row1.line.replace("\"\\N\"", "") ;

but currently I did it with linux sed command that is more easy and faster.
I created a custom component for myself but is seems remove double-quotation around \N is hard and it come from CSVwriter class that is belong to taland core class.
But sill I think it is not efficient way.So, I found bug point in Infobright 3.4 in file : DataParserForText.cpp that fixing and keep compiling is huge effort

Code:

void DataParserForText::PrepareNulls()‏
{
    if(!IsLastColumn()) {
        for(int i = 0; i < no_prepared; i++) {
            int rs = row_sizes[i];
            if(
                *values_ptr[i] == delimiter ||
                (((values_ptr[i] + 5) < (rows_ptr[i] + rs)) && strncasecmp(values_ptr[i], "NULL", 4) == 0
                        && (*(values_ptr[i] + 4) == delimiter)) ||
                (((values_ptr[i] + 3) < (rows_ptr[i] + rs)) && strncasecmp(values_ptr[i], "\\N", 2) == 0
                        && (*(values_ptr[i] + 2) == delimiter))‏
            )‏
                nulls[i] = 1;
            else
                nulls[i] = 0;
        }

Thank you fr your help

Uploaded Images

gtfalk · 2010-10-13 21:25:11

Thank you. I have opened Infobright ticket #1954 for this.

Geoffrey

Announcement

Unanswered posts

#1 2010-08-08 15:13:48

"\N" problem Infobright loader, How can I drop this "\N"

#2 2010-08-09 04:53:40

Re: "\N" problem Infobright loader, How can I drop this "\N"

Code:

#3 2010-08-10 03:54:42

Re: "\N" problem Infobright loader, How can I drop this "\N"

#4 2010-08-10 03:58:05

Re: "\N" problem Infobright loader, How can I drop this "\N"

#5 2010-08-11 07:09:59

Re: "\N" problem Infobright loader, How can I drop this "\N"

Code:

Code:

#6 2010-10-13 21:25:11

Re: "\N" problem Infobright loader, How can I drop this "\N"

Board footer