Read a file - Null Field Value

[devidotcom]

Hi All,

I want to read a file with the contents below. All the three columns are nullable. What will be the null field value to be given to read the null columns?

Here the first line is the header column names. Col3 is nullable with no values given to the row 2 and 3

Col 1, Col 2, Col3
1234, "DATASTAGE1",2345
1234,"DATASTAGE2",
1234,"DATASTAGE2",
1234,"DATASTAGE2",2345

I gave the following format options
Quote: Double
Delimiter : Comma

Col1 ---- Integer (4), Nullable
Col2 ---- VarChar(20), Nullable
Col3 ---- Integer(7),Nullable

The job fails to read the row 2 and 3.
Please help
Thanks

[vivekgadwal]

Col 1, Col 2, Col3
1234, "DATASTAGE1",2345
1234,"DATASTAGE2",
1234,"DATASTAGE2",
1234,"DATASTAGE2",2345

The job fails to read the row 2 and 3.
Please help
Thanks

Do you have the Col3 as empty i.e. there is nothing after the comma in rows 2 and 3?

Try having the Null field value option in the Sequential File stage set to "\x00". This should ensure that you are reading empty string as the Null character.

Hope this helps...

[vivekgadwal]

Col 1, Col 2, Col3
1234, "DATASTAGE1",2345
1234,"DATASTAGE2",
1234,"DATASTAGE2",
1234,"DATASTAGE2",2345

The job fails to read the row 2 and 3.
Please help
Thanks

Do you have the Col3 as empty i.e. there is nothing after the comma in rows 2 and 3?

Try having the Null field value option in the Sequential File stage set to "\x00". This should ensure that you are reading empty string as the Null character.

Hope this helps...

[devidotcom]

[quote="vivekgadwal"][quote="devidotcom"]
Col 1, Col 2, Col3
1234, "DATASTAGE1",2345
1234,"DATASTAGE2",
1234,"DATASTAGE2",
1234,"DATASTAGE2",2345

The job fails to read the row 2 and 3.
Please help
Thanks[/quote]

Do you have the Col3 as empty i.e. there is nothing after the comma in rows 2 and 3?

Try having the Null field value option in the Sequential File stage set to "\x00". This should ensure that you are reading empty string as the Null character.

Hope this helps...[/quote]

No it did not. Yes its empty. I gave the value \x00 for null field value option. But that did not work.

[vivekgadwal]

No it did not. Yes its empty. I gave the value \x00 for null field value option. But that did not work.

What do you have the Final delimiter option set as? Try removing the "Final Delimiter" i.e. have that value as "None" and try it.

[devidotcom]

Yes did it but does not work. It yet reads only 2 rows..

[edison]

Col 1, Col 2, Col3
1234, "DATASTAGE1",2345
1234,"DATASTAGE2",
1234,"DATASTAGE2",
1234,"DATASTAGE2",2345

this is working for me,put null field value as ''

[devidotcom]

Thank you so much it worked with ''.

[vivekgadwal]

Yes did it but does not work. It yet reads only 2 rows..

Read everything as a Varchar. I just created a dummy job and had all the settings the way I told you to have and am reading everything as a Varchar. It is working for me!