While using OLEDB reading excel file, some cells return null even they got data on them.
Cause - The OLDEB driver will read the first 8 rows to determine the data type of each column.
If the first 8 rows of a column is numeric, but all the data is text from 9th row to the end. Then it can just read the first 8 rows data, all the others will return null.
Solution - Configure IMEX ( IMport EXport mode )
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
Set "IMEX=1" to the Extended Properties
e.g. Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Data.xls";Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
If it still happening even configure IMEX, then we can edit the registry:
HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\
Set TypeGuessRows = 0 (Default = 8)
