That looks much better so we load the data. Later the same day, the finance manager says that the balance is wrong you look at the data and there are some null values again.
Why we have null in the database?
It is a common problem: trying to load the data from Excel file half of the data is coming as nulls, or columns with more than 255 characters are truncated
The logic behind Excel mixed data types
Our source file has mixed data types some account codes have only numbers and some have characters as well. We have more cells with numbers only so Excel ODBC/Jet assumes the field type is numeric and shows the rest of it as nulls.
Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted into text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight-row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.
ODBC/MS Jet scans first TypeGuessRows to determine field type
Here how Excel ODBC/MS Jet works
In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.
In your eight (8) scanned rows all of them less than 255 characters the provider will truncate all data to 255 characters
In your eight (8) scanned rows, if the column contains five (5) values with more length than 255 the provider will return more than 255 characters
What else can we do?
We can change TypeGuessRows and set it to 0. This works very well for files up to 16384 lines.
The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384.\
For larger files, it may not work again, so the best solution to the problem is not to use excel as a data source but use text files instead. But this may bring another problem if user open CSV after it was exported it may lose leading zeros.
The only way to make import from Excel work is
Set IMEX=1 in the connection string
Close any programs that are running.
On the Start menu, click Run. Type Regedit and click OK.
In the Registry Editor, expand the following key depending on the version of Excel that you are running: