Solved

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

Posted on 2014-03-01
9
1,183 Views
Last Modified: 2014-03-03
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.
0
Comment
Question by:Dale Fye (Access MVP)
  • 4
  • 4
9 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 39897388
I would explicitly use CONVERT in your OPENROWSET query, rather than relying on implicit conversions.
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39897731
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39897789
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
 
LVL 5

Assisted Solution

by:Lawrence Barnes
Lawrence Barnes earned 300 total points
ID: 39897807
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39900867
@Lawrence, no go on the upload.  Already checked that there is no special formatting on that column.
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39901093
For kicks can you increase the nvarchar(10) amount to something far greater?
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39901116
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
 
LVL 5

Assisted Solution

by:Lawrence Barnes
Lawrence Barnes earned 300 total points
ID: 39901870
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
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 39902110
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now