Is their an issue with importing Excel files with null rows into SQLServer 2005 tables

I am using openrowset() function to import a开发者_运维知识库n Excel file into a temporary SQLServer 2005 table.

It works fine in most cases. But if the first 10 rows of the Excel file are null, the remaining non-null rows are imported as null.

Has anyone encountered this issue previously? Any thoughts on how to overcome it?

This is caused by how the OLEDB driver determines the datatypes in Excel. By default it scans the first 8 rows to determine the datatype of the fields.

• You used to be able to set the "MaxScanRows" in the connection string to configure the rows to scan in the worksheet. This setting unfortunately does not work anymore since Jet 4.0. The only way to force it now is to set th registry key [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows]. Possible values are 1-16 or 0 to scan the whole file.
• There is also the IMEX setting (which means ImportMixedTypes) When you set it to 1 it means "ImportMixedTypes=Text". This solves a lot of issues as the driver determines the datatype based on the amount of values in a certain type it finds.