While attempting to do some data merging across several Excel spreadsheets, I found some interesting behavior in how OLEDB determines data types of columns with an Excel source.
For me, the issue manifested itself with a column that looked like the following (shortened for brevity’s sake):
10132 10133 10134D 10134 10135
By default, Excel looks at the first 8 rows of each column to determine the type. If most of the items are numeric, it will create the column as an int or double type. The problem is, if the column also contains text data, like the one above (10134D), the column can’t be converted to a numeric type and OLEDB will just drop the value with no error or explanation.
With the above data, since 4 out of 5 are numeric, OLEDB turns the column into a numeric type, and the 3rd row’s column of that data would be NULL.
I tried several ways of fixing the issue but found that the only solution that worked was to set the IMEX property in the …Read More