Sql-server – 64 bit Machine 32 bit Excel Using SQl Server Management Studion V17

excelsql server

I am using the below sql command to import an excel file (created in excel 2016) into SQL Server Management Studio on a 64bit Machine with 32 Bit Office.

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

But I am getting the following error:

Msg 7403, Level 16, State 1, Line 1 The OLE DB provider
"Microsoft.ACE.OLEDB.12.0" has not been registered.

I tried downloading 2007 Office System Driver: Data Connectivity Components but it didn't help.

Is it better to upgrade to 64 Bit Office? I only want part of the excel document to upload directly into a sql table. I can do the whole table via import but want to do it with VB or a Macro..

Best Answer

You'll need to use a more recent driver since the excel file was created in Excel 2016.

Install the following driver on the machine running the SQL Server Database Engine: Microsoft Access Database Engine 2016 Redistributable (64-bit)

https://www.microsoft.com/en-us/download/details.aspx?id=54920

After the driver is installed, open up SSMS and navigate to:

Linked Servers -> Providers -> right-click Microsoft.ACE.OLEDB.16.0: Enable "Allow In-Process"

From here, you can use the Microsoft.ACE.OLEDB.16.0 in your OPENROWSET command. I prefer to setup linked servers to these files, as follows:

EXEC sp_addlinkedserver @server='YourLinkedServerName',
                        @srvproduct='Excel',
                        @provider='Microsoft.ACE.OLEDB.16.0',
                        @datasrc='C:\File1.xlsx'

Then you can query the Excel file using the 4-part Linked Server syntax (the DB name and schema part are shorthanded here with ...):

SELECT * FROM [YourLinkedServerName]...[Sheet1$]

Related Question