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

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.
LVL 50
Dale FyeAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
I would explicitly use CONVERT in your OPENROWSET query, rather than relying on implicit conversions.
0
 
Lawrence BarnesCommented:
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
0
 
Dale FyeAuthor 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.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Lawrence BarnesConnect With a Mentor Commented:
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.
0
 
Dale FyeAuthor Commented:
@Lawrence, no go on the upload.  Already checked that there is no special formatting on that column.
0
 
Lawrence BarnesCommented:
For kicks can you increase the nvarchar(10) amount to something far greater?
0
 
Dale FyeAuthor 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.
0
 
Lawrence BarnesConnect With a Mentor Commented:
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
0
 
Dale FyeAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.