decimal import error from text file
Moderators: chulett, rschirm, roy
decimal import error from text file
Field "VALUE" has import error and no default value; data: {- 6 5 8 9 9 8 . 0 0 0 0 0d}
I have a text file that ends with a decimal and no delimeter for some reason I get the above error when importing this file. I have the datatype set to 38,10 in DS. For some reason it is seeing an 0d at the end of the number.
2009{5{TEST{TEST{TEST{TEST{TEST{TEST{TEST{TEST{-658998.0000
If I change the file to the below and specidy the end delimiter it works....why is that.
2009{5{TEST{TEST{TEST{TEST{TEST{TEST{TEST{TEST{-658998.0000{
I have a text file that ends with a decimal and no delimeter for some reason I get the above error when importing this file. I have the datatype set to 38,10 in DS. For some reason it is seeing an 0d at the end of the number.
2009{5{TEST{TEST{TEST{TEST{TEST{TEST{TEST{TEST{-658998.0000
If I change the file to the below and specidy the end delimiter it works....why is that.
2009{5{TEST{TEST{TEST{TEST{TEST{TEST{TEST{TEST{-658998.0000{
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard. You have told DataStage that there is no delimiter, but there is a record delimiter. The 0d in the raw string is Char(10), the UNIX line terminator character. That is what is generating the error.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Can you see the 0d at the end of this string? That's a Char(10). You have to figure out why it's there in the data and/or why your format specification does not match what's actually in the file. Can you post the record schema associated with this link?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
If I view the file in TextPad,TOAD FTP or import into excel no value exists at the end of the record. I have tried all the Final Delimeter's (none,end,whitespace). At a previous place we insisted all files end with a pipe delimiter and I think it was for the same reason. I will move forward with that option if necessary but I always wondered why this caused an issue. If I change the data type to a FLOAT instead of decimal it will work but for some reason it rounds random numbers. Odd it works as FLAOT but not decimal.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
actually you need to see the file in hexa format to see the 0D charecter(hEX 13) at the end of each line.u need to use text editors like crimson or ultra edit(ctrl+h) to see the hexa format of a fileray.wurlod wrote:Not Final Delimiter. This is not the same as Record Delimiter. ...
generally these charecters are placed while the file is generated by some mainframes machines.
sun
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's just your normal Windows/UNIX record delimiter tomfoolery and is a sign that whomever transferred the file didn't do it properly. UNIX uses a single LineFeed character while Windows uses two characters - a CR/LF pair. A Windows or "DOS" style file on a UNIX server will show the CR as a last bit of data at the end of each record and that hex 0D shows up as a "control-M".
There are several solutions - an ascii ftp rather than a binary one, for example. Or declaring the file a DOS file. Or running it through your "dos2UNIX" equivalent (Filter) before reading it.
There are several solutions - an ascii ftp rather than a binary one, for example. Or declaring the file a DOS file. Or running it through your "dos2UNIX" equivalent (Filter) before reading it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers