What Is Imex Within Oledb Connection Strings

What is IMEX within OLEDB connection strings?

From ConnectionStrings

"If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. .."

Please note that the IMEX value can be very important when you need to write back data to the Excel.
A fast search on Internet on IMEX found numerous articles about problems with various IMEX values

What is the default value of IMEX in OLEDB?

Per the article below, IMEX values of 0 and 2 do the same thing; they use ImportMixedTypes=MajorityType. That is the default if you don't specify IMEX=1 in your extended properties.

So the default IMEX behavior is MajorityType, and this can be overridden by using IMEX=1 in your connection string along with the registry setting ImportMixedTypes=Text.

http://www.instantpages.ltd.uk/ADODB_WP.htm

Reading Excel sheet using ACE.OLEDB.12.0 with IMEX=1 not working

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 suppose there must be a valid reason. If you find one let us know.

OleDBConnection with IMEX 1 ignores AM / PM in time value

The problem with ODBC is that it is notoriously buggy. It is hard to give correct advice when you do not have exact settings/office version/registry etc.

The IMEX=1 setting you have used sets only the "mode" which is an import mode. Then you have a registry setting ImportMixedTypes where you can specify two values: Text or Majority Type. The default setting is Text and that is the reason you are getting everything as text. For more details you can read my answer here.

Your issue of time is more complex than only converting it to text as you are trying. The excel itself has an internal representation of the time which you are then converting to text. That is the reason why you are missing the PM/AM part. The simpliest way out would be to use the 24-hour format, which you probably know and don't want.

Is there way out? Yes. You need to specify the column directly when you are connecting to the excel file:

...
var timeColumn = 
  new OleDbDataAdapter("SELECT FORMAT([Time], 'hh:mm tt') as [Time] FROM [sheetName]", oleDbConnection);
...

To see more options for the FORMAT see the MSDN.

IMEX in OleDbConnection

The problem is that there's a limit to 255 Characters doing it this way.

You will maybe need to look at using the Microsoft.Office.Interop.Excel assembly for a better solution.

http://www.dotnetperls.com/excel

Excel driver not reading data as text even if IMEX=1

IMEX=1 means that when the driver encounters mixed types in the first 8 rows that it should treat the column as text. Without it it will scan the first 8 rows, determine the data type and return null for those cells that do not correspond to the data type. It does not mean return everything as text.

Sadly the Microsoft.ACE.OLEDB.12.0 provider ignores ImportMixedTypes and TypeGuessRows in the connection string. They are set in the Registry. This is backwards step in my opinion.

If you have a header and it's text that will definately help return text for the columns. Try IMEX=1 and HDR=No and discard the header in subsequent processing.

Specification of Extended Properties in OleDb connection string?

I am using UDL file for that.

Do next:

1. create empty file test.udl
2. open it
3. You will see Data Link Properties dialog
4. On first tab change provider to Microsoft.Jet.OLEDB.4.0;
5. Second tab select you Excel file
6. Third tab set permission like Read
7. On last tab set Extended Properties = 'Excel 8.0; HDR=Yes'

Than save, and open file in text editor and you will see connection string

As well you can check msdn article ADO Provider Properties and Settings

---

Related Topics