sql - Get Excel sheet into temp table using a script

Question

Im trying to get this excel sheet into a table, so I can apply select statements to it etc, to update tables with its info.

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
          'Excel 8.0;Database=C:PB.xlsx',
          'SELECT * FROM [Sheet1$]')

Im running 64 bit machine. No idea what excel version it is.

Error:

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

When I change to Excel 12.0;

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Answer 1

For xlsx files (Excel 2007-2010) you can use the ACE oledb provider instead of the JET

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:PB.xlsx',
    'SELECT * FROM [Sheet1$]');