last field null chk fails
Moderators: chulett, rschirm, roy
last field null chk fails
A transformer in the job should reject NULL records for a tab delimited sequential file with the following format:
Col1 Col2 Col3
c1val1 c2val1 c3txtval1
c1val2 c2val2 c3txtval2
c1val3 c2val3 c4txtval3
c1val4 c2val4 \n
We have a problem when rejecting Values from Col3. When the first character of the Col3 value is a newline character, the value record doesnt reject out. I have represented the above with the 4th row with \n as the newline character. This is a problem only with the last column value.
The constraints used in the transformer are IsNull(c4txtval3) and Len(Trim(c4txtval3)) = 0. Is there something wrong with the constraints being used?
Col1 Col2 Col3
c1val1 c2val1 c3txtval1
c1val2 c2val2 c3txtval2
c1val3 c2val3 c4txtval3
c1val4 c2val4 \n
We have a problem when rejecting Values from Col3. When the first character of the Col3 value is a newline character, the value record doesnt reject out. I have represented the above with the 4th row with \n as the newline character. This is a problem only with the last column value.
The constraints used in the transformer are IsNull(c4txtval3) and Len(Trim(c4txtval3)) = 0. Is there something wrong with the constraints being used?
check the property in the sequential file
Actually that is an issue as, you need to set some property for the "Final Delimiter", if you leave it to default which is end, then it will treat the "\n" as the final delimeter.
Probably you need to set some other property as the final delimiter and also need to modify the sequential file accordingly.
Probably you need to set some other property as the final delimiter and also need to modify the sequential file accordingly.
Regards,
Novneet Jain
Novneet Jain
Re: check the property in the sequential file
output produced is having an extra column Col4 and output produced is as below.
Col1 Col2 Col3 Col4
c1val1 c2val1 c3txtval1\n c4val1\n
c1val2 c2val2 c3txtval2\n c4val2\n
c1val3 c2val3 c4txtval3\n c4val3\n
c1val4 c2val4 \n c4val4\n
this error is coming only for the last column. Column datatype is varchar
Col1 Col2 Col3 Col4
c1val1 c2val1 c3txtval1\n c4val1\n
c1val2 c2val2 c3txtval2\n c4val2\n
c1val3 c2val3 c4txtval3\n c4val3\n
c1val4 c2val4 \n c4val4\n
this error is coming only for the last column. Column datatype is varchar
After a close analysis here what is happening exactly.
input sequential file property
record delimiter = new line character
field delimiter = tab
final delimiter = none (the last row ends with a new line character)
file has 3 columns col1 col2 col3
output produced has 4 columns with column col4 added at the end
output produced had special thick pipe like character at the end of col3, which after seeing through a text editor found to be a new line character.
Actually while writing to the output file the col3 value is appended with a new line character (last column in input file).
any thoughts on this?
input sequential file property
record delimiter = new line character
field delimiter = tab
final delimiter = none (the last row ends with a new line character)
file has 3 columns col1 col2 col3
output produced has 4 columns with column col4 added at the end
output produced had special thick pipe like character at the end of col3, which after seeing through a text editor found to be a new line character.
Actually while writing to the output file the col3 value is appended with a new line character (last column in input file).
any thoughts on this?
Set you final delimiter to end.
Also the thickened pipe that you are seeing is CR, ascii (013). I bet this is a dos file that you ftp'd over in binary mode. Ftp it again in ascii mode so that the CRLF gets converted to LF (dos to unix) formats. Or find one of the many sed/tr/awk commands to convert dos files to unix.
Also the thickened pipe that you are seeing is CR, ascii (013). I bet this is a dos file that you ftp'd over in binary mode. Ftp it again in ascii mode so that the CRLF gets converted to LF (dos to unix) formats. Or find one of the many sed/tr/awk commands to convert dos files to unix.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: