数据库大师成长日记：在SQL中读取和使用Excel数据

朋友们，我们在使用SQLServer时，可能会碰到需要从外部的Excel读取数据的情况。比如我们实施ERP系统时，客户将数据整理在Excel中，要求我们批量导入系统。碰到这样的问题，应该如何解决呢？

总体来说，有两种方法可以使用。第一种方法就是通过MSSQL管理器自带的导入工具，一步步将Excel的工作表导入SQLServer成为一个数据表；另一种方法则是通过SQL语句直接读取Excel的工作表。如果您是开发人员，您会用哪一种方法呢？

当然是第二种方法最方便了，但也有朋友喜欢第一种方法。两种方法有本质的区别，导入方法要求Excel文件在MSSQL管理器本地即可mssql 管理器，SQL直接查询方法要求Excel文件要放在SQLServer服务器上。

今天我们就两种方法的使用做详细的说明。

数据准备

为了便于说明，我准备了一个Excel文件。这是一个简单的销售表，Excel文件名称“SQL.XLS”、工作表名称为“销售表”。如下图所示：

利用MSSQL管理器的导入工具导入

这种方法比较保险，各种Excel文件基本都可以导入，而且待导入的Excel文件在管理器所在的电脑，而不是服务器。在我们无法控制服务器电脑时，这种方法就很有用。

操作步骤如下：

1、在数据库上点右键，弹出菜单选择导入。

2、选择Excel数据源。

3、选择本地Excel文件，并设置Excel的版本。

4、选择和设置导入到的数据库。

5、选择Excel工作表，并设置导入的表名

6、完成导入。

导入成功后，数据库中就会多出一个表。

使用SQL语句直接访问Excel文件

SQLServer提供了行集函数OPENROWSET实现对外部数据源的读取，要求待读取的Excel必须在服务器的某个硬盘下。

执行语法如下：

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\SQL.xls;hdr=yes;imex=1', 销售表$);

如果我们直接执行可能会报错：

尚未注册 OLE DB 访问接口 "Microsoft.ACE.OLEDB.12.0"

碰到这样的情况，我们首先要启用Ad Hoc Distributed Queries，脚本如下：

EXEC sp_configure 'show advanced options',1
reconfigure
EXEC sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

如果服务器没有安装AccessDatabaseEngine_X64.exe驱动，还需要先安装驱动，可以到微软官网下载。如下图：

驱动安装成功时提示如下：

做完这些工作，我们就可以开始工作了。再次执行脚本：

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\SQL.xls;hdr=yes;imex=1', 销售表$);

效果如下：

希望对您有所帮助！

（编辑：武陵站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!