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

Additional Material