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',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
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/252983.html

標籤：SQL Server

上一篇：TTL 生存時間

下一篇：SQL Server讀取及匯入Excel資料