IMEX=1

Solving Excel Data Import Issues in ETL Workflows

Common issue

When importing data from Excel, columns sometimes return nulls or long text values get truncated after 255 characters.

Understanding Excel’s Mixed Data Type Behavior

Microsoft Excel’s ODBC and MS Jet drivers use registry-based logic to detect column data types. This often results in unpredictable and incorrect imports, especially when handling mixed or long data.

More details from Microsoft: Initializing the Microsoft Excel Driver

How Excel Drivers Handle Mixed Data Types

Default settings: TypeGuessRows=8 and IMEX=1

5 numeric + 3 text → 5 values imported, 3 null
3 numeric + 5 text → 3 null, 5 values imported
4 numeric + 4 text → 4 values imported, 4 null
All cells < 255 chars → all data truncated to 255 chars
5 cells > 255 chars → values retained correctly

Note

IMEX=1 converts mixed data to text, but only if the first rows include text. If the first 8 rows are numeric, the column remains numeric - even if later rows contain text.

Nobody wants partial data. Everyone expects a complete and accurate import.

Conventional Fix Using Registry Settings (Not Ideal)

To work around Excel import issues using legacy drivers, follow these steps:

Set IMEX=1 in your connection string
Modify TypeGuessRows in the registry to 1
Ensure the first row contains representative data (e.g., long text for text fields)

Registry Paths for Different Excel Versions

64-bit Excel

Excel 2007-365:
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\[Version]\Access Connectivity Engine\Engines\Excel

32-bit Excel

Excel 2007-365:
HKEY_LOCAL_MACHINE\Software\WOW6432Node\Microsoft\Office\[Version]\Access Connectivity Engine\Engines\Excel

After locating the path, set TypeGuessRows to 1 (Decimal) using the registry editor. Restart your ETL tool or database server for changes to take effect.

Drawbacks of Using Legacy Excel Drivers

Despite registry tweaks, traditional Excel import drivers like ODBC, OLEDB, Jet, or even .NET often require you to modify spreadsheets manually before importing - which defeats the purpose of automation.

Testimonial
We receive thousands of Excel files each day. Modifying them manually was not sustainable. We tried ODBC, OleDB, then SSIS, even custom macros. Nothing worked reliably. Advanced ETL Processor changed everything - it saved us thousands of hours.
John Spoon

Why Switch to Advanced ETL Processor?

Excel import support

Works with Excel formats from 3.0 to 365
No ODBC, OLEDB, or Jet required
Handles mixed data types correctly
Supports cells with over 255 characters
No need for IMEX=1, HDR=Yes, or registry hacks

Automation options

Reliable data import with no Excel file modification
Create Excel files in legacy and modern formats
Insert data starting from a specific cell
Clear cells or ranges before inserting
Add headers programmatically

Our ETL tools always work reliably with Excel

Direct link - no registration required.

Next step

See the related product page for current features, editions, and trial downloads.

View Advanced ETL Processor Enterprise Download trial

Direct link, no registration required.

Company news, product updates, and practical articles from DB Software Laboratory.