Read a file - Null Field Value
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
Read a file - Null Field Value
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
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
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Read a file - Null Field Value
Do you have the Col3 as empty i.e. there is nothing after the comma in rows 2 and 3?devidotcom wrote: 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
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...
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Read a file - Null Field Value
Do you have the Col3 as empty i.e. there is nothing after the comma in rows 2 and 3?devidotcom wrote: 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
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...
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
Re: Read a file - Null Field Value
[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.
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.
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Read a file - Null Field Value
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 wrote:
No it did not. Yes its empty. I gave the value \x00 for null field value option. But that did not work.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
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!devidotcom wrote:Yes did it but does not work. It yet reads only 2 rows..
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted