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