2

I am using SSIS 2008 to load alphanumeric columns from Excel.

I have one column which starts off as integer

1
2
...
999

Then changes to AlphaNumeric

A1
A2
A999

When I try to load using using an Excel Data Source, excel will always say that it is an integer as it must only sample the top of the file.
(BTW - I know that I can re-order the file so that the alphas are at the top but I would rather not have to do this...)

Unfortunately you can't seem to be able to change its mind. This means that when it loads the data, it filters out the 'A' and the A999 record will update the 999 record. This is obviously not good... I have tried to change the external and output columns to string under the advanced editing options, but I get errors and it won't run until you set the columns back to integer.

Does anyone have a solution?

praveen
  • 12,083
  • 1
  • 41
  • 49
GordyII
  • 7,067
  • 16
  • 51
  • 69

2 Answers2

3

SSIS uses Jet to access the Excel files. By default, Jet scans the first 8 rows of your data to determine the type of each column.

To fix it, you will need to edit the registry to increase the TypeGuessRows DWORD value of one of the following registry keys to determine how many rows to scan in your data:

It depends on what version of Windows and what version of excel ... as follows:

For 32-bit Windows

Excel 97
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

Excel 2000 and later versions
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

For 64-bit Windows

Excel 97
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\3.5\Engines\Excel

Excel 2000 and later versions
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

Then, specify IMEX=1 in the connection string as follows:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\abc.xls;
Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

This information can be found in a more verbose form at: http://support.microsoft.com/kb/189897/

praveen
  • 12,083
  • 1
  • 41
  • 49
Marcus D
  • 1,074
  • 1
  • 12
  • 27
0

Wow, that looks like a huge pain. I came across a couple of examples where you could alter the connection string and sometimes get better results but they don't seem to work for everyone.

Scripting an automatic conversion to an .csv file would be a good workaround, there are a number of suggestions in this thread:

converting an Excel (xls) file to a comma separated (csv) file without the GUI

including some code in C# that you may be able to easily plop in:

http://jarloo.com/code/api-code/excel-to-csv/

here is the simiar question where altering the connection string is discussed if you want to look into it for yourself: SSIS Excel Import Forcing Incorrect Column Type

Good luck!

Community
  • 1
  • 1
DKnight
  • 733
  • 3
  • 15