Short fix to oledb blank values when importing excel file

If you are having trouble importing data from excel file and are receiving blank values, it is due to that the excel drive scans (by default) 8 rows and determines which is the predominant data value. This does not take into consideration that you have set your cell type to General or Numeric.

Also, adding the IMAX=1 in the connection string does not always work.

The upside to this is that you can change the default scan vaule from 8 rows to be between 1 and 16. This is done by adding MaxScanRows=1 to the connection string. Note that you can set it to 0 where it SHOULD scan all the rows, but there is a bug in the ODBC driver and the setting has no effect.

The simple solutions is to insert a blank row under the header and add a blank space as the first character.

http://support.microsoft.com/kb/257819

Rows to Scan: Excel does not provide ADO with detailed schema information about the data it contains, as a relational database would. Therefore, the driver must scan through at least a few rows of the existing data in order to make an educated guess at the data type of each column. The default for "Rows to Scan" is eight (8) rows. You can specify an integer value from one (1) to sixteen (16) rows, or you can specify zero (0) to scan all existing rows. This is done by adding the optional MaxScanRows= setting to the connection string, or by changing the Rows to Scan setting in the DSN configuration dialog box.

However, due to a bug in the ODBC driver, specifying the Rows to Scan (MaxScanRows) setting currently has no effect.

For example:

· 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.

About IMAX=1
http://support.microsoft.com/kb/194124 

Insure that the data in Excel is entered as text. Just reformatting the Excel column to Text will not accomplish this. You must re-enter the existing values after reformatting the Excel column. In Excel, you can use F5 to re-enter existing values in the selected cell.

NOTE: 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 to 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.

You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.