Reading Excel InterMixed DataType Without Modifying Registry Key

Question

I am trying to use C# to read excel file which has intermixed datatype. Below is my connection string

var path = //xls location
var MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties='Excel 8.0;IMEX=1;'");

Research taught me that the complete Extended Properties in the connection string is supposed to be

Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text

However, I was informed that in connection string, the TypeGuessRows=0 has no meaning as the value will be taken directly from the Registry. Hence I need to modify the key manually and remove this property from connection string.

The particular registry key that was involved is:

Path:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Key:

TypeGuessRows

Original value = 8, in order to make it work change into = 0

Without doing this the IMEX won't work even tough I add TypeGuessRows=0 into the Extended Properties.

However, my company forbids modifying registry value (strictly). I was told to find alternatives doing this.

In short:

Is there a way to read intermixed datatype excel file without having to modify any registry key (which is quite a common practice)?

Further topic:

Have you experienced this before? Are there possibilites that we can set TypeGuessRows=0 from the connection string only without having to modify the registry key (cancelling out my above premise).

If things don't work out with OleDb:

Are there alternatives beside OleDb?

I appreciate any advise or suggestion.

Regards

Solution 1

What you can do is to require having header in first row of Excel and set connection string to

var MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties='Excel 8.0;HDR=No;IMEX=1;'");

The key here is to set HDR=No (NO HEADER), however since you have header now each column will be treated as string (text), and you can do parsing or validation on each cell value. Of course you will need to skip or remove first row, since it contains header information.

Solution 2

Instead of using OleDb I know use Excel Data Reader. It works greatly! Highly recommended!

Comments

[Bronek]

Great discovery! Thanks!