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 51
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
I would explicitly use CONVERT in your OPENROWSET query, rather than relying on implicit conversions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

Dale FyeOwner, Developing Solutions LLCAuthor 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:


I just don't get it, the maximum width of the entries in this column with or without leading/trailing spaces is 8 characters.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Lawrence BarnesCommented:
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, Developing Solutions LLCAuthor Commented:
@Lawrence, no go on the upload.  Already checked that there is no special formatting on that column.
Lawrence BarnesCommented:
For kicks can you increase the nvarchar(10) amount to something far greater?
Dale FyeOwner, Developing Solutions LLCAuthor 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.
Lawrence BarnesCommented:
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

Dale FyeOwner, Developing Solutions LLCAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.