从Excel工作表标签中提取数据到SQL Server

Question

问题

我需要多次反复查询从Excel中提取数据（即我想查询，而不是进口）。 Excel工作簿具有多个包含表格/命名区域的工作表/选项卡。我的解决方案已经适用于MS Access，但我试图让它与SQL Server一起工作。我看到这个问题以前曾被多次提出过，但是我一直没有能够实现。

在下面的原型中，Excel文件是Spread1.xlsm;一个选项卡被命名为“数据源”。我为原型创建了数据库“ExcelProto”。

下面列出的两个参考文献似乎相关。我已经尝试了描述的即席查询方法和链接服务器方法，但都以类似的方式失败。我适应代码：

第一种方法：链接服务器原型

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$]'); 

在这个原型代码，我第一次把在以前执行尝试创建为你看到的链接服务器;无论如何它都无法初始化。

链接服务器的错误行为和信息

ACE正确显示在供应商树。通过正确的代码步骤进入sp_addLinkedServer，参数得到适当的评估，并且sp_addLinkedServer内部语句似乎可以正常执行。但上的sp_addlinkedserver退出，执行停止，显示的错误消息：

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

第二种方法：条件查询原型 条件查询设置

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 

建立消息

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. 

查询个

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

查询消息

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

问题

相同或类似的错误了这两种方法。查看我的代码适配的任何问题？如果代码看起来没问题，那么问题可能是权限或角色的分配，如果是这样的话，哪些实体？ Express可以限制吗？这些参考文献使用ACE，但Microsoft文档指的是Jet for Excel ... ACE在SQL Server 2017中是否真正适用于Excel？ 2017年有没有Jet？

配置

• 的Windows 10专业版64位。我是管理员。
• 的SQL Server Express 2017年的x64，SSMS 17.3
• 安装Microsoft Access数据库引擎2010可再发行 在链接引用
• 的Office 365 /（EXCEL 2016）32位

参考

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

Import Excel 2010 Into SQL Server

Access Database Engine Redistributable

** 10月27日更新
代码OPENROWSET和链接服务器，显示注册和初始化步骤：**

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 

拿了sp_MSset_oledb_prop从master.sys代替master.dbo，希望没关系;他们确实执行正确。

链接服务器和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$]'); 

注册和到位初始化。

Access数据库引擎2010已安装，没有安装错误。注册表项是正确的Microsoft.ACE.OLEDB.12.0，在Computer \ HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ MSSQL14.SQLEXPRESS \ Providers \ Microsoft.ACE.OLEDB.12.0

但是，OPENROWSET和链接服务器方法导致消息
OPENROWSET：
Msg 7303，级别16，状态1，行27 无法初始化链接服务器“（null）”的OLE DB提供程序“Microsoft.ACE.OLEDB.12.0”的数据源对象。

链接服务器：
消息7303，级别16，状态1，线44 无法初始化OLE DB提供程序 “Microsoft.ACE.OLEDB.12.0” 链接服务器 “EXCELLINK” 的数据源对象。

因此，既不能初始化Microsoft.ACE.OLEDB.12.0。

虽然安装Access数据库引擎2010时没有错误，Office 365安装是32位（MS推荐配置！）。在重新安装之前，我将在没有安装Office的计算机上尝试使用上述最新的SQL。

Answer 1

你可以这样试试吗？

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

;

随着头：

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

无头：

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/

我在这里更新我原来的职位。 。 。

检查此链接：

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

而且，我知道这里的人不喜欢贴ONLY联系，所以我会添加从上面列出的网站一些更多的信息。

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