使用 T-SQL 查询 Excel 或 CSV 文件

Federico Di Marco

4.70/5 (4投票s)

2023 年 10 月 23 日

CPOL

5分钟阅读

16687

339

无需先导入到表格，即可使用 T-SQL 查询 Excel 或 CSV 文件

下载源代码 - 234.6 KB

引言

有时，无需通过 BulkInsert 将 Excel 或 CSV 文件导入表，就可以使用 SQL 语句直接查询它们。本文将详细介绍如何实现这一点，并使用了 Microsoft Access Database Engine，该引擎应该可以在 2005 年以来的所有 SQL Server 版本上运行。

SQL Server 设置 (只需执行一次)

在 SSMS 中启用临时分布式查询

sp_configure 'show advanced options',1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
GO

在服务器上安装Microsoft Access Database Engine Redistributable，我在 Microsoft 网站上找到的最新版本是2016 版 (点击此处)
通过在 SSMS 中输入以下命令，为 Microsoft Access Database Engine 启用InProcess和DynamicParameters

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1 
GO

重启 SQL Server。与 SSMS 重新连接后，您应该能在服务器对象 / 链接服务器 / 提供程序下看到Microsoft..ACE.OLEDB.16.0。

Using the Code

要查询 Excel 或 CSV 文件，您必须首先将其复制到 SQL Server 中一个 SQL Server 实例正在运行的用户帐户具有读取权限的文件夹（您可以通过运行services.msc，右键单击SQL Server (MSSQLSERVER) ->属性->登录选项卡->此帐户来了解是哪个帐户）。如果您遇到问题，可以仅为测试目的将其复制到一个文件夹（例如，c:\temp），然后在安全选项卡中（右键单击文件夹 ->属性-->安全选项卡）为EVERYONE用户添加读取权限。

如何查询 CSV 文件 (逗号分隔): 如果 CSV 文件的第一行包含列名，则可以将 HDR 参数设置为 YES。

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',_
   'Text;Database=<path to file.csv>;HDR=YES','SELECT * FROM <file.csv>')

如何查询 CSV 文件 (使用非逗号分隔符): 在这种情况下，您必须在包含 CSV 文件的同一个文件夹中创建一个schema.ini文件，并包含以下内容以指定分隔符（您可以为不同的文件指定不同的分隔符，只需重复此四行结构即可）
```
[<file.csv>]
ColNameHeader=True
CharacterSet=ANSI
Format=Delimited(<separator char>)
```
查询 CSV 的语句与上面相同，例如：
```
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',_
  'Text;Database=<only directories to file.csv>;HDR=YES','SELECT * FROM <file.csv>')
```
如何查询 Excel 文件 (.xlsx 或 .xls): 您必须使用不同的语句，并指定要查询的工作表名称（通常是 Sheet1$，但请注意 Excel 是本地化的，在不同语言中会有所不同）。
```
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0','Excel 12.0; _
         Database=<full path to excel.xlsx>', [<sheet name>$])
```

故障排除

您可能会遇到一些奇怪的错误，通常大多数都与安全问题有关。为了解决这些问题，我们必须理解 Microsoft.ACE.OLEDB.16.0 是一个COM 组件。COM 组件是 Windows 操作系统的一个核心部分，并且自 Windows 的早期版本（从 Windows 95 开始）就一直存在。基本上，COM 组件是一个服务器对象，它通过一组接口公开一项或多项功能，这些接口可供任何编程语言（因此也可供用不同于开发它的语言（通常是 C/C++）编写的程序）访问。您可能会认为此功能如今很普遍，但在 90 年代，互操作性很少。COM 组件在首次安装时始终在 Windows 注册表中注册，位于Computer\HKEY_CLASSES_ROOT\CLSID下，并具有一个 CLSID（一个 GUID）和一个 PROGID（一个有意义的名称，例如上面的 Microsoft.ACE.OLEDB.16.0）用于标识和实例化它们。COM 服务器可以通过三种方式“托管”

进程内 (In-Process): COM 服务器通常是一个 DLL，它会被动态加载并在调用程序的安全上下文中运行。因此，当我们配置 SQL Server 中的上述进程内选项时，Microsoft.ACE.OLEDB.16.0 DLL 会从 SQL Server 加载并在其中运行。
- 在 SQL Server 用户帐户下，如果您使用 SQL Server 身份验证连接到服务器。
- 在当前执行上述查询的用户下，如果您使用 Windows 身份验证连接。
进程外 (Out-Of-Process): COM 服务器通常是一个外部可执行文件，每当需要访问服务器提供的接口时都会自动运行。还有一个选项是通过使用一个标准的系统提供的代理 EXE（您是否在任务管理器中见过dllhost.exe :-) ？）来允许进程内 DLL 作为进程外运行。进程外托管可以在本地计算机或远程计算机上完成，在这种情况下，我们讨论的是DCOM - 分布式 COM。

DCOM 安全权限非常复杂且精细，事实上，我们有：

启动权限: 允许用户首次启动 COM 组件的用户。
访问权限: 允许用户访问已启动的 COM 组件（例如，使用其接口中提供的方法）的用户。因此，您可能被允许访问 COM 组件，但不被允许启动它（在这种情况下，必须由其他人为您启动，然后您才能访问它）。
配置权限: 允许用户使用命令行工具dcomcnfg.exe更改 DCOM 配置的用户。
标识 (在同名选项卡上): 指定 COM 组件运行的用户安全上下文，它可以是当前登录的用户（交互式用户）、启动服务器的客户端进程的用户帐户、指定的用户或服务。

COM 组件安全通过运行命令行dcomcnfg.exe进行配置。如果转到组件服务 \ 计算机 \ 我的电脑 \ DCOM 配置，您应该会看到所有注册的组件，负责实例化所有OLEDB 链接服务器提供程序的组件是MSDAINITIALIZE。

如果使用进程内托管运行 Microsoft.ACE.OLEDB.16.0，您应该可以通过以管理员身份启动 SSMS来解决大多数安全问题。如果您更喜欢（或需要）使用进程外托管，可以参考此 Microsoft Tech Community 文章，其中应该解释了如何通过调整 DCOM 配置来解决大多数问题。此外，此StackOverflow 帖子包含一些关于如何解决最常见问题的有用信息。

关注点

我附上了用于测试上述代码的示例 Excel / CSV /schema.ini文件。您可以在我的 GitHub 存储库 TSqlResources上找到源代码。

历史

V1.0 (2023 年 10 月 22 日)
- 初始版本
V1.1 (2023 年 11 月 3 日)
- 添加了故障排除部分，并简要解释了 COM