SQL Server读取及导入Excel数据

一、引言

使用SQL Server的OPENROWSET及OPENDATASOURCE函数，可以像查询数据表一样来读取Excel数据。但是，要想让这两个函数能正常运行，可不是那么容易，假如没理解或没配置好的话，一路的报错会让你怀疑人生。

二、配置

2.1、组件安装

要想使用OPENROWSET及OPENDATASOURCE函数来读取Excel数据，首先要在目标的SQL Server主机上安装AccessDatabaseEngine组件。

1）换句话说：假如要操作的数据库是在本地的，那我在本地安装AccessDatabaseEngine即可；假如要操作的数据库安装在远程的服务器上，那么需在远程的服务器上安装AccessDatabaseEngine。

2）需要说明的是，读取Excel数据，只需安装AccessDatabaseEngine，并不一定要安装Office。

3）依目标的SQL Server主机的操作系统位数，来对应安装AccessDatabaseEngine版本。本处Excel是2013版本(.xlsx)，需安装Microsoft Access Database Engine 2010 Redistributable。下载地址

2.2、服务配置

在目标的SQL Server主机上，Win+R调出运行，输入services.msc调出服务。将SQL Server (MSSQLSERVER)、SQL Full-text Filter Daemon Launcher (MSSQLSERVER)两个服务的登录身份，改为本地系统账户。

2.3、参数配置

在目标的SQL Server上打开查询分析器，执行以下语句：

--1、开启导入功能（查看参数：exec sp_configure）exec sp_configure 'show advanced options',1reconfigureexec sp_configure 'Ad Hoc Distributed Queries',1reconfigure--2、允许在进程中使用ACE.OLEDB.12.0exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1--3、允许动态参数exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

2.3.1、开启导入功能对应的系统界面：

2.3.2、允许在进程中使用ACE.OLEDB.12.0及允许动态参数对应的系统界面：

三、测试

3.1、测试语句

在目标的SQL Server上打开查询分析器，执行以下语句：

--1、使用查询分析器查询EXCEL--注意1：若连接的是本机的数据库，E:\EDI\年度返利费用表.xlsx指的是本机的文件路径。--注意2：若连接的是远程的数据库，E:\EDI\年度返利费用表.xlsx指的是远程服务器的文件路径，可使用映射的方式将文件拷到远程服务器。SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;DATABASE=E:\EDI\年度返利费用表.xlsx',[Sheet1$])SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;DATABASE=E:\EDI\年度返利费用表.xlsx','SELECT * FROM [Sheet1$]')SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Extended Properties="Excel 12.0;HDR=YES;IMEX=2";Data Source="E:\EDI\年度返利费用表.xlsx"')...[Sheet1$]

1）特别需要注意的是文件路径，请特别留意上面代码注释的注意1及注意2。

2）OPENROWSET及OPENDATASOURCE函数，实现的功能是一样的，只不过写法有点不一样而已。

3）连接数据库的账号，服务器角色需为sysadmin。

3.2、测试结果

执行结果如下：

四、案例

能在查询分析上读取Excel数据之后，意味着能在代码上来读取Excel数据了。下面通过一个比较简单的VBA代码，将【年度返利费用表.xlsx】写入到远程数据库的BRC_AnnualRebateFee表中。

4.1、数据表建立

CREATE TABLE [dbo].[BRC_AnnualRebateFee]( [客户编号] [CHAR](10) NOT NULL, [品号] [CHAR](20) NOT NULL, [年度返利费用] [NUMERIC](16, 2) NULL, CONSTRAINT [PK_BRC_AnnualRebateFee] PRIMARY KEY CLUSTERED ( [客户编号] ASC, [品号] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

4.2、文件拷贝

将【年度返利费用表.xlsx】拷贝到远程服务器下的E:\EDI文件夹下。

4.3、VBA程序

1）在本地打开Excel2013，另存为xlsm格式。

2）点击"文件"->"选项"->"自定义功能区"，勾选"开发工具"。

3）点击"开发工具"->"插入"->"命令按钮(ActiveX 控件)"。

4）点击"设计模式"，然后双击命令按钮进入代码页。

5）点击"工具"->"引用"，勾选"Microsoft ActiveX Data Objects 2.0"，然后点击"确定"。

6）命令按钮代码如下：

 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '变量定义 Dim cn As ADODB.Connection, cmd As New ADODB.Command Dim strCn As String, strSql As String  '数据库连接 strCn = "Provider=SQLOLEDB;Data Source=erpserver;Initial Catalog=TEST;User Id=edi;Password=edi;" Set cn = New ADODB.Connection cn.Open strCn If cn.State <> adStateOpen Then  cn.Close  MsgBox "数据连接失败。", vbOKOnly, "提示"  Exit Sub End If  '命令对象赋初始值 With cmd  .ActiveConnection = cn  .CommandType = adCmdText  .CommandText = ""  .CommandTimeout = 0 End With ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '读取年度返利费用表 strSql = "TRUNCATE TABLE BRC_AnnualRebateFee" cmd.CommandText = strSql cmd.Execute  strPath = "E:\EDI\年度返利费用表.xlsx" strSql = "INSERT INTO BRC_AnnualRebateFee (客户编号,品号,年度返利费用) SELECT 客户编号,品号,年度返利费用 FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;Database=" & strPath & "','SELECT * FROM [Sheet1$]')" '第一种写法 'strSql = "INSERT INTO BRC_AnnualRebateFee (客户编号,品号,年度返利费用) SELECT 客户编号,品号,年度返利费用 FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;Database=" & strPath & "')...[Sheet1$]"   '第二种写法 cmd.CommandText = strSql cmd.Execute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '关闭连接 cn.Close Set cn = Nothing '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

原文转载：http://www.shaoqun.com/a/514858.html

跨境电商：https://www.ikjzd.com/

tiki：https://www.ikjzd.com/w/2053

skyee：https://www.ikjzd.com/w/290

一、引言使用SQLServer的OPENROWSET及OPENDATASOURCE函数，可以像查询数据表一样来读取Excel数据。但是，要想让这两个函数能正常运行，可不是那么容易，假如没理解或没配置好的话，一路的报错会让你怀疑人生。二、配置2.1、组件安装要想使用OPENROWSET及OPENDATASOURCE函数来读取Excel数据，首先要在目标的SQLServer主机上安装AccessDat
parser：parser
汇通天下物流：汇通天下物流
清远北站到古龙峡漂流打车多少钱?清远到古龙峡漂流一日游攻略：清远北站到古龙峡漂流打车多少钱?清远到古龙峡漂流一日游攻略
玩转亚马逊LD秒杀，这些成功推出秒杀的技巧你需要掌握：玩转亚马逊LD秒杀，这些成功推出秒杀的技巧你需要掌握
圆一个海岛梦 玩遍海南最美海岛：圆一个海岛梦 玩遍海南最美海岛