SQL Server – Fixing Arithmetic Overflow Error Converting Float to Numeric

sql server

I am trying to move data from a csv file into a SQL server database. Some of my values are in the scientific notation. I figured out on how to get most of them converted but for one value I get the Arithmetic overflow error.

The value that is causing the error is 4.56621E-6. If I change the part before the E by removing the 1 so it reads 4.5662E-6 the import works fine. All the other values I need to import work fine.

I use a format file to import the data. Below the line for the column that is giving me grief:

88  SQLFLT8 0   0   "," 89  PPL_2_BL    ""

The format in the database is decimal(18,9). Any suggestions on how to avoid this error without manually changing values in the source file?

To put it into perspective. The CSV file contains more than 2.2 million rows with 154 columns each. Which results in a CSV file size of more than 2GB. Currently I am working with a test file. When the final go live comes. I need to switch over fast. Which means I can not analyze and edit the file for several days.

Update

I played around with the values a little bit.

4.56621E-6   -> fails
6.5789474E-6 -> works
4.5662E-6    -> works
4.56622E-6   -> fails
4.566210E-6  -> works (surprisingly)
4.66621E-6   -> fails

Best Answer

I'm no expert in this, but it looks to me as if you're trying to use FLOAT. Have you tried using NUMERIC? Something like this maybe? Just throwing ideas out.

88  SQLNUMERIC 0   0   "," 89  PPL_2_BL    ""
Related Question