IMEX=1 seems to have no effect

Question

I use ADODB to pull data from Excel file to another Excel file without opening the source. I have a strong suspicion that the IMEX=1 parameter is not accepted in my connection. I start my connection like this:

con1.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
 "Data Source=" & sFile & ";" & _
 "Extended Properties=""Excel 12.0;HDR=No;IMEX=1;"""

So I explicitly state that I want IMEX=1 to be used, but I find it not working, although it exists in the connection properties tree:

The Minimal, Complete, and Verifiable example

1. Create a file C:/Temp/Source.xlsx where you should enter these 9 values:

Save and close this file.

2. Paste the following code in any other (new) worksheet which should have a sheet named "Sheet1" and run it:

Sub PullData()

    Dim con1 As Object
    Dim rst1 As Object
    Dim sFile As String
    Dim x As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim arrData() As Variant, arrTransp() As Variant
    Dim rngTargStart As Range

    Set con1 = CreateObject("ADODB.Connection")
    Set rst1 = CreateObject("ADODB.Recordset")
    sFile = "C:\Temp\Source.xlsx"

    con1.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
     "Data Source=" & sFile & ";" & _
     "Extended Properties=""Excel 12.0;HDR=No;IMEX=1;"""

    'pulling
    rst1.Open "SELECT * FROM [Sheet1$A1:A9];", con1, 3, 1
    x = rst1.RecordCount '.GetRows sometimes yields "Record is too large". This line fixes it.
    arrData = rst1.GetRows
    rst1.Close

    'transposing
    Xupper = UBound(arrData, 2)
    Yupper = UBound(arrData, 1)
    ReDim arrTransp(Xupper, Yupper)
    For x = 0 To Xupper
        For Y = 0 To Yupper
            arrTransp(x, Y) = arrData(Y, x)
        Next
    Next

    'updating worksheet
    Set rngTargStart = ThisWorkbook.Sheets("Sheet1").Range("A1")
    rngTargStart.Resize(UBound(arrTransp, 1) + 1, UBound(arrTransp, 2) + 1).Value = arrTransp

    con1.Close
    Set con1 = Nothing
    Set rst1 = Nothing
End Sub

After running it I get this:

Any idea why, and how to fix it? I'll greatly appreciate any comments.

The full con1.connectionstring looks like this:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Temp\Source.xlsx;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False;

I use Office Pro Plus 2013, 32-bit on Windows 7, 64-bit.

Answer 1

IMEX does have effect, but it works in a crazy way : It depends on what proportion of data is returned as text by theTypeGuessRows parameter.

Detailed explanation is available here : https://web.archive.org/web/20211215203520/https://www.etl-tools.com/imex1.html

My solution is to always set IMEX=1 with TypeGuessRows=1 AND ensure my Excel files always include a first "dummy" row with the data type I need the ADO driver to interpret data as. This is also explained in this article.

Answer 2

So originally I thought that IMEX=1 prevents any evaluation of any number of values and just imports every value as text. Apparently one cannot prevent from evaluating values (by default 8 values are evaluated). What IMEX=1 actually does, is storing every value in a column as text ONLY if certain criteria are met on those 8 evaluated cells. I tested different sets of source data to understand how those 8 values are evaluated (which values take precedence over other in deciding if all values in a column will be treated as text, and therefore, imported). This is the result.

Text values > Numeric values > Text format > (Custom format) > Numeric format > formatted cells > General format empty cells

The values in bold lets the whole column be imported (treated as text), only if there are no values of higher precedence within those 8 evaluated cells. E.g., if within the first 8 cells there are 7 empty cells which are formatted as text and one cell which has an actual number inside, you will not have your text data imported (blank values will be imported instead).

Detailed results of testing:

If you have any actual text value within the first 8 cells, you will always succeed to import all the values in a column:

If there is any number value and no text values within the first 8 cells, you will fail to import text values in that column (blank values will be imported instead):

Now, if you have no values within your first 8 cells, and at least one of them is formatted as text, you will have your text values imported:

If all the 8 first cells are empty and if there is at least one number-formatted cell (no text-formatted cells), you will fail to import text values in that column (blank values will be imported instead):
_{However, the latter (only one numeric format) would successfully import text values in the column if the source workbook was not open in any instance of Excel at the time of import.}

Then we have empty formatted cells (e.g. color format) - text values in the column would be successfully imported:

Another case - empty unformatted cells, but with some data or formats in another column:

Finally, if there were no data or formats in other columns (perfectly cleared rows), your first rows might disappear from the output:

I skipped custom formats, as I think the outcome might depend on the actual format. My results were interesting, as when the source book was open, I successfully imported text data in the column, but when it was closed, I didn't. My guess is that custom formats, when the workbook is closed, might be treated as number formats.

Answer 3

This page explains it pretty well. Basically, it works out that the input column is numeric. When it gets to row 9 and finds text, it could crash and usually would. Since IMEX is set to 1, it doesn't crash, and instead returns a null for the value. If you had a 10th row with 8 in it, that would get imported as 8 into row 10.

However if IMEX is not set, then an error will be generated, so it's working as expected as far as I can see.