OPENROWSET inserts null

I am using this:

insert into bla select * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=c:\bla.xls',
    'select * from [Sheet1$]');

but for 开发者_JAVA技巧some reason some values contain null although the original data definately contains values (e.g. 'abc'). What could be the reason for this strange behaviour. Thanks.

Chris

Try adding ";IMEX=1" (to read all fields as text - this could have to do with mixing values & text).

Second option is try to install Office Data Connectivity Components (from http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en) - and work around OLEDB. Then setup your OPENROWSET as such:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=c:\bla.xls;HDR=Yes;IMEX=1','SELECT * FROM [Sheet1$]');

Good luck!

I have found that setting Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows to zero and using the IMEX=1 option will fix the problem. There is meant to be a decrease in performance because it means the entire column/sheet is examined but I did not notice any thing of significance.