T-SQL——关于SQL读取Excel文件

目录

志铭-2021年10月1日 18:28:27

0. 背景说明

  • 某系统上线，需要大量的数据初始化，用户提供的而是Excel文件。
    期望直接插入到SQL Server数据库的表中，所以可以按照以下步骤使用MSSM读取Excel表格中数据，实现Excel到SQL Server的数据批量导入

1. 安装Access Database Engine

  • 首先安装Access Database Engine 即需要安装Micsoft.ACE.OLEDB安装包

  • 因为我本机已经安装了Office2007（32位）

    • 在该种情形下，安装64位的Micsoft.ACE.OLEDB则会报错：

      装64(32)为office Access驱动的时候无法安装64(32)位版本的Office因为在您的PC上找到了以下32(64)位程序

    • 而此时我并不想卸载我的32位Office，或者服务器不允许我卸载32位的程序

    • 上述情形可以使用以下安装包安装对应位数的版本即可

    • 百度云链接: 2351144/2018rupg/未在本地计算机上注册“microsoft.ACE.oledb.12

  • 2024年7月31日10:49:11 参考T-SQL——关于安装 Mcrosoft.ACE.oledb.16.0出现的32位和64位的冲突问题


2. SQL脚本

说明：Excel表格是第一行默认是读取结果集的列名

--开启启用 Ad Hoc Distributed Queries 高级选项，
--在SQL Server中，该选项默认是Disable的，需要显式启用（Enable）；
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

--允许在进程中使用ACE.OLEDB.12
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;


--连接Excel表格的两种方式
--注意使用OpenDataSouce函数，后使用三个点后连接需要获取的工作簿名称
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=E:\1.xlsx')...[Sheet1$];
--注意OPENROWSET第二个参数是Excel中的工作簿名称
SELECT *
FROM
    OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\1.xlsx;hdr=yes;imex=1', Sheet1$);

--关闭第一开启的配置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

3. 使用MSSM的导入数据功能

可以通过MSSM的图形界面的导入数据的功能，将Excel数据导入到数据库

数据库->右键->导入数据 ，进入导入向导

选择Micsoft Excel格式的数据源，若是报错提示：“未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”

则还是默认32位的问题，可以从菜单栏选择“SQL Server2019 导入导出数据（64位）”执行导入向导，则不会在报错


4. .net项目中通过Micsoft.ACE.oledb读取Excel文件

见:.net程序读取Excel文件

posted @ 2021-10-01 18:37  shanzm  阅读(1610)  评论(1)  编辑  收藏  举报
TOP