将数据从Excel工作表选项卡拉入SQL Server

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.

我需要多次重复查询从Excel中提取数据（即我想要查询，而不是导入）。 Excel工作簿具有多个包含表/命名范围的工作表/选项卡。我的解决方案已经适用于MS Access，但我试图让它与SQL Server一起使用。我之前已经多次询问过这个问题，但是我无法让它发挥作用。

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

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

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:

ACE在Providers树中正确显示。代码逐步进入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".

Second Approach: Ad Hoc Query prototype Ad Hoc Query setup

第二种方法：Ad Hoc Query原型Ad Hoc Query设置

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?

两种方法的相同或类似错误。看到我的代码调整有任何问题？如果代码看起来没问题，问题可能是权限或角色的分配，如果是，那么实体是什么？ Express可以限制吗？引用使用ACE，但Microsoft文档引用Jet for Excel ... ACE真的适用于SQL Server 2017中的Excel吗？ 2017年有喷气机吗？

Configuration

组态

• Windows 10 Pro x64. I am Admin.
• Windows 10 Pro x64。我是管理员。
• SQL Server Express 2017 x64, SSMS 17.3
• SQL Server Express 2017 x64，SSMS 17.3
• Installed Microsoft Access Database Engine 2010 Redistributable referenced in Links
• 已在Links中引用的Microsoft Access数据库引擎2010可再发行组件
• Office 365 / (Excel 2016) 32-bit
• Office 365 /（Excel 2016）32位

References

参考

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

如何在SQL Server中将Excel文件配置为链接服务器

Import Excel 2010 Into SQL Server

将Excel 2010导入SQL Server

Access Database Engine Redistributable

访问数据库引擎可再发行组件

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

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

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

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

从master.sys而不是master.dbo获取sp_MSset_oledb_prop，希望没关系;他们确实执行得当。

Linked Server and OPENQUERY

链接服务器和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

安装了Access Database Engine 2010，安装时没有错误。注册表项适用于Microsoft.ACE.OLEDB.12.0，位于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)".

但是，OPENROWSET和Linked Server方法都会导致消息OPENROWSET：消息7303，级别16，状态1，行27无法为链接服务器初始化OLE DB提供程序“Microsoft.ACE.OLEDB.12.0”的数据源对象“（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".

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

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

因此，两者都无法初始化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.

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

1 个解决方案

#1

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/

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

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/

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