When we talk about using import mode, "IMEX=1," to resolve a common problem when importing from Excel, we sometimes give or get the impression that setting IMEX=1 automatically imports all data as text. This is an over-simplification.
In fact, IMEX=1 causes the value of the Registry setting, ImportMixedTypes, to be applied. ImportMixedTypes commonly has a value of Text. Therefore, if and only if you have a column that contains both numbers and strings, all the values in that column are imported as strings.
However, if your columns do not contain mixed data types - if each column is purely strings or purely numbers - then the numbers remain numbers, and are imported as numeric values and not as text. That is, ImportMixedTypes is not applicable and is not applied. For some users, this may be an important clarification: You can't use IMEX=1 to force everything to text.
Here are the Registry settings for Jet's Excel driver:
Thanks to BI blogger and new MVP Todd McDermid for the comments that reminded me that my previous postings didn't clarify this point.