how to solve the error The OLE DB provider “Microsoft.Jet.OLEDB.12.0” has not been registered. when trying to extract data from excel

0
votes

I have an excel file that is sitting on my C drive and I would like to know how I can extract that data row by row validate it and insert into a sql table using openrowset. This is my query below

select * into crm_marketingdbclients_temptable FROM OPENROWSET('Microsoft.Jet.OLEDB.12.0', 'Excel 12.0;Database= ;C:\Temp\HighEarners.xlsx;;HDR=YES', 'SELECT * FROM [Sheet1$]')

I run this query and I keep getting an error that reads "The OLE DB provider "Microsoft.Jet.OLEDB.12.0" has not been registered." I am using office 365 on a 64 bit machine

1
sql-servertsqlstored-proceduresjet
Jet 12.0? Do you mean ACE? Jet can't read .xlsx files; it's designed for Excel 97-2003 files (xls) – Larnu

1 Answers

1
votes

Please use 64 bit engine, you are almost there. 

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

Check this post if you still have error.