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?

Cyclorama

Answer 1

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.

Gusta

Unfortunately I have excel 2002. I had to save my sheet as tab delimited txt file to work around that. That introduced other headaches, but worked after some massage. – Cyclorama

I think in Excel 2002 the Text to Columns option is under the Data menu. – Gusta

This really seems like it almost would work for me too. (Using Excel 2007). However, commas are being stripped from values like 47,100 into just 47100, which is not acceptable. (The value isn't a number! it is 47 and 100 separated by a comma). It is really hard to believe how big of a problem this is and so many people expressing frustration at it, but no real easy answer. – Handcuff

A slight variation... I selected the entire column, right clicked, Format Cells -> and chose "Text" on the first tab. – Registrar

Answer 2

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.

Monck

Answer 3

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\Wow6432Node\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.

Gehring

This appears to read every row for each column. For large sheets with many rows it can take a long time. – Hieroglyphic

Indeed. However, the alternative is that the system guesses that a given column should be a number or date based on the first 100 or so rows and then on row 748,456 it fails and kills the whole process and you get to start over. – Gehring

Answer 4

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

Cataldo

Answer 5

Adding IMEX=1 BEFORE the HDR setting worked for me.

Jennijennica

Answer 6

This is the best solution:

1. Click File on the ribbon menu, and then click on Options.

2. Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.

3. Click OK.

4. In the worksheet, select the cells that you want to format.

5. On the Home tab, click the Dialog Box Launcher Button image next to Number.

6. In the Category box, click Number.

7. In the Decimal places box, enter the number of decimal places that you want to display.

Smallpox