Excel in SSIS: fixing the wrong data types

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.

150804b

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.

150804c

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

150804d

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

150804e

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:

  • 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 majority data type, 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:

“NOTE: Setting IMEX=1 tells the driver to use 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 from that sampling determines the datatype. If  this eight row sampling is 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.

150804

150804f

And loading this into the database gives the following result:

150804g

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…”.

150804h

 

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

150804i

 

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. Cell are now formatted as having textual values.

150804j

 

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

150804k

 

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

150804l

 

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 of 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.