?
Solved

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

Posted on 2014-03-01
9
Medium Priority
?
1,249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 800 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 48

Author Comment

by:Dale Fye
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 5

Assisted Solution

by:Lawrence Barnes
Lawrence Barnes earned 1200 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
 
LVL 48

Author Comment

by:Dale Fye
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 48

Author Comment

by:Dale Fye
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 1200 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 48

Author Closing Comment

by:Dale Fye
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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