Mixed datatypes in Excel Cell problem in SSIS

Case

My column in Excel contains mixed data types (strings and numbers). The string values are shown as NULL. How can I get all values?

Solution

The Excel driver reads the first 8 rows to determine the datatype. If the majority of the rows are numbers it will choose a numeric datatype instead of a string data type and visa versa.

So if your Excel sheet looks like this. The column start with number then gets DT_R8 and column start with string gets DT_WSTR.



TypeGuessRows

You can change the default of checking 8 rows (1 to16) by changing the windows registry, If you want to change that number... Start the Windows Registry-editor and search for "TypeGuessRows". The place of that key depends on the version of Windows and Office. Examples:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines\Excel

Note: For performance reasons, setting the TypeGuessRows value to zero (0) is not recommended (especially when your Excel table is very large). With this zero value Excel will scan all records in your table to determine the type of data in each column.

Steps needs to be followed:

To change the value of TypeGuessRows, use these steps:

1. On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.

2. Open the following key in the Registry editor:

a. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
Note For 64-bit systems, the corresponding key is as follows:

b. HKLM\SOFTWARE\wow6432node\microsoft\jet\4.0\engines\excel

3. Double-click TypeGuessRows.

4. In the DWORD editor dialog box, click Decimal under Base(Based on requirement). Type a value between 0 and 16, inclusive, for Value data.

5. Click OK, and then exit the Registry Editor.


Import Mode

If you want to get the data of all rows, regardless the datatypes in Excel, you should add the string ;IMEX=1 to the Excel Connection String. Changing to Import Mode (0 is Export mode, 1 is Import mode and 2 is Linked mode) will result in getting everything (including numbers) as a string. Go to the properties of the Excel Connection manager and change the Connection String property:


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

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyExcelFile.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1";