Question on file loading to table

puliram · Post by **puliram** » Mon Nov 09, 2009 11:38 pm

Hi,
I have a csv file reading using SeqFile stage. I have about 10 columns in csv file(eg. below) but the last two columns are decimals.

Here is Eg. my csv file
641,ABC,2009,10,ABCDEF,GHIJKLM,111,200,485262.93,336360.00

first 8 files are varchar files and last two are decimal fields i would like to read
but till the 9 field i was successful but 10 column, i couldnt able to read as decimal but could able to read it as varchar

Any idea, how I can read 10th column too as decimal.

Thanks

gssr · Post by **gssr** » Mon Nov 09, 2009 11:42 pm

As you read the column as Varchar, you can convert it using the Type conversion technique. Do you get the whole value as Varchar(Including the decimal value)?

puliram · Post by **puliram** » Mon Nov 09, 2009 11:45 pm

gssr wrote:As you read the column as Varchar, you can convert it using the Type conversion technique. Do you get the whole value as Varchar(Including the decimal value)?

I can use the type coversion but its not working. all 0's are being loaded into table in last column

Just let me know the format of stringtodecimal syntax, though its is clear in help, its not helping much.

gssr · Post by **gssr** » Mon Nov 09, 2009 11:55 pm

Can you able to see the records in the sequential file ?. If not then check with the metadata you have mentioned and then we can go for the type conversion!!

gssr · Post by **gssr** » Tue Nov 10, 2009 12:01 am

puliram wrote: Just let me know the format of stringtodecimal syntax, though its is clear in help, its not helping much.

Can you post the conversion syntax that you have used?

Actually,In 7x, we dont need any conversion function,as this can be done in the implicit conversion.

puliram · Post by **puliram** » Tue Nov 10, 2009 12:17 am

gssr wrote:Can you able to see the records in the sequential file ?. If not then check with the metadata you have mentioned and then we can go for the type conversion!!

Yes I can able to see records in veiwdata, with final column as varchar. But want to read as decimal.
I have another file, with two decimals which is at 7,8th positions, I was able to read as decimals and loaded successfully.
Only if the decimal column is at the end, there might be some rule to read as decimal. Just need that help

puliram · Post by **puliram** » Tue Nov 10, 2009 12:20 am

gssr wrote:As you read the column as Varchar, you can convert it using the Type conversion technique. Do you get the whole value as Varchar(Including the decimal value)?

yes got whole value as varchar including decimal, but dont want that. want to read as decimal

gssr · Post by **gssr** » Tue Nov 10, 2009 1:57 am

Then try to map the Varchar Column to Decimal without using any function to the target file. For your records your varchar(9) should be mapped to Decimal(6,2)

I do had a situation like you, and used the above solution which worked fine! (In 7x)

puliram · Post by **puliram** » Tue Nov 10, 2009 2:46 am

gssr wrote:Then try to map the Varchar Column to Decimal without using any function to the target file. For your records your varchar(9) should be mapped to Decimal(6,2)

I do had a situation like you, and used the above solution which worked fine! (In 7x)

TRIED, but did not worked, still loading as zero's into table.

puliram · Post by **puliram** » Tue Nov 10, 2009 2:52 am

puliram wrote:
gssr wrote:Then try to map the Varchar Column to Decimal without using any function to the target file. For your records your varchar(9) should be mapped to Decimal(6,2)

I do had a situation like you, and used the above solution which worked fine! (In 7x)
TRIED, but did not worked, still loading as zero's into table.

Resolved, tried myself

read as float
and then used function
DFloatToDecimal, it worked

Thanks