I'm using the new apex_data_parser package to get data from uploaded Excel sheets into a database table. Certain columns contain item numbers which have a decimal number format with between 0 to 4 (possibly more) digits following the decimal point. I am loading these into a varchar2 column in order to be able to do some type checking and other validation before processing proceeds to the next stage. Processing is done using a PL/SQL packaged procedure
The issue is that in some cases where the spreadsheet displays an item number such as "500.102", the file parse result is something like "500.10199999999998". I believe this is because the user has used an Excel formula to derive the value and Excel is storing it internally in that manner. I'm not suggesting that this is an issue with the file parser. I cannot simply round up the result since I do not know to how many decimal places any specific item number should be rendered.
Changing the column format in Excel doesn't seem to make any difference. I think my only option is to analyse each number string to pick up repeated "9999" or "0000" values after the decimal point and work out the number of places to round up or down to.
Does anyone know a better way?