解決 Excel 長文字匯入 SQL Server 2005 被截斷的三大方案

問題背景

使用 SSIS 或 ADO.NET 將 Excel 匯入 SQL Server 2005 時，若某欄位文字超過 255 字元，常遇到「文字被截斷」或「外部欄位與輸出欄位不匹配」錯誤。Excel 來源的資料類型預設最長僅支援 NVARCHAR(255)，甚至嘗試 NTEXT 仍無法解決。

根本原因

Jet / ACE OLE DB 驅動程式預設只掃描前 8 列（TypeGuessRows=8）來推斷欄位資料類型與長度。若前 8 列皆短於 255 字元，驅動程式會將該欄位定義為 NVARCHAR(255)，導致後續長資料被截斷。

方案一：轉為 CSV 再用平面檔案來源匯入

1. 將 Excel 另存為 CSV（UTF-8 或 ANSI，視編碼需求）。
2. 在 SSIS 使用「平面檔案來源」，手動將該欄位 OutputColumnWidth 設大（如 4000）。
3. 注意編碼問題：若含中文，建議先確認系統地區設定或使用 UTF-8 搭配 CodePage=65001。

優點：不需修改登錄表，相容性佳。
缺點：需額外轉檔步驟，大檔案較慢。

方案二：修改登錄表 TypeGuessRows = 0（推薦）

讓驅動程式掃描「整欄」資料決定型別，超過 255 字元自動判定為 NTEXT（舊版）或 NVARCHAR(MAX)（新版）。

登錄表路徑

操作步驟

1. 開啟 regedit，定位至對應機碼。
2. 找到 TypeGuessRows（DWORD），將值從 8 改為 0。
3. 若不存在，新增 DWORD 值 TypeGuessRows = 0。
4. 重啟 SSIS 服務或 Visual Studio 即可生效。

SSIS 連線管理員額外設定

• Provider 選 Microsoft ACE OLEDB 12.0（或 16.0）。
• Extended Properties 設為 Excel 12.0;HDR=YES。

方案三：在 Excel 端先用函數拆欄，匯入後再合併

1. 使用 LEFT()、MID() 將長欄位拆成多個 <255 字元的欄位。
2. 匯入 SQL Server 後，用 CONCAT() 或 + 合併還原。
3. 善用 Excel「選擇性貼上」只貼上數值，避免公式參考錯誤。

適用情境：無法修改伺服器登錄表的受管環境。

補充：使用 OPENROWSET / OPENDATASOURCE 直接查詢 Excel 長欄位

-- Jet 4.0
SELECT LEN(人員分工), *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:\book1.xls";Extended Properties=Excel 8.0')...[Sheet1$]
ORDER BY LEN(人員分工) DESC;

-- ACE 12.0 (建議)
SELECT LEN(人員分工), *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;IMEX=1;Database=c:\book1.xls', [Sheet1$])
ORDER BY LEN(人員分工) DESC;

IMEX=1 強制混合模式讀取，配合 TypeGuessRows=0 可正確讀取超長文字。

快速套用：下載現成 .reg 檔

文章提供 登錄表修正檔下載（請依實際 Office 版本與位數選擇），雙擊匯入即可完成設定。