Importing Excel file to SQL Server generates "arithmetic overflow error (#8115)

Dale Fye
Dale Fye used Ask the Experts™
on
I'm trying to implement a query that uploads data into SQL Server table using OpenRowSet.

Occasionally, I run into a file that generates an error message:

8115, Arithmetic overflow error converting expression to data type nvarchar

My research indicates that this is generally caused by trying to stuff too long of a string into an nvarchar field.  I have reviewed the data in Excel, and cannot find any field where the number of characters in the a cell exceed the number of characters defined for the field in the destination table.  All of the fields that are currency in the spreadsheet are being imported into fields defined as Float in the SQL Server.  But there are a number of columns in the Excel file which contains what looks like numbers but should actually be represented as text (thus the import into nvarchar(x) fields).

I'm assuming that either Excel or SQL Server is experiencing a data representation issue and although it is formatting the numbers display properly in Excel, the values are actually being stored with an excessive number of decimals, and when SQL tries to convert to nvarchar it is doing so for the actual value in the field, not the value that is displayed in the spreadsheet.

 So, how do I limit the number of digits that are being imported from Excel into these nvarchar  fields.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
I would explicitly use CONVERT in your OPENROWSET query, rather than relying on implicit conversions.
Users can type anything into Excel, so I stage the import and allow error reporting.  By staging I mean that the import initially goes into a staging table with all VARCHAR values.  Then that table can be queried for non-convertable values to report back to system/user/etc. if there are alpha's in a numeric field.  When importing from the staging table to the destination table the results can be adjusted/cleansed/etc.

LVBarnes
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Yeah, that is what I generally do with Access too.  But this is an Excel file that is being exported from the clients accounting package, and the files contain anywhere from 50K to 700K rows, so I would really prefer to pull the data directly into SQL Server so they can access it with their BI software.

I've narrowed the problem down to a single field.  It contains a combination of numeric values, NULLs, and strings consisting of 3 or 4 space characters.  I tried to append a space to the value in the field and then use LTRIM to get rid of the spaces using:

LTRIM('' '' + [Oper])

But that resulted in error 8152: String or binary data would be truncated

When I tried:

Cast([Oper] as nvarchar(10))

I got the 8115: Arithmetic overflow error converting expression to data type nvarchar
And I get the same error when I try:

Convert(nvarchar(10),[Oper])

I just don't get it, the maximum width of the entries in this column with or without leading/trailing spaces is 8 characters.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Any chance of uploading csv file with a sample of the data and the fieldtype of the column you are putting it into?

I think the accounting package export rules this out...but I would also check to see if the number formatting ($ and commas) wasn't being passed as a string.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
@Lawrence, no go on the upload.  Already checked that there is no special formatting on that column.
For kicks can you increase the nvarchar(10) amount to something far greater?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Had already tried setting it to 50.  with the same results.

There is something wrong with the data in that column.  I'm going to try another track and loop through the rows of the spreadsheet and replace "    " values with "" and see if that makes a difference.
There are a lot of cleaning functions around, but I don't like to use them because of the overhead.  Perhaps Excel has some non-visible returns in place?

You can also escalate this question to get more eyes on it.

REPLACE(REPLACE(REPLACE(FieldName,''),CHAR(13),''),CHAR(10),''),' ','')

Open in new window


Lawrence
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks for your recommendations gentlemen.  I finally got it working with a combination of Coalesce and Convert statements.  Not quite sure why it was not able to insert NULLs into an nvarchar(50) NULL field, but I appreciate your recommendations.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial