Importing Data From Excel Gotcha – The Top 8 Row Rule

I have ran across this a couple times now, I actually had a blog post draft written up for this but never got around to finishing it, well this week I ran into the issue again, so here it is.

Importing data from Excel (using whatever – C#, VB.NET, SSIS, Access, etc) – you have data scattered throughout, and when you go to import it, some of the values are not getting read, they are just missing – empty, or not correct. Something goofy is happening and you cannot figure it out.

Excel tries to figure out the data type of a column, or if there is any data at all in a column by looking at the Top 8 Rows. It will ignore the header row. But it looks at these 8 rows and tries to be smart about it, and this can really screw you up if you are importing data.

Here is my example.

Year | Value1 | Value2
—————————–
1998 |             |   20  
1999 |             |   
2000 |             |   15
2001 |             |   20  
2002 |             |   
2003 |             |   15
2005 |             |   20  
2006 |             |   
2007 |     55     |   15

Now this might not work if you actually try it, but just bear with me for the examples sake. If you write a function in .NET to import this XLS sheet, the 55 in Value1 column will never get read. Why? Because the first 8 rows for Value1 are empty, and it thinks it is an empty column, just wacked. If you put a 0 in the 1998 row for Value1, then the 55 will get read. Since Value2 column has values in the first rows, it works just fine.

How can you get around this? Well, if you somehow read your XLS file without using the JET engine, then you are probably OK, also, you can tweak a registry setting to make excel look at more rows, say, 1000, which will usually get you by.

HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet<version of jet>EnginesExcel

Where <version of jet> is usually 4.0

Change the TypeGuessRows value to something larger than 8 and then you should be good to go.

Just another Microsoft head scratchier…had me stumped for about an hour! :)

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