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

There is a potential problem when reading Excel files with an OleDbConnection.

If you use

  "Extended Properties='Excel 8.0;HDR=NO;IMEX=3;'"

for a column like the following where the first 8 rows have 4 (or more) numeric values, then the type is considered to be numeric and the string values are read as null.

Notice that the header is not used as a header here (HDR=NO) so the "zipcode" row is the first row. (These zip codes are from Sweden in case you don't recognize their format.)

 1) zipcode
 2) 125 45
 3) 115 50
 4) 18735
 5) 11335
 6) 13940
 7) 181 55
 8) 11759
 9) 176 74
10) 137 38

But if your data looks like this, where only 3 are numeric of the first 8 rows

 1) zipcode
 2) 125 45
 3) 115 50
 4) 18735
 5) 11335
 6) 139 40 <-- This one changed so that it is a string
 7) 181 55
 8) 11759
 9) 176 74
10) 137 38

then it works, it reads it all, as strings.

So the first case is a problem. But there is a solution.

Let's say you use

  "Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"

where we have changed IMEX to 1 and HDR to YES, then it will read the data as strings in both cases above. But let's say we have data like this

 1) zipcode
 2) 12545
 3) 11550
 4) 18735
 5) 11335
 6) 13940
 7) 18155
 8) 11759
 9) 17674
10) 137 38

then all of the first 8 data rows are numeric and then it again fails, even though we have IMEX=1.

You can solve this problem in the following way. Change the connection string to this

  "Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'"

Notice that we kept IMEX=1 but changed back HDR to NO.

Now row 1 is not treated as a header anymore and "zipcode" is read as data and since it is clearly a string, all rows are read as strings (that's how IMEX=1 works).

There are a couple of drawbacks with this method. Both can be solved:

1. You can't refer to the column by its name (zipcode) but need to use for example F7 depending on where the column is located.

You can solve this by figuring out where the zipcode column is located (can be done programmatically) and change the SQL text accordingly by changing "zipcode" to for example "F7".

2. The value "zipcode" will appear in your data.

This can be solved by having F7<>'zipcode' in your where clause. One might think that this would counteract the fact that we included zipcode (being a string) to make sure all rows are treated as strings. After having tested this it turns out though that the where clause trick that excludes "zipcode" does not have such a counteracting effect.