OPENROWSET fails to import some data from Excel file

Context: SQL Server 2008, Excel 2007

When importing Excel data into SQL Server using OPENROWSET, if a column has 'mixed' data, i.e. it contains both numeric AND text values, not of all of that data may make it to the SQL Server. The reason lies in how the database driver works. It uses first several rows of the Excel file to determine type of values in columns. It then uses the infered type to import all values. BUT. If while importing it encounters a value that can be resolved to a different type, the driver just returns NULL for that value! Which gets inserted into the table in SQL Server.

To remedy this there are two things that need to be done:

1.Put "IMEX=1" into the provider string. This option tell to handle columns with mixed content (numeric and text) always as text.

The query should look like this:

SELECT * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Fee_matrix.xls;IMEX=1;',
'SELECT * FROM [tProduct$]')2.Set the following value in the system registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 0

This setting determines exactly how many rows at the beginning of the Excel file does the driver consider to determine the type of the values in the column. Zero means "use ALL rows". Be aware that this may affect performance if your Excel file is big.

P.S. Also, the OPENROWSET queries may not work right away. The following may help:

- Open SQL Server Mgmt Studio
- Right-click the server
- Facets
- Facet: Surface Area Configuration
- AdHocRemoteQueriesEnabled: True
- Ok your way out of the dialogs