Excel in SSIS: fixing the wrong datatypes

Imagine your client gives you an Excel file as the SSIS source for a new project. When you load the data, a few things stand out. The data type is not aligned as expected and some of the rows are not filled with the data provided.

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


Investigating the issue

Well, let’s 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 load this into our database, 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:

  • The Excel source component in SSIS determines the input data types by itself, based on the first eight rows of the Excel file.
  • “Missing values. The Excel driver reads a certain number of rows (by default, eight rows) in the specified source to guess 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

For 64-bit

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 eight 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 welcome 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 of the data and the data flow.

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

Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...
How to easily parse JSON in Power BI Desktop
Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want...
Power BI reports as monitoring tool
Who has not created monitoring reports or beautiful dashboards in Power BI? Would it not be great to show off...
Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all...