Data type conversion error while importing from Excel to SQL Server 2008

Question

Every time I try to import an Excel file into SQL Server, I get a certain error. When I try to edit the mappings, the default value for all numeric fields is float. None of the fields in my table have decimals in them, and they are not a data type of money. They are only 8 digits. However, since I do not want my primary key to be stored as a float when it was int, how can I fix this? This gives me some truncation error, I will send the screen cache if necessary. Is this a common problem?

It should be noted that I cannot import Excel 2007 files (I think I found this tool), but even when I try to import .xls files, each value that contains numbers is automatically imported as a float and when I try to change it. I get an error message.

http://imgur.com/4204g

Answer 1

SSIS does not implicitly convert data types, so you need to do this explicitly. Excel Connection Manager can only process a few data types, and it tries to make a better guess based on the first few lines of the file. This is fully documented in the SSIS documentation.

You have several options:

• Change destination data type to float
• Download the "staging" floating-point table using the import wizard and then INSERT to the real CONVERT table using CAST or CONVERT to convert the data
• Create an SSIS Package and Use Data Transformation to Convert Data

You can also mark comments in Documents of the import wizard about data type mappings.

Answer 2

Failure to say what Derloopkat said, which can still fail to convert (no offense Derloopkat), because Excel is terrible at this:

• Paste from excel into notepad and save as usual (.txt file).
• Inside excel, open the specified .txt file.
• Choose the next one since it is clearly tabbed.
• Select "none" for the text specifier, then again.
• Select the first line, hold shift, select the last line and select the text radial button. Click Finish

It will open, check it to be accurate, and then save it as an excel file.

Answer 3

When Excel finds mixed data types in one column, it guesses what is the correct format for the column (most values ​​determine the type of column) and rejects all other values ​​by inserting NULL. But Excel does this very poorly (for example, if the column is considered to be text, and Excel finds the number, then it decides that the number is an error and instead inserts NULL, or if some cells containing numbers are “text”, they can be NULL into an integer database column).

Decision:

• Create a new new excel sheet with the column names in the first row
• Format columns as text
• Insert lines without format (use CVS format or copy / paste to Notepad to get only text)

Note that formatting columns in an existing Excel worksheet is not enough.

Answer 4

There is a workaround.

• Import an excel sheet with numbers as a float (default).
• After import, Goto Table-Design
• Change the DataType of a column from Float to Int or Bigint
• Save changes
• Change DataType of a column from Bigint to any text type (Varchar, nvarchar, text, ntext, etc.)
• Save changes.

What is it.

Answer 5

This seems to be a really simple solution when solving data type problems.

Basically, at the end of the 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 solve data type problems, such as columns, in which values ​​are mixed with text and numbers.

To access the connection property, right-click on the Excel connection manager below the control flow and click properties. This will be to the right in the explorer section. Hope this helps.

Answer 6

Workaround to consider as a last resort:

• save a copy of the excel file, change the column for text type formatting
• copy the column values ​​and paste into a text editor, save the file (name it tmp.txt ).
• Change the data in the text file for the beginning and end with a character so that the SQL Server import engine recognizes the text. If you have a fashion editor, use the included tools. I am using awk in cygwin on my windows laptop. For example, I begin to end the column value with a single quote, for example, " $ awk" {print "\ x27" $ 1 "\ x27"} ./ tmp.txt> ./tmp2.txt "
• copy and paste the data from tmp2.txt over the desired column in the excel file and save the excel file
• start sql server import for your modified excel file ... make sure to double check the data type selected by the importer is not numeric ... if so, repeat the above steps with a different character set

The data in the database will have quotes after the import is completed ... you can later update the data to remove the quotes or use the replace function in a read request, for example, replace ([dbo]. [MyTable]. [MyColumn], '' '', '') "