Home > Excel, SSIS > Excel Data Type Guess Registry Setting

Excel Data Type Guess Registry Setting


We already know that the excel make its own guess about the column data type in the excel sheet.  But, we can tell the excel reader to treat all columns as string by using IMEX property in the Excel connection string.

Well, now we can reader excel data as string until column have some data for at least in first 8 rows. Otherwise, Excel will skip that column or make all rows a null.

Experiment:

I have an excel file with some data in it. I will use SSIS excel reader to export data from excel to SQL server.

Excel File

When I preview the data in the Excel reader preview. I can see the data in the column A and Column B is all null. I am surprised about this behavior.

After some Bing search I have found an answer to this mystery. It is a registry setting that holds a magic number that tell the excel to guess the data type of the column and in my case it tells that my Column B is string and It is all NULL value.
So, I change the registry setting as given below.

Registry Key location:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\ExcelRegistry Key name
TypeGuessRows

Value:
Default Value = 8
New value = 0
Note: I am using Windows 7. In some machine the registry key may be in this location.

“Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ TypeGuessRows”
 

After the change has made in the registry the output of my excel reader is what I was expecting.

Sample Excel file used for this experiment: TypeGuessSample97_2003

Note: This content is just to show how the problem has been resolved using the registry value. But, modifying the registry setting may damage your system. So, implement this experiment at your own risk.

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: