64 bit Machine 32 bit Excel Using SQl Server Management Studion V17

Question

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

Answer 1

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

Answer 2

Those drivers are a total pain in the ass to deal with.

You are better off using the SQL server data export wizard (or the import one, if you are importing) and saving the package, which you can then execute from a scheduled job.