Trying to read from an Excel document. Field has very precise values (ex. 0.0128540) that Data Services is rounding when reading. I have the document set up to cast the field as Decimal(28,8). I have the job set up as a direct read from the Excel to an Oracle table, and Data Services imports the values rounded to four decimal places. (ex. 0.0129)
When I View Data in DS, it shows 0.0129, even though I can open the file in Excel and see 0.0128540. In Excel, the field is set up as Number with 7 decimal places.
The really interesting thing about this is that the two fields next to this one have the same precision, but also have significant digits to the left of the decimal point. This one does not. The other numbers import fine, all seven significant digits to the right. If it doesn’t have significant digits to the left, it only brings in four to the right.
Any ideas on what I could do? Data Services 12 seemed to have no issues with this…
I think there is a format option while loading the excel in file format. Default it is none, but you have to change to decimal format which is something like ###.0
In Excel, the field is defined as a Number field with 7 decimal places. I’ve also tried defining it as an Accounting field with 7 decimal places, as well as custom.
In the DTD, the field is defined as decimal 28,8.
In Oracle, the field is defined as decimal 28,10.
The only thing that even partially works so far is changing Excel to treat the column as text (and the DTD to varchar(255)) and convert it mid-stream. That’s less than ideal, but it is the workaround we have in place for now.
Better to ensure that all the excel file stored the data as text in the columns, and then read as Varchar, and convert to decimal, where you can specify the format.
(BOB member since 2007-03-14)
(BOB member since 2012-04-11)