11

Every time that I try to import an Excel file into SQL Server I'm getting a particular error. When I try to edit the mappings the default value for all numerical fields is float. None of the fields in my table have decimals in them and they aren't a money data type. They're only 8 digit numbers. However, since I don't want my primary key stored as a float when it's an int, how can I fix this? It gives me a truncation error of some sort, I'll post a screen cap if needed. Is this a common problem?

It should be noted that I cannot import Excel 2007 files (I think I've found the remedy to this), but even when I try to import .xls files every value that contains numerals is automatically imported as a float and when I try to change it I get an error.

https://i.sstatic.net/UzCE1.jpg

Improve this question
2
  • What tool are you using the for the import? SSIS? And it would be helpful to know exactly what an "error of some sort" means. Commented Mar 21, 2012 at 16:11
  • @ Pondlife, I'm using the Import Wizard in SSMS. A link to the error has been edited in the original post. Commented Mar 21, 2012 at 16:18

8 Answers 8

Reset to default
11

SSIS doesn't implicitly convert data types, so you need to do it explicitly. The Excel connection manager can only handle a few data types and it tries to make a best guess based on the first few rows of the file. This is fully documented in the SSIS documentation.

You have several options:

  • Change your destination data type to float
  • Load to a 'staging' table with data type float using the Import Wizard and then INSERT into the real destination table using CAST or CONVERT to convert the data
  • Create an SSIS package and use the Data Conversion transformation to convert the data

You might also want to note the comments in the Import Wizard documentation about data type mappings.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I was thinking I'd have to make a 'staging' table. I didn't want to resort to that because I figured that was kludgey, but if it works I guess it'll work. Thanks! :)
I did make a staging table, but all values that weren't convertible to floats were silently converted to NULLs.
9

Going off of what Derloopkat said, which still can fail on conversion (no offense Derloopkat) because Excel is terrible at this:

  1. Paste from excel into Notepad and save as normal (.txt file).
  2. From within excel, open said .txt file.
  3. Select next as it is obviously tab delimited.
  4. Select "none" for text qualifier, then next again.
  5. Select the first row, hold shift, select the last row, and select the text radial button. Click Finish

It will open, check it to make sure it's accurate and then save as an excel file.

Improve this answer

2 Comments

derloopkat's answer to your comment under his question sums up my thoughts exactly
This didn't work for me. In SQL Server 2012 it still tried to convert the column to float.
7

There is a workaround.

  1. Import excel sheet with numbers as float (default).
  2. After importing, Goto Table-Design
  3. Change DataType of the column from Float to Int or Bigint
  4. Save Changes
  5. Change DataType of the column from Bigint to any Text Type (Varchar, nvarchar, text, ntext etc)
  6. Save Changes.

That's it.

Improve this answer

Comments

5

When Excel finds mixed data types in same column it guesses what is the right format for the column (the majority of the values determines the type of the column) and dismisses all other values by inserting NULLs. But Excel does it far badly (e.g. if a column is considered text and Excel finds a number then decides that the number is a mistake and insert a NULL instead, or if some cells containing numbers are "text" formatted, one may get NULL values into an integer column of the database).

Solution:

  1. Create a new excel sheet with the name of the columns in the first row
  2. Format the columns as text
  3. Paste the rows without format (use CVS format or copy/paste in Notepad to get only text)

Note that formatting the columns on an existing Excel sheet is not enough.

Improve this answer

3 Comments

The same problem with your solution, It will save the number as a VarChat, that doesn't work...
All solutions provided on this page require to manually change the column type. Sorry, I assumed anyone know how to do that. The question above was how to prevent integer numbers to become floats, being truncated, etc, and more specifically to avoid this error message "Error converting data types".
Using MSSQL 2012 and Office 15.0 this did not work for me. SSIS still wants to convert the columns to float.
1

There seems to be a really easy solution when dealing with data type issues.

Basically, at the end of Excel connection string, add ;IMEX=1;"

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\YOURSERVER\shared\Client Projects\FOLDER\Data\FILE.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

This will resolve data type issues such as columns where values are mixed with text and numbers.

To get to connection property, right click on Excel connection manager below control flow and hit properties. It'll be to the right under solution explorer. Hope that helps.

Improve this answer

2 Comments

Even with IMEX=1, it's assuming my field is a string field, and chopping off significant digits in the numbers. E.g. my number 987654321 becomes 9.876543e+008. Converting what it imported back to a number again gives 987654300, losing the last two digits. Excel's interoperability with actual data is a total abortion. I'm trying to get all my clients to remove Excel from their machines, but they're all addicted - mentally dependent sheeple.
Interesting. Yeah, Excel can be very aggravating with formatting. munishbansal.wordpress.com/2009/12/15/… This is best resource I have found on this topic, and should help you dealing with your large numbers. I haven't had to mess with numbers like this before, so it's a new issue to me.
1

To avoid float type field in a simple way:

  1. Open your excel sheet..
  2. Insert blank row after header row and type (any text) in all cells.
  3. Mouse Right-Click on the head of the columns that cause a float issue and select (Format Cells), then choose the category (Text) and press OK.

And then export the excel sheet to your SQL server.

This simple way worked with me.

Improve this answer

Comments

0

A workaround to consider in a pinch:

  1. save a copy of the excel file, modify the column to format type 'text'
  2. copy the column values and paste to a text editor, save the file (call it tmp.txt).
  3. modify the data in the text file to start and end with a character so that the SQL Server import mechanism will recognize as text. If you have a fancy editor, use included tools. I use awk in cygwin on my windows laptop. For example, I start end end the column value with a single quote, like "$ awk '{print "\x27"$1"\x27"}' ./tmp.txt > ./tmp2.txt"
  4. copy and paste the data from tmp2.txt over top of the necessary column in the excel file, and save the excel file
  5. run the sql server import for your modified excel file... be sure to double check the data type chosen by the importer is not numeric... if it is, repeat the above steps with a different set of characters

The data in the database will have the quotes once the import is done... you can update the data later on to remove the quotes, or use the "replace" function in your read query, such as "replace([dbo].[MyTable].[MyColumn], '''', '')"

Improve this answer

Comments

0

1. you set your primary key in database as no identity.
2. Upload your excel file to google drive and open it with google sheet. Then download it as .csv file
3. After download, open .csv file wih notepad and save as .txt file (change Encoding when saving if need)
4. In SQL Server Import and Export Wizard, choose data source is Flat File Source, then browse .txt file to upload

It worked for me.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.