SQL Server 使用 OPENROWSET/OPENDATASOURCE 匯入 Excel 資料完整指南

前置需求

1. 啟用「Ad Hoc Distributed Queries」進階選項：

   EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
   EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

2. 安裝 Microsoft Access Database Engine (ACE) 驅動程式 (64 位元環境需 64 位元驅動)：
   • Office 2007/2010: Microsoft.Jet.OLEDB.4.0 (僅 32 位元)
   • Office 2010+ / ACE 2016: Microsoft.ACE.OLEDB.12.0 (支援 32/64 位元、.xlsx)
3. Excel 檔案儲存格式：.xls (Excel 8.0) 或 .xlsx (Excel 12.0 Xml)

方法一：OPENDATASOURCE (臨時連接)

中文工作表名稱 (需加方括號與 $ 符號)

-- 單一工作表匯入暫存表
SELECT * INTO #temp
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\物件編碼.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"')
...[辦公用品編碼$];

SELECT * INTO #temp
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\物件編碼.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"')
...[零件編碼$];

預設工作表名稱 (Sheet1、Sheet2...)

SELECT * INTO #temp
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\物件編碼.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"')
...[Sheet1$];

方法二：OPENROWSET (單次查詢)

-- 直接查詢不建立暫存表
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\物件編碼.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"',
    'SELECT * FROM [辦公用品編碼$]');

-- 指定欄位與條件
SELECT [品項代碼], [品項名稱], [單價]
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\物件編碼.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"',
    '[零件編碼$]')
WHERE [單價] > 100;

連接字串關鍵參數說明

常見問題與解決方案

錯誤：「找不到提供者 'Microsoft.ACE.OLEDB.12.0'」

• 安裝 Microsoft Access Database Engine 2016 Redistributable
• 64 位元 SQL Server 必須安裝 64 位元 ACE 驅動
• 若已安裝 32 位元 Office，需安裝 64 位元 ACE (passive 模式)：
  AccessDatabaseEngine_X64.exe /passive

錯誤：「無法啟動分散式查詢的 OLE DB 提供者」

• 確認 Ad Hoc Distributed Queries 已啟用
• 檢查 SQL Server 服務帳戶對 Excel 檔案資料夾是否有讀取權限
• 註冊表 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0 下設 DisallowAdhocAccess = 0 (DWORD)

資料型別推斷錯誤 (數字欄位讀成 NULL)

• 在 Extended Properties 加入 IMEX=1 強制文字模式
• 或建立 Schema.ini 檔案於同目錄強制指定欄位型別

進階應用：自動化匯入流程

CREATE PROCEDURE usp_ImportExcel
    @FilePath NVARCHAR(500),
    @SheetName NVARCHAR(100),
    @TargetTable NVARCHAR(200)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT * INTO ' + QUOTENAME(@TargetTable) + N'
        FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',
            ''Data Source=' + @FilePath + N';Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"'')
        ...[' + @SheetName + N'$]';
    EXEC sp_executesql @SQL;
END;

-- 使用範例
EXEC usp_ImportExcel 'D:\Data\Sales.xlsx', 'Sheet1', 'dbo.Staging_Sales';

效能優化建議

1. 大量資料優先使用 SSIS 封裝 或 BULK INSERT (配合 CSV 中間檔)
2. 避免在生產核心時段直接查詢 Excel，建議先匯入暫存表
3. 僅選取必要欄位，減少網路傳輸與記憶體壓力
4. 定期清理暫存資料庫 (tempdb) 避免空間耗盡

替代方案比較