Search code examples
sqlsql-serveropenrowset

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

Solution

  • 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!