Pulling data from Excel worksheet tab into SQL Server

Question

Problem

I need multiple and repeated queries pulling data from Excel (ie I want query, not import). Excel workbook has multiple worksheets/tabs containing tables/named ranges. My solution already works with MS Access, but I am trying to get it to work with SQL Server. I see this has been asked several times previously, but I haven't been able to get it to work.

In the prototyping below, Excel file is Spread1.xlsm; one tab is named "Datasource". I created database "ExcelProto" for prototyping.

The two references listed below seem relevant. I have tried both the described ad hoc queries approach, and the linked server approach but both fail in a similar way. My adapted code:

First Approach: Linked Server prototype

USE ExcelLink
GO

EXEC sp_dropserver
@server= 'ExcelLink',
@droplogins= 'droplogins';
GO

EXEC sp_addLinkedServer
@server= N'ExcelLink',
@srvproduct= N'ACE 12.0',
@provider= N'Microsoft.ACE.OLEDB.12.0',
@datasrc= N'C:\TestProgs\Spread1.xlsm',
@location= NULL,
@provstr= N'Excel 12.0 Macro;HDR=YES',
@catalog= NULL;
GO

SELECT * FROM OPENQUERY (ExcelLink, 'Select * from [Datasource$]');

In this prototype code, I first dropped the linked server created in the previous execution attempt as you see; it couldn't be initialized anyway.

Linked Server Error Behavior and messages

ACE shows properly in the Providers tree. Code steps through properly into sp_addLinkedServer, arguments are properly evaluated and sp_addLinkedServer internal statements appear to execute properly. But on sp_addLinkedServer exit, execution stops and an error message is displayed:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelLink" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 19
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelLink".

Second Approach: Ad Hoc Query prototype Ad Hoc Query setup

USE ExcelProto
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Setup messages

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

Query

SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Macro;Database=C:\TestProgs\Spread1.xlsm;HDR=YES', 'SELECT * FROM [Datasource$]');

Query messages

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Questions

Same or similar error for both approaches. See any problem with my code adaptations? If the code looks okay, could the issue be assignment of permissions or roles and if so to what entities? Could it be limitations in Express? The references use ACE, but Microsoft docs refer to Jet for Excel... does ACE really work for Excel in SQL Server 2017? Is there a Jet for 2017?

Configuration

Windows 10 Pro x64. I am Admin.
SQL Server Express 2017 x64, SSMS 17.3
Installed Microsoft Access Database Engine 2010 Redistributable referenced in Links
Office 365 / (Excel 2016) 32-bit

References

How Do I Configure an Excel File as a Linked Server in SQL Server

Import Excel 2010 Into SQL Server

Access Database Engine Redistributable

** 27 Oct Update
Code for OPENROWSET and Linked Server, showing registration and initialization steps: **

OPENROWSET

USE ExcelProto
GO

/* Configure OLEDB */
sp_configure
  @configname='Show Advanced Options',
  @configvalue=1;
RECONFIGURE WITH OverRide;
GO
sp_configure
  @configname='Ad Hoc Distributed Queries',
  @configvalue=1;
RECONFIGURE WITH OverRide;
GO
EXEC master.sys.sp_MSset_oledb_prop
  @provider_name=N'Microsoft.ACE.OLEDB.12.0',
  @property_name=N'AllowInProcess',
  @property_value=1;
GO
EXEC master.sys.sp_MSset_oledb_prop 
  @provider_name=N'Microsoft.ACE.OLEDB.12.0',
  @property_name=N'DynamicParameters',
  @property_value=1;
GO

/* Pull in each Excel worksheet/table */
SELECT * FROM OPENROWSET(
N'Microsoft.ACE.OLEDB.12.0',
N'Excel 12.0 Xml; Database=C:\TestProgs\Spread3.xlsx; HDR=YES; IMEX=1',
'SELECT * FROM [Datasource$]'
);
GO

Took sp_MSset_oledb_prop from master.sys instead of master.dbo, hope that is okay; they do execute properly.

Linked Server and OPENQUERY

USE ExcelProto
GO

/* Configure OLEDB */
sp_configure
  @configname='Show Advanced Options',
  @configvalue=1;
RECONFIGURE WITH OverRide;
GO
sp_configure
  @configname='Ad Hoc Distributed Queries',
  @configvalue=1;
RECONFIGURE WITH OverRide;
GO
EXEC master.sys.sp_MSset_oledb_prop
  @provider_name=N'Microsoft.ACE.OLEDB.12.0',
  @property_name=N'AllowInProcess',
  @property_value=1;
GO
EXEC master.sys.sp_MSset_oledb_prop 
  @provider_name=N'Microsoft.ACE.OLEDB.12.0',
  @property_name=N'DynamicParameters',
  @property_value=1;
GO

/* Delete prior instances of Linked Server to each worksheet/table */
EXEC sp_dropserver
  @server= 'ExcelLink',
  @droplogins= 'droplogins';
GO

/* Create a Linked Server to each Excel worksheet/table */
EXEC sp_addLinkedServer
  @server= N'ExcelLink',
  @srvproduct= N'Excel',
  @provider= N'Microsoft.ACE.OLEDB.12.0',
  @datasrc= N'C:\TestProgs\Spread3.xlsx',
  @location= NULL,
  @provstr= 'Excel 12.0 Xml;HDR=YES;IMEX=1;',
  @catalog= NULL;
GO

/* Pull in each Excel worksheet/table */
SELECT * FROM OPENQUERY (ExcelLink, 'Select * from [Sheet1$]');

Registrations and initializations in place.

Access Database Engine 2010 was installed, no errors on install. Registry entries are correct for Microsoft.ACE.OLEDB.12.0, at Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.SQLEXPRESS\Providers\Microsoft.ACE.OLEDB.12.0

