decimal import error from text file

[radarada]

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{

[ray.wurlod]

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.

[radarada]

Using the UNIX newline delimiter i get this error.

Delimiter for field "VALUE" not found; input: {- 6 5 8 9 9 8 . 0 0 0 0 0d}

[ray.wurlod]

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?

[radarada]

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.

[ray.wurlod]

Not Final Delimiter. This is not the same as Record Delimiter.

[suneyes]

Not Final Delimiter. This is not the same as Record Delimiter. ...

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 file

generally these charecters are placed while the file is generated by some mainframes machines.

[radarada]

I can get the import to work with other data types such as FLOAT,DOUBLE,Int,BigInt,etc...(however it distorts the real number) For what ever reason the Decimal cannot be used. If I change the value to a varchar and bring it in it will bring it in with an odd box at the end of the last character.

[ray.wurlod]

Are you permitting zero-valued decimals? There's an environment variable that you can set to do that - I can't recall the name of it at present.

[radarada]

The file that was being created was adding the unix ^M onto the end of each row. I was never able to figure out a way for DS to see the ^M as the final delimiter so we had to request the files creator to be sure and remove the ^M prior to submitting.

[chulett]

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.