Excel reading in ASP.NET : Data not being read if column has different data formats

Question

I have an asp.net C# application where i am reading the contents of a spreadsheet using OLEDBConnection. I am using the below line of code to read from the excel spreadsheet.

 OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");

One of my column has data in various formats like strings,numbers,date etc in various rows.When running this ,When the data format is different,its not reading that value fromthe excel file. I searched in net a lot and found that we need to mention IMEX proprety in connection string.I added that,but no positive response !.

After surfine a lot, ifound that Any built-in Excel driver will query the first 8 rows of a sheet and then make a determination (without your permission or knowledge) as to what type of column it is, thereby ignoring anything that doesn’t meet this data type later in the sheet.

http://www.mattjwilson.com/blog/2009/02/13/microsoft-excel-drivers-and-imex/

Is there anyway to get rid of this problem ?

Answer 1

You are running into one of the many fun features of the JET engine. This one will basically sample all the data in each row for a single column and it will try to guess the data format. If you want your code to "just work" then there is a registry setting that will help with this. However be forewarned that this registry setting will affect how JET works with all imports on a system, not just your particular import.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"ImportMixedTypes"="Text"
"TypeGuessRows"=dword:00000000

This registry setting will tell JET to check the format of every row in a column before guessing a format. If it finds mixed content it will import the row as text.

By default JET tests the first 25 rows when type guessing.

Alternatively you can change TypeGuessRows to 1 and JET will check the first row only when type guessing. That means if the first row is a number and the second row is a string JET will assume all rows are numbers and you will not be able to read them using ADO.NET

Another caveat: Make sure you are careful when editing your registry. You can decimate your system very quickly if you do not use care.