

Importing Excel files with mixed data types: Why some values are converted to NULL and how to get around that problem
Sample data to illustrate what happens with mixed data types during import:

Table results after importing file:
Values turned to NULL due to Majority Rule on Row Sampling that determines data type:

Row sampling based off registry setting “TypeGuessRows=#”
- TypeGuessRows # defaults to 8 with a max of 16
- Setting TypeGuessRows = 0 will scan all rows (per this article)
- Altering registry is obviously risky and rarely permitted (particularly on servers)
- TypeGuessRows = 0 would not be a good option in general
- Even if you set the max to 16 — any values that follow and don’t conform to the data type defined in the first 16 rows will all be imported as NULL
Enter IMEX
SSIS ConnectionString property: Extended property setting of IMEX
IMEX modes: 1 is Import , 0 is Export, 2 is Linked
All illustrations are in Import Mode

IMEX bypasses Majority Rule so that all values become text — as long as there is some mix of types within the row sampling:

But if IMEX only applies to the Row Sampling registry setting (TypeGuessRows = #) — what do you do about the rows that follow (9 and beyond below)?
Results in the following whether IMEX is used or not

IMEX Workaround for the above
Dummy row
