SSIS CSV Import: String converts to scientific notation

0
votes

I am attempting to import a CSV file that contains an alphanumeric column into a VARCHAR column in a MS SQL table and am running into issues where the data is sometimes re-written in a scientific notation format. This only occurs when the CSV string format is similar to scientific notation.

EX: CSV string "3344E133" is being imported as "3.344E+136" in the SQL table.

This only happens when the CSV string begins with a number, the only letter is "E", and it ends with a number. Other strings are being imported without being re-written (EX: CSV string "50524676" and "2D8FDD0C" are imported as "50524676" and "2D8FDD0C").

I've confirmed that the column in the CSV file is text and it's being imported into a VARCHAR(50) SQL column. I'm using a Flat File Source Connection Manager for the import.

Any thoughts on why this is happening or what I can do to fix?

1
sql-servercsvssis
Sounds like you've set the data type of the column in SSIS to a float; or allowed the connection manager to make the decision rather than you tell it it's a DT_STR. SSIS won't change the data type because it "feels like it". SSIS won't even implicitly cast a DT_STR to a DT_WSTR, let alone a to a floating point value. - Larnu
On the Flat File Connection Manager Editor under the Advanced the column is set to Data Type: string [DT_STR] with an OutputColumnWidth of 50. - Jericho
So where are you seeing the value 3.344E+136 then? SSIS certainly won't be doing this, and a CSV can't be anything else other than text; it's a CSV. - Larnu
ugh...turns out this is an Excel issue. The csv was opened using Excel to which converted the string to scientific notation and was then resaved so the csv kept the conversion as text. When I re-ran the import using a clean csv everything worked fine - I'll mark this as answered, apologies for the mis-leading question - Jericho
One of the many reasons Excel can be a Terrible product. It too "clever" (stupid) for it's own good. - Larnu

1 Answers

0
votes

False alarm. Issue was caused when the csv file was opened using Excel. Excel converted the string to scientific notation and the file was re-saved as a csv. The csv then kept the scientific notation format as text which was imported into the SQL table. When process was re-run using a clean csv everything imported correctly.