Import error for decimal field in csv file

udayanguha · Post by **udayanguha** » Wed Feb 18, 2015 8:50 pm

Hi,
I am trying to read a csv file with 22 fields last of which is a decimal field named BOAT_HASH_KEY (decimal 38,0). The field is set to NULLABLE in output column properties of the sequential file stage. Format of sequential file stage is as below:

Record level
Final Delimeter = end

Field defaults:
Delimeter = comma
NULL field value = ''
Quote = double

When I try to view data I get the following error:
Field "BOAT_HASH_KEY" has import error and no default value; data: {3 0 5 1 2 4 9 0 8 9 0d}, at offset: 77

When I remove this fiield from the csv file (and from the stage metadata as well), I am able to view the data. Its only causing a problem with a decimal field in the end. If there's a varchar field in the end (the last field), I am able to view data.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 18, 2015 9:04 pm

The CSV file was created on a Windows system (hence the 0x0d character at the end). Set the Record Delimiter String property value to "DOS style", or set the Final Delimiter property value to 013.

udayanguha · Post by **udayanguha** » Wed Feb 18, 2015 9:20 pm

Hi Ray,
Thanks a lot. One of the solutions worked and the other I could not understand.
I set the record delimeter string = DOS format and it worked fine for me.

I could not implement the 2nd solution you suggested i.e. Final Delimiter = 013
Datastage doesn't allow me to set any value more than 1 character long for Final Delimeter. I can't type anything after 0 (so could not type 013 in full)

udayanguha · Post by **udayanguha** » Thu Feb 19, 2015 1:34 pm

Hi,
Can anyone help how can I set the option Final Delimiter = 013 in the sequential file stage. DS is not letting me type anything after a single character.

Mike · Post by **Mike** » Thu Feb 19, 2015 1:54 pm

013 is the decimal value of that single ASCII character. The easiest way to put that single character into the stage property is to cut & paste it from a text editor. It'll probably show as a box once pasted, but should work fine.

You can also try choosing whitespace as the final delimiter since that character is likely considered whitespace.

Mike

Mike · Post by **Mike** » Thu Feb 19, 2015 2:01 pm

Note that if you open it as text on a Windows box, you won't be able to cut & paste it.

Open it as binary on a Windows box with a text editor that supports binary.

Mike