前置配置四步驟
- 外圍應用配置器：服務和連接 → Database Engine → 服務 → 重啟服務
- 注冊表：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0
新建 DWORD (64位) DisallowAdhocAccess = 0
- 功能外圍配置：Database Engine → 即席遠程查詢 → 勾選「啟用 OPENROWSET 和 OPENDATASOURCE 支持」
- 啟用分布式查詢（每次用完建議關閉）：
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
-- 用完後
EXEC sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
導入語句兩種寫法
OPENDATASOURCE + SELECT INTO（建新表）
SELECT * INTO y
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="G:\1.xls";Extended Properties="Excel 8.0;HDR=YES;IMEX=1"')...[Sheet1$];
OPENROWSET + INSERT INTO（追加到現有表）
INSERT INTO x
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;DATABASE=G:\1.xls', 'SELECT * FROM [Sheet1$]');
關鍵參數詳解
| 參數 | 值 | 含義 | 建議 |
|---|
| Provider | Microsoft.Jet.OLEDB.4.0 | Jet 4.0 驅動（僅 32 位，支持 .xls） | 32 位環境用 |
| Provider | Microsoft.ACE.OLEDB.12.0 | ACE 12.0 驅動（支持 .xls/.xlsx，有 64 位版） | 64 位/新版本首選 |
| Extended Properties | Excel 8.0 | Excel 97-2003 格式 | .xls 固定用 8.0 |
| Extended Properties | Excel 12.0 Xml | Excel 2007+ (.xlsx) | .xlsx 用此值 |
| HDR | YES / NO | 第一行是否列名 | 標準表頭用 YES |
| IMEX | 0 / 1 / 2 | 導出/導入/連接模式 | 讀取必用 1 |
IMEX 三種模式深度解析
- IMEX=0 導出模式：僅寫入，讀取會報錯或返回空
- IMEX=1 導入模式（只讀）：混合類型列按文本讀取，避免數字列前幾行是數字後面是文本導致文本行讀為 NULL，**讀取 Excel 必須設置 1**
- IMEX=2 連接模式：可讀可寫，但鎖定文件，併發差，生產慎用
64 位環境部署避坑
- 安裝 Microsoft Access Database Engine 2010/2016 Redistributable (x64)
- 連接字串改用 ACE：
Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'
- SQL Server 服務賬號需有文件讀取權限（或用 SQL 代理代理賬號）
- 若報「找不到安裝可用的 ISAM」，檢查驅動位數與 SQL Server 進程位數一致
常見錯誤對策
| 錯誤 | 原因 | 解決 |
|---|
| 無法初始化數據源 | 文件被佔用/路徑錯誤/權限不足 | 關閉 Excel、用 UNC 路徑、授權服務賬號 |
| DisallowAdhocAccess | 注冊表值為 1 或缺失 | 設置為 0 並重啟 SQL 服務 |
| Ad Hoc Distributed Queries disabled | 未啟用分布式查詢 | 執行 sp_configure 啟用 |
| 工作表名不存在 | Sheet 名稱錯誤/帶空格 | 用 [Sheet1$] 含 $ 且方括號 |
| 數據類型不匹配 | IMEX=0 導致混合列讀空 | 強制 IMEX=1 |
最佳實踐
- 導入前關閉 Excel 文件
- 大數據量分批導入，避免長事務鎖表
- 臨時表中轉，清洗驗證後再寫業務表
- 導入腳本參數化，納入部署流程
- 定期清理導入產生的臨時表/備份表
