6

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
  • We import everything as a SQLCHAR into a staging table where all columns are datatype VARCHAR then do the conversion/cleanup from staging to the main table. Might be the easiest way? – Mark Sinkinson Mar 19 '15 at 16:17
  • I thought about using a staging table too. Since this is a one-time import and I have a second similar sized file with 177 columns, I try to avoid this approach. – Peter Schuetze Mar 19 '15 at 17:39
  • What are the resulting db values for your test cases? – Thomas Cleberg Mar 19 '15 at 17:40
  • 1
    Notably, if it's truncating the string at 8 chatacters for some reason, the failures would result in invalid strings like "#######e-' – Thomas Cleberg Mar 19 '15 at 17:46
  • I ask the values because if that's happening, the resulting value for the second test would just be 6.5789474 – Thomas Cleberg Mar 19 '15 at 17:55
0

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    ""
  • You get Invalid data for type "numeric". message. The thing is that scientific notation is only valid for a float but the target column is a Decimal. I haven't found the right trick (if it even exists) to read it as a float and store it as a decimal. Standard procedure seems to be, to load as varchar into a temp table and have a post processing step moving it to the final table. – Peter Schuetze Mar 20 '15 at 12:56
  • Yeah I haven't seen any trick to do it. The temp table makes sense or... Now this is a little off the wall, but you could do your insert as Varchar and then create a calculated column that converts it to Decimal. – Stephan Mar 20 '15 at 13:56
0

Unfortunately, the successful import of this column is misleading. The data does not end up in the table. The column only contains 0 as a value.

Conclusion

If you have data in floating point format, import it in a float or text column. It will not work with importing it into a decimal column. For maximum flexibility, import everything into a temp table where all columns are varchar and then format and error check the data. When you are done put it into the final table. .... or use a proper ETL tool.

Disclaimer: I am not sure if the XML format files are more powerful and provide better data conversion capabilities.

What I did

I worked with the business and they provided the export formatted. Fortunately, they knew with what columns that could have happened and formatted these columns to always be in a format suitable for a decimal column. I could use my original format files.

-1

Interesting Question!

Reference source from MS documentation here.

This appears to be a function of the format file-

Specifying a host-file length of 0 in the format file automatically defaults the length of the CSV field to the length of the database source file +1. Since the length past the decimal in the database is 9, .00000045662 fits (9+1)but .000000456621 does not (11).

Try setting the format file to:

88  SQLFLT8 0   12   "," 89  PPL_2_BL    ""

Unless you expect longer values to come through the field in the future, this should work.

  • I had to try it out right away, but it's not the solution. See updated examples in Question. If I add a 0 to the number it imports fine. If I change a digit it still seems to fail. I am puzzled. – Peter Schuetze Mar 19 '15 at 17:28
  • 1
    Actually I read the MS documentation again. It states If you are creating a non-XML format file for a delimited text file, you can specify 0 for the host file data length of every data field. When a delimited text file having a prefix length of 0 and a terminator is imported, the field-length value is ignored, because the storage space used by the field equals the length of the data plus the terminator. Meaning it ignores the length value anyway since I am using field separators. Nevertheless, I tried it with 12 and even 20 as field length with no success. I guess it's some freaky math issue – Peter Schuetze Mar 19 '15 at 17:42
  • Agreed, this is not the answer. – Thomas Cleberg Mar 19 '15 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.