Reading Excel sheet using ACE.OLEDB.12.0 with IMEX

Question

I'm using the below Connection String with ACE.OLEDB.12.0 to read data from an XLSX Spreadsheet, but the I set IMEX=1, it does not work while when I remove IMEX=1 completely, it works fine.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Working Folder\ICDE\Ramsden 4.xlsx;Extended Properties=""Excel 12.0 xml;HDR=No;IMEX=1;"""

Can anyone explain why? Because I had the impression that IMEX=1 reads all data as text, so it was more secure!

Thanks

JP

IMEX= <0/1/2> IMEX refers to IMport EXport mode. This can take three possible values.

IMEX=0 and IMEX=2 will result in ImportMixedTypes being ignored and the default value of ‘Majority Types’ is used. In this case, it will take the first 8 rows and then the data type for each column will be decided.

IMEX=1 is the only way to set the value of ImportMixedTypes as Text. Here, everything will be treated as text.

Answer 1

What worked for me, was to set IMEX=0 in the connection string. I have a SELECT and an UPDATE statement and just the SELECT statement was working when IMEX=1. Now both are working when IMEX=0

<add name="ExcleConn" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\ServerPath\Folder\GroupScheduling.xlsm;
     Extended Properties='Excel 12.0 Macro;HDR=YES;IMEX=0;Persist Security Info=False;ReadOnly=0;'"/>

Answer 2

IMEX=1 does not return all data as text. It's a very common misconception.

What OLEDB does is scan the first n rows (default=8) and determines a data type. If you leave out the IMEX=1 then it will return Null for any values that do not match that data type. If you include IMEX=1 and the scan encounters mixed data types then it will return text. If your sheet has a text header then you can help this process by specifying HDR=No and discarding the header. However OLEDB will always scan the first n rows to determine the data type and return results accordingly.

The Rows to scan is determined by the value of TypeGuessRows.

The older Microsoft.Jet.OLEDB.4.0 driver would allow you to specify TypeGuessRows in the connection string but Microsoft.ACE.OLEDB.12.0 does not. TypeGuessRows is now held in the registry under...

Excel 2007: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
Excel 2010: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
Excel 2013: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

32 Bit applications running on a 64 Bit Machine will find them under the Wow6432Node. E.g...

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

This is a retrograde step in my opinion but I there must be a valid reason. If you find one let us know.