Home > Excel, SSIS > Excel auto column data type detector

Excel auto column data type detector


Excel Source reader task detects the column data type from Excel file automatically. But, this is not always helpful for developer to get the default settings from the excel source component. For instance, if a column in the excel file contains both numeric data and alpha-numeric data then the excel source assumes the data type as Integer.In this case, you will get an error if you store this data in numeric column in the SQL table. Because, we have got non-numeric data in the same column.

There is no property in Excel source task to stop this auto column detector setting. But, we can declare an extended property IMEX=1 in the excel connection string in excel connection manager that tells the excel driver to read the data as alpha-numeric.

A sample excel connection string given below reads the excel file content as intermixed (int, float, data and etc) as alpha-numeric data.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FileName>;Extended Properties=”EXCEL 12.0 XML;HDR=NO;IMEX=1“;

Property value

IMEX=1; This setting reads all excel content as alpha-numeric value

IMEX=0; This setting tells the excel driver to auto detect column data type

In some case you may see the date value as numeric value for instance, 01/15/1991 in excel and the output will be 33253.

The solution is very simple if you have your excel open while executing the package in design mode. It will show as this or you will get the date as text type.

There is one more property that can help to avoid this problem. Excel extended property IMPORTMIXEDTYPES=TEXT. But, You have to close the open excel file.

Thanks for reading.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: