Floating Point Numbers Stored as Text with Integration Services
-
I've encountered an unusual situation in an import program I wrote. From what I can determine, the root of the problem is that I read data from an Excel spreadsheet (through a data conversion component which changes all data types to DT_STR) into an unconstrained table (no FK's, all columns are varchar(255)). For some reason, on occasion currency values are imported with what I assume is their floating point representation. Confusingly, this doesn't appear to be consistent behaviour in a given file. e.g. 10098.73 -> 10098.73 but 8978.98 -> 8978.9799999999996. In a second package, the data is imported into a correctly typed table (there's a few lookups etc done along the way too). The type of the currency column is numeric(15,2) but I don't seem to be able to catch the data truncation as it is reported as a data overflow instead and I don't want to ignore the error completely in case e.g. text has been entered instead of a number. I'm assuming the easier way to go is to figure out why the numbers are occasionally stored as floating point in text - can anyone shed any light? Failing that, I might just write a SPROC that will truncate the text automatically...!! :)
It definitely isn't definatley
-
I've encountered an unusual situation in an import program I wrote. From what I can determine, the root of the problem is that I read data from an Excel spreadsheet (through a data conversion component which changes all data types to DT_STR) into an unconstrained table (no FK's, all columns are varchar(255)). For some reason, on occasion currency values are imported with what I assume is their floating point representation. Confusingly, this doesn't appear to be consistent behaviour in a given file. e.g. 10098.73 -> 10098.73 but 8978.98 -> 8978.9799999999996. In a second package, the data is imported into a correctly typed table (there's a few lookups etc done along the way too). The type of the currency column is numeric(15,2) but I don't seem to be able to catch the data truncation as it is reported as a data overflow instead and I don't want to ignore the error completely in case e.g. text has been entered instead of a number. I'm assuming the easier way to go is to figure out why the numbers are occasionally stored as floating point in text - can anyone shed any light? Failing that, I might just write a SPROC that will truncate the text automatically...!! :)
It definitely isn't definatley
moon_stick wrote:
I'm assuming the easier way to go is to figure out why the numbers are occasionally stored as floating point in text - can anyone shed any light?
Because $2 divided by three people equals something that's hard to store in binary. It results in 0.666666~, where the length of a byte is limited. A digital representation of a analogue value :) Excell sometimes keeps the "calculated" values without rounding them. Those values are already imprecise, since you can't encode every fraction using a byte. It keeps the unrounded value around, and displays a value with the specified formatting: in this case, a currency with two positions after the decimal-separator. It would be easier to test whether the data can be interpreted as a numeric, and if so, round it by two decimals. Keep in mind that you don't want to use these (formatted) numbers for further calculations; you'd want the original floats for that :)
I are Troll :suss: