经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MS SQL Server » 查看文章
SQL Server读取及导入Excel数据
来源:cnblogs  作者:缥缈的尘埃  时间:2021/2/1 11:52:15  对本文有异议

一、引言

使用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',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--2、允许在进程中使用ACE.OLEDB.12.0
exec 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.cnblogs.com/atomy/p/14297377.html

 友情链接: NPS