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

最新推荐文章于 2024-09-25 21:09:34 发布

寒冰屋

最新推荐文章于 2024-09-25 21:09:34 发布

阅读量127

点赞数

原文链接：https://www.codeproject.com/Tips/5370433/Query-Excel-or-CSV-files-with-T-SQL

版权

数据库专栏收录该内容

229 篇文章 14 订阅

订阅专栏

介绍

SQL Server安装程序（必须完成一次）

使用代码

故障排除

兴趣点

下载源代码 - 234.6 KB

介绍

有时，使用SQL语句查询Excel或CSV文件很有用，而不必通过BulkInsert将其导入到表中。本文详细介绍了Microsoft Access数据库引擎的使用方式，该引擎应适用于自2005年以来的所有SQL Server版本。

SQL Server安装程序（必须完成一次）

在SSMS中启用即席分布式查询：Enable Ad Hoc Distributed Queries in SSMS：

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

- 在服务器上安装Microsoft Access数据库引擎Redistributable，我能够在Microsoft网站上找到的最新版本是2016（点击这里）
- 通过输入SSMS为Microsoft Access数据库引擎启用 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 在“服务器对象/链接服务器/提供程序”下。

使用代码

若要查询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才能指定分隔符（您可以为不同的文件指定不同的分隔符，只需重复4行结构）：

[<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 95开始）就一直存在。基本上，COM组件是一个服务器对象，它通过一组接口公开一个或多个功能，每种编程语言都可以访问（因此，对于用与用于开发它的语言不同的语言编写的程序，通常是C/C++。您可能认为此功能现在很常见，但在90年代，几乎没有互操作性）。首次安装COM组件时，它们始终在Computer\HKEY_CLASSES_ROOT\CLSID下的Windows注册表中注册，并且具有用于标识和实例化的CLSID（guid）和PROGID（有意义的名称，例如，上面的Microsoft.ACE.OLEDB.16.0）。COM服务器可以通过三种方式“托管”：

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

DCOM安全权限非常复杂和细化，实际上，我们有：

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

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

如果运行具有进程内托管的Microsoft.ACE.OLEDB.16.0，则应该能够通过以管理员身份启动SSMS来解决大多数安全问题。如果您更喜欢（或需要）使用进程外托管来托管它，则可以查看这篇Microsoft技术社区文章，该文章应说明如何通过调整DCOM配置来解决大多数问题。此外，这篇关于StackOverflow的帖子提供了一些关于如何解决最常见问题的好信息。