Error converting data types when importing from Excel to SQL Server 2008

Question

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   http   imgur com 4204g

User · Accepted Answer

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

User · Answer

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    Create a new excel sheet with the name of the columns in the first row Format the columns as text 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

User · Answer

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

User · Answer

A workaround to consider in a pinch    save a copy of the excel file  modify the column to format type  text  copy the column values and paste to a text editor  save the file  call it tmp txt   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  copy and paste the data from tmp2 txt over top of the necessary column in the excel file  and save the excel file 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

User · Answer

There is a workaround    Import excel sheet with numbers as float  default   After importing  Goto Table-Design Change DataType of the column from Float to Int or Bigint Save Changes Change DataType of the column from Bigint to any Text Type  Varchar  nvarchar  text  ntext etc  Save Changes    That s it

User · Answer

Going off of what Derloopkat said  which still can fail on conversion  no offense Derloopkat  because Excel is terrible at this    Paste from excel into Notepad and save as normal   txt file   From within excel  open said  txt file  Select next as it is obviously tab delimited  Select  none  for text qualifier  then next again  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

[sql-server] Error converting data types when importing from Excel to SQL Server 2008

Examples related to sql-server

Examples related to sql-server-2008