SQL Server 導入 Excel 實戰避坑指南：OPENROWSET 資料類型轉換與異常處理

場景：臨時性 Excel 匯入資料庫

使用 `OPENROWSET` 搭配 `Microsoft.Jet.OLEDB.4.0` (舊版 .xls) 或 `Microsoft.ACE.OLEDB.12.0` (.xlsx) 直接查詢 Excel 如資料表，無需 SSIS/導入精靈，適合臨時/輕量級 ETL。

核心語法與連線字串關鍵參數

-- .xls (Excel 97-2003)
SELECT * FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;IMEX=1;Database=\\Server\Share\File.xls',
    'SELECT * FROM [Sheet1$]'
);

-- .xlsx (Excel 2007+) - 需安裝 ACE Driver
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=\\Server\Share\File.xlsx',
    'SELECT * FROM [Sheet1$]'
);

關鍵屬性：

• `HDR=YES`：第一行為標題 (預設)。`HDR=NO` 則第一行為資料，欄位名為 F1, F2...。
• `IMEX=1`：混合模式 (關鍵!)。強制將混合型態欄位 (數字+文字) 視為文字讀取，避免「前 8 行是數字，第 9 行是文字」導致文字變 NULL 的經典 Bug。
• `Database`：UNC 路徑 (`\\Server\Share\File.xls`) **必須為 SQL Server 服務帳號可讀取的路徑**。本機路徑 (`C:\...`) 僅在 SQL 服務跑在同機器時有效。
• 工作表名稱：`[Sheet1$]` (帶 `$` 且方括號括起)；命名範圍則直接用名稱。

常見錯誤與解決方案

1. 「OLE DB 提供者不包含表 'Sheet1$'」

• 檔案路徑錯誤/權限不足 (SQL 服務帳號無讀取權)。
• 工作表名稱拼寫錯誤 (含空格/特殊字元) → 用 `SELECT * FROM OPENROWSET(..., 'SELECT * FROM [Sheet Name$]')`。
• 檔案被獨佔開啟 (Excel 開啟中) → 關閉 Excel 或複製副本讀取。

2. 數據類型推斷錯誤 (數字變 NULL / 科學記號 / 精度遺失)

根因：OLEDB 掃描前 8 行 (預設 `TypeGuessRows=8`) 推斷型別。若前 8 行純數字，後面出現文字 → 文字讀為 NULL；若長數字 (身分證/卡號/手機) → 變科學記號/截斷。

解決三板斧：

1. 強制 `IMEX=1` (最簡單，多數情況有效)。
2. 修改註冊表 `TypeGuessRows=0` (掃描全表推斷，效能稍影響) → `HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows` = 0。
3. 來源 Excel 欄位格式設為「文字」 (最徹底，但需業務配合)。

3. 匯入後 T-SQL 類型轉換最佳實踐

將 `OPENROWSET` 查詢結果 `INSERT INTO` 目標表時，建議在 `SELECT` 列表中**顯式轉換**而非依賴隱含轉換。

INSERT INTO dbo.TargetTable (CarNo, PuDate, GuidStr, Amount, DrNo, ...)
SELECT
    CONVERT(CHAR(4), car_no),                                    -- 定長字串
    CONVERT(DATETIME, [pu_date]),                                 -- 日期
    CAST(CAST([guid_col] AS DECIMAL(38,0)) AS VARCHAR(50)),      -- 長數字/科學記號 -> 精確字串 (關鍵技巧)
    CONVERT(DECIMAL(12,2), [amount_col]),                         -- 金額
    CONVERT(CHAR(4), dr_no),
    CAST(CAST([bigint_col] AS DECIMAL(38,0)) AS VARCHAR(50)),    -- 大整數避免溢位
    CAST(CAST([other_num] AS DECIMAL(38,0)) AS VARCHAR(50)),
    CONVERT(DATETIME, [other_date]),
    ISNULL([remark_col], '')
FROM OPENROWSET(...);

轉換邏輯解析：

• `CAST(col AS DECIMAL(38,0))`：先轉為高精度定點數 (移除科學記號、截斷小數)。
• `CAST(... AS VARCHAR)`：再轉為字串，保留完整數字文字表現。
• 避免 `FLOAT`/`REAL` (近似數值) 導致精度遺失；避免直接 `CAST(col AS VARCHAR)` 於文字型欄位可能保留前導空格/科學記號。

進階：64 位元環境部署陷阱

• 64 位元 SQL Server **無法**直接使用 32 位元 `Jet.OLEDB.4.0`。
• 方案 A：安裝 64 位元 `Microsoft.ACE.OLEDB.12.0` (Access Database Engine 2010/2016 Redistributable x64) → 用 `ACE` Provider 讀 `.xls`/`.xlsx` 通吃。
• 方案 B：啟用 32 位元 SQL Server 執行個體 (WoW64) 僅跑匯入任務 (不推薦)。
• 方案 C：用 SSIS / PowerShell / Python (pandas + `pyodbc`/`sqlalchemy`) 預處理後寫入 DB (架構更乾淨)。

替代方案：更穩健的匯入管道

總結：Excel 匯入黃金法則

1. **優先存為 CSV + BULK INSERT/BCP**，繞過 OLEDB Provider 地雷區。
2. **必須用 OPENROWSET** 時：`IMEX=1` + `ACE 64-bit Provider` + `UNC Path` + `顯式 CAST/CONVERT` + `錯誤輸出導向`。
3. **長數字欄位 (ID/卡號/手機) 一律當字串處理** (Excel 設文字格式 / `CAST(DECIMAL->VARCHAR)`)。
4. **生產環境禁用 `Ad Hoc Distributed Queries`** (`sp_configure 'Ad Hoc Distributed Queries', 0`)，臨時開啟用畢即關。