TypeGuessRows=0 and IMEX=1 are not a reliable Excel import strategy. They are workarounds for drivers that guess column types, and guessing is a poor business process. Especially when the spreadsheet was built by someone who treats one column as a meeting room for numbers, dates, and random text.
If you load Excel files through ODBC, OleDB, MS Jet, or ACE, you can still lose values, truncate long text, or import perfectly good cells as null. Advanced ETL Processor avoids that problem by reading Excel files directly. No registry changes. No driver roulette. No scripting required.
Why TypeGuessRows=0 still causes Excel import problems
Excel drivers inspect sample rows and decide what data type each column should be. That sounds reasonable until row 1 contains an invoice number, row 9 contains text, and row 300 contains a comment longer than 255 characters.
Microsoft documents how the Excel driver initialises and interprets workbook data. In practice, the result is simple: the driver makes assumptions before your ETL process gets a proper look at the data.
More details from Microsoft: Initializing the Microsoft Excel Driver.
The spreadsheet looks correct in Excel. The import result is wrong. That is the sort of bug that wastes an afternoon and then asks for another one.
IMEX=1 helps sometimes, but it does not make imports safe
IMEX=1 tells the driver to treat mixed data more carefully. It does not guarantee correct results. If the sampled rows suggest a numeric column, later text values can still become null. If the driver decides a field is short text, long values can still be truncated.
Setting TypeGuessRows=0 can make the driver scan more rows, but it still leaves the import dependent on driver behaviour, registry settings, installed Office components, and bitness. That is too many moving parts for a scheduled ETL job.
What usually goes wrong with Excel ODBC imports
Data type guessing
- Mixed numeric and text values import as nulls
- Dates are treated as text or numbers
- Invoice codes lose leading zeroes
- Different machines produce different results
Text and layout issues
- Long text can be cut after 255 characters
- Named ranges behave differently from sheet ranges
- Blank sample rows confuse the driver
- Manual spreadsheet edits become part of the process
The better approach is to stop using the guessing driver
The simplest approach is to read Excel directly with an ETL tool built for spreadsheet variation. Advanced ETL Processor Enterprise supports Excel formats from Excel 3.0 to Microsoft 365. It can read sheets, ranges, tables, named ranges, and cells without relying on ODBC, OleDB, MS Jet, or ACE.
That means the workflow can stay fully automated. You do not need to edit the workbook before import. You do not need to change registry keys. You do not need a special machine with just the right driver installed. The spreadsheet can be ugly. We have seen worse.
What Advanced ETL Processor does instead
- Reads Excel files directly, without ODBC or OleDB
- Handles mixed data types without registry hacks
- Reads cells with more than 255 characters
- Works with old and current Excel formats
- Loads data into databases, files, and downstream ETL workflows
- Automates repeat imports through scheduled packages
The reality for many teamsWe receive thousands of Excel files each day. Modifying them manually was not sustainable. We tried ODBC, OleDB, SSIS, and custom macros. Nothing worked reliably. Advanced ETL Processor changed everything. It automated the work and saved us thousands of hours.
John Spoon
When not to use Advanced ETL Processor
If you only import one clean spreadsheet once a year, fix it by hand and move on. That is cheaper. If you receive Excel files every day, from different people, in different formats, automation starts paying for itself quickly.
For a related explanation of IMEX=1, see the IMEX=1 article. For product capabilities, compare the Advanced ETL Processor editions. If you want to test it with your own files, use the 30-day fully functional trial.
New data grid improvements
This release also improved the data grids used inside the product. These are small changes, but useful ones. The sort you notice after a long day of checking import results.
- Grouping panel for better data organisation
- Advanced filtering in grid views
- Record numbers displayed in data grids