What is IMEX within OLEDB connection strings?

Question

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=localhost;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"

What is the purpose of IMEX=2 in the above connection string?

Puckery

Answer 1

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

Moskva

From here: instantpages.ltd.uk/ADODB_WP.htm "MEX=0 and IMEX=2 result in ImportMixedTypes being ignored and the default value of ‘MajorityType’is used." – Himself

Got it. It's used for columns with mixed data type. Thanks for the replies – Puckery

@Steve, Awesome Explanation. +1 for IMEX – Boucicault

Update to link in comments: web.archive.org/web/20080329055859/http://… – Study

Answer 2

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.

Smoker

Thanks for the detailed explanation. I am dealing with this issue with US Zip code. 5 digit zipcodes are treated as double values, and 5-4 zipcodes are ignored as they are strings :( – Yowl

Great explanation! What I do when using HDR=NO + IMEX=1 and querying into DateTable is to re-create column names from ROW 1, and then delete the first row. All data is treated as TEXT. – Sallet

Answer 3

When you are reading an excel file into a data table, the data table reads the column values and after about 8-10 records, it will assign data type to a column. For example if the column values are
11.0
22.0
33.0
44.0
55.0
66.0
77.0
88.0
99.0
abc
So, the data table won't have the value abc as the column has been assigned the data type "Double". To avoid this, and to read complete data IMEX = 1 is used.
Please, comment for further queries.

Nonbelligerent

I'm able to read data in text format with IMEX=1;HDR=NO; but problem is I'm getting formatted text data. for suppose, cell has value 0.15 and that cell has cell percentage formatting when we open xlsb we can see 15.00%, Oledb reading 15.00% but I need to read general data instead of formatted data. Please help. – Overdevelop

Answer 4

Please use a generalised function in a Module...

Public Function DeleteBlankRowsfromDataset(ByRef Dtset As DataSet) As Boolean
    Try
        Dtset.Tables(0).AsEnumerable().Where(Function(row) row.ItemArray.All(Function(field) field Is Nothing Or field Is DBNull.Value Or field.Equals(""))).ToList().ForEach(Sub(row) row.Delete())
        Dtset.Tables(0).AcceptChanges()
        DeleteBlankRowsfromDataset = True
    Catch ex As Exception
        MsgBox("Deleting Blank Records in Dataset Failed")
        DeleteBlankRowsfromDataset = False
    End Try

End Function

Ginnifer

While we appreciate the effort. Answers to questions should answer the question at hand. This answer may require further explanation. – Estellestella