Import Data Wizard Does Not Like Data Type I Choose For A Column

Question

Does anybody else have this same problem, when you import data from Excel file to MSSQL Server 2005, if some column contains mostly numeric data, but even if you set the column type to varchar, the wizard fails to import those fields that fail to parse as numbers?

Answer 1

Yes i have some problem with it too, especially when converting varchar into datetime, btw it can be solved by installing Integration Services... btw may i know what SQL Server 2005 version do you use? Enterprise/Developer/Standar? and what level service pack is?

PS: You can install integration services with your sql server 2005 cd and select integration services and workstation component (since you have install database engine)

Answer 2

Try this (Note: These instructions are based on Excel 2007)...

The following steps should force Excel to treat the column as text:

Open your spreadsheet with Excel.

Select the whole column that contains your "mostly numeric data" by clicking on the column header.

Click on the Data tab on the ribbon menu.

Select Text to Columns. This will bring up the Convert Text to Columns Wizard.

-On Step 1: Click Next

-On Step 2: Click Next

-On Step 3: Select Text and click Finish

Save your Excel sheet.

Retry the import using the SQL Server 2005 Import Data Wizard.

Answer 3

You need to edit the connection string that SSIS uses. Add this to the end of the string "IMEX=1;" (No quotes) That tells SSIS/jet to not try and figure out what the data types are. Just import them.

Answer 4

I tried to change the column to Text but the number loaded in with unexpected conversion like convert 2345.73 to 2345.72999999999.

Answer 5

Create a new column that is an apostophe + the contents of the column you want to import. This will force it to be a string

="'"&E2

Answer 6

IMEX =1 in Extended Properties is Helping only if the first few values in the column is text. If the first few values in the column are numbers, the following text is not accepted by SSIS. WHAT TO DO NOW !!!

We cant ask user to sort data etc crap.. Or enter only text data... by making 30 as 30 units... They are not buying that there could be an issue with SSIS and EXCEL.. Its hopeless.. !!

Could you please help

Answer 7

awasome solution Joe L. it worked..thnks

Answer 8

You might try tweaking Jet settings for importing Excel data. You can force the Jet engine to sample the entire sheet when trying to determine the column type during a given import. Change the following registry key (after making a backup first) and see if that doesn't do it:

HKLM\Software\Microsoft\Jet\4.0\Engines\Excel

Or on x64

HKLM\Software\Wow5432Node\Microsoft\Jet\4.0\Engines\Excel

Set the value TypeGuessRows equal to zero. This will force Jet to sample all rows to determine the column type.

Answer 9

Thomas, I have tried that on XP and Windows 7, its still not working. One thing I am unable to understand do I need to make this change on the server itself or on client machines. This is a painful for us as we would be doing a lot of importing of excel documents. And I have not been able to find one good solution for this issue. What went wrong with MS head this time, this was the solution on their KB, it didn't help.

Answer 10

Yes SSIS is a total joke. I would add an SQL box and put in a single SQL line using OPENROWSET to import the excel sheet, takes about 5 mins as opposed to a day of frustration with none of these insane problems. I can guarantee this SSIS will die a death within 5 years as everyone will abandon it, then msoft will come up with some new mad badly conceived idea dreamt up down the pub one drunken night. Most of SSIS development time has clearly been spent thinking of the most convoluted timewasting ways to make simple tasks in SQL nigh on impossible.