Excel in SSIS: fixing the wrong data types

Imagine your client gives you an Excel file as the SSIS source for a new project. When you load the data you find that the data type is not aligning as expected and some of the rows are not filled with the data provided.

You then try to change the input data type in the Excel source component, but you can’t seem to get the output you want. So what's the problem?

Well, let’s take a look at the input.

The first thing we notice is that the CODE column consists of two kinds of data types: numeric and alphanumeric (rows 9 to 12).

When we try to load this into our database, we notice that rows 9 to 12 are not loaded correctly and are shown as null values.

The Excel source suggests that the data type of the CODE column is numeric (float), as shown below.

This happens even when we set the fixed outcome of the source to be alphanumeric:

The first question is, why is the value given as a float when the input clearly shows that the input data are a combination of alphanumeric and numeric values?

Some research on MSDN reveals the following information:

<https://msdn.microsoft.com/en-us/library/ms141683.aspx>

- The Excel source component in SSIS determines the input data types by itself, based on the first 8 rows of the Excel file.

- “Missing values. The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the data type with the majority, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.”

IMEX is set to 1, but if we investigate further we find following:

<http://support.microsoft.com/en-us/kb/194124>

“NOTE: Setting IMEX=1 tells the driver to use the Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM  driver by default looks at the first eight rows and determines the datatype from that sample. If  these eight sampled rows are all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. ”

As a side note, you aren’t allowed to change the registry settings…

But if you want to change them, here is where to find them:

For 32-bit

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel

For 64-bit

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

Also for 64-bit

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

We had the bad luck of the first 8 rows being of numeric type, with the first alphanumeric value in the 9th row. When we take a new Excel file that has alphanumeric values for the top rows, you’ll notice that the data type is now alphanumeric.

And loading this into the database gives the following result:

A simple solution, which has proven to be successful so far.

Note that we don't format the cells as “General” but as “Text”.

This is how to do it:

1. Select the columns that are causing the issue and select “Format Cells…”.

2. In this dialog box, go to the “Number” tab.

As you can see, the cell formatting is set to "General", meaning Excel decides what type of formatting is applied to each individual cell. We set this value to “Text” instead of “General”.

3. Cells are now formatted as having textual values.

When we go back into the SSIS Excel source, we find a welcoming change in the input data types.

And this is the result on execution – no more null values for records 9 to 12:

One of the easiest ways of avoiding this issue is to work with CSV files instead. A CSV file stores tabular data as plain text data and allows you to choose the best possible data type for the project (or what you expect it to be). This gives you more control over the data and the data flow.

If plain data is dropped into SQL from Excel, another option is to use OpenRowSet, which allows you to read the XLS file from within SQL and insert it directly from the same query.