IMEX=1

Solving problems with loading data from Excel files into databases

As partially explained here

ODBC/MS Jet scans first TypeGuessRows to determine field type

(TypeGuessRows=8 IMEX=1)

In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.
In your eight (8) scanned rows all of them less than 255 characters the provider will truncate all data to 255 characters
In your eight (8) scanned rows, if the column contains five (5) values with more length than 255 the provider will return more than 255 characters

1. Set IMEX=1 in the connection string
2. Close any programs that are running.
3. On the Start menu, click Run. Type Regedit and click OK.
4. In the Registry Editor, expand the following key depending on the version of Excel that you are running:
   1. Excel 97 (64bit)
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
   2. Excel 2000 (64bit)
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
   3. Excel 2007 (64bit)
      HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
   4. Excel 2010 (64bit)
      HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
   5. Excel 2013 (64bit)
      HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel
   6. Excel 2016 (64bit)
      HKEY_LOCAL_MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
   7. Excel 97 (32bit)
      HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\3.5\Engines\Excel
   8. Excel 2000 (32bit)
      HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel
   9. Excel 2007 (32bit)
      HKEY_LOCAL_MACHINE\Software\WOW6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Exce
   10. Excel 2010 (32bit)
       HKEY_LOCAL_MACHINE\Software\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
   11. Excel 2013 (32bit)
       HKEY_LOCAL_MACHINE\Software\WOW6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel
   12. Excel 2016 (32bit)
       HKEY_LOCAL_MACHINE\Software\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
5. Select TypeGuessRows and on the Edit menu click Modify.
6. In the Edit DWORD Value dialogue box, click Decimal under Base.
7. Set the value to 1
8. Open Excel file
9. Make sure that the cells in the first line of the table have relevant data for example
   1. mixed numbers and text characters for text fields
   2. only numbers for numeric fields
   3. If some of the data will be longer than 255 characters make sure that the first line cell has more than 255 characters otherwise it will be truncated

We have spent an enormous amount of time trying to get it fixed. So far we were not able to find a better solution.

The way Excel import works make it not possible to automate it. You have to modify most excel files manually in order to load them.

This is why we are no longer using ODBC/OleDB/Ms Jet for Excel connections. Our ETL solutions work correctly with Excel all the time

• Works correctly with all Excel versions
• No ODBC, OleDB or MS Jet Required
• Works correctly with mixed data types
• Works correctly with cells with more than 255 characters
• No need for IMEX=1, HDR=Yes or Registry hacks (TypeGuessRows)
• Loads data correctly all the time + no need to edit Excel file
• Can create Excel files in Excel 3.0-2016 format
• Can insert data starting from a specific cell
• Can clear area before adding data into Excel
• Can add headers

• Initializing the Microsoft Excel driver