However, both OPENROWSET and Linked Server approach result in messages
OPENROWSET:
Msg 7303, Level 16, State 1, Line 27 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Linked Server:
Msg 7303, Level 16, State 1, Line 44 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelLink".

So neither can initialize Microsoft.ACE.OLEDB.12.0.

Altho no errors on install of Access Database Engine 2010, the Office 365 installation is 32-bit (MS recommended config!). Before I re-install that, I am going to try the latest SQL above on a machine with no Office installation at all.

This should answer your question about the differences between the drivers (basically ACE is newer and 64 bit supported): https://stackoverflow.com/questions/14401729/difference-between-microsoft-jet-oledb-and-microsoft-ace-oledb — Jacob H, Oct 10 '17 at 18:16
Also noticed your "database" is an XLSM file. Not XLS or XLSX. That may be the issue. I've never tried querying an XLSM with this method. Try with a blank XLSX file and see if you have the same errors perhaps? — Jacob H, Oct 10 '17 at 18:18
Finally, maybe try IMEX=1 in your connection string properties: https://stackoverflow.com/questions/10102149/what-is-imex-in-the-oledb-connection-string — Jacob H, Oct 10 '17 at 18:19
Thanks JacobH. Same result with blank .xlsx (changed file type to "Excel 12.0 Xml" and also tried just Excel 12.0), and tried IMEX=1 with both HDR=NO and HDR=YES... Still not able to initialize data source object of OLE DB provider. — netboyz, Oct 10 '17 at 19:59
Hmm... maybe x86 versus x64 version of the ACE driver? Maybe it is a file permission like on this post: https://blog.sqlauthority.com/2016/12/31/sql-server-cannot-initialize-data-source-object-ole-db-provider-microsoft-ace-oledb-12-0-linked-server/ — Jacob H, Oct 10 '17 at 20:20
Thanks again, JacobH. The target file is in a c:\directory on the machine running SQL Server, with at least Read & Execute permission to Users (of this machine), Administrators (of this machine), SYSTEM, and Authenticated Users. Does SQL Server, on the same system, use a member of one of those to access it? (the article I understood resolves by granting the client/user permission to the file path on the server). I'm trying to execute the query via SSMS on the server and am logged in to SSMS via Windows Authentication, so system, file access, and SQL logins should all match. — netboyz, Oct 10 '17 at 20:58
Separately, checking the ACE driver: Was installed with SQL Server, x64 but updated from 2013. If it was overwritten by the Access Redistributable, that install was also x64. — netboyz, Oct 10 '17 at 20:59

ASH · Answer 1 · 2017-10-26T19:03:03.250

Can you try it this way?

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

OR

SELECT * 
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\DataFiles\EmployeeData1.xlsx;
   Extended Properties=Excel 12.0 Xml')...[vEmployee$]

;

With Header:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=YES;
   Database=C:\DataFiles\EmployeeData1.xlsx',
   [vEmployee$]);

Without Header:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=NO;
   Database=C:\DataFiles\EmployeeData1.xlsx',
   [vEmployee$]);

https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/

I'm updating my original post here . . .

Check this link:

https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/

Also, I know people here don't like ONLY links posted, so I'll add some more info from the site listed above.

So let’s first of all enable this:


USE [MSDB]
GO

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

You can also enable this setting graphically by going to “Surface Area Configuration” and enable it.


–> Now, to access the Excel file there are 2 ways:

1. Directly fetch records from Excel by using the OPENROWSET() function by providing the providers and other options

2. Indirectly by creating a Linked Server first of all, then:
2.a. fetching records from Excel by using OPENQUERY() function or
2.b. by using the Linked Server name within the SELECT query


-- 1. Directly, by using OPENROWSET() function
SELECT * FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=E:\SQL Server - Blogs\LinkedServer2010.xlsx;HDR=YES;IMEX=1',
    'SELECT * FROM [Sheet1$]'
);

-- OR --

-- 2. Indirectly, by Creating Linked Server & using OPENQUERY:
EXEC sp_addLinkedServer
    @server= N'XLSX_2010',
    @srvproduct = N'Excel',
    @provider = N'Microsoft.ACE.OLEDB.12.0',
    @datasrc = N'E:\SQL Server - Blogs\LinkedServer2010.xlsx',
    @provstr = N'Excel 12.0; HDR=Yes';
GO

-- 2.a. Using OPENQUERY() function:
SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from [Sheet1$]')

-- 2.b. Using the Linked Server name within the SELECT query:
SELECT * FROM XLSX_2010...[Sheet1$]

I searched on net and I got following solution in MSDN forums to register the ACE OLEDB 12.0 provider:



USE [master]
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

Thanks for answer and link. --Both OPENROWSET and OPENDATASOURCE return Msg 7303, Level 16, State 1, Line 24 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". --Example of my implementation: SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; Database=C:\TestProgs\Spread3.xlsx', [Datasource$]); --My config: SQL Server Express x64, ACE x64; Office365 x86: ACE install issue? Shows okay in SSMS tho. — netboyz, Oct 26 '17 at 17:18
I just updated my post. I can't test it where I am now, but try what I just suggested and see if that works out for you. — ASH, Oct 26 '17 at 19:03
thanks again for input and link. I also found http://searchsqlserver.techtarget.com/tip/Using-the-OPENROWSET-function-in-SQL-Server, all on the same wavelength. Updated original post, labeled as 27 Oct: All SQL updates made, but still initialization error. This machine has 32-bit Office so will re-try code on machine with no Office installed at all. — netboyz, Oct 27 '17 at 17:48

Pulling data from Excel worksheet tab into SQL Server

1 Answers1