SQL Server 匯入 Excel 發生文字截斷問題：根因分析與三種解決方案

問題現象

使用「匯入/匯出精靈」將 Excel 匯入 SQL Server 時，長文字欄位被截斷 (預設 255 字元)，或出現錯誤：

錯誤 0xc02020c5：資料轉換失敗，文字被截斷...

根本原因

• Excel 來源資料採樣前 8 列 (預設) 推斷資料型別，若前 8 列較短，後方長文字會被視為截斷。
• 目的地資料表欄位長度 (如 VARCHAR(50)) 小於實際資料長度。
• 精靈預設「忽略錯誤」導致靜默截斷。

方案一：修改登錄擴大掃描範圍 (機器層級)

路徑：HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
值：TypeGuessRows = 0 (DWORD)  -- 0 表示掃描所有列
重啟 SSMS/精靈生效。

方案二：使用 OPENROWSET / OPENDATASOURCE T-SQL 直接匯入 (推薦)

-- 1. 啟用 Ad Hoc Distributed Queries
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

-- 2. 匯入並自動建立目標表 (欄位長度依實際資料)
SELECT * INTO [TargetDB].[dbo].[TargetTable]
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\Data\Source.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"')...[Sheet1$];

-- 3. 完成後關閉 Ad Hoc (安全考量)
EXEC sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

注意： Excel 檔需放在 SQL Server 伺服器本機磁碟，或透過 UNC 路徑且服務帳號有讀取權限。

方案三：精靈中手動調整欄位對應與錯誤處理

1. 精靈「選擇來源資料表與檢視」頁面，點擊 編輯對應。
2. 將問題欄位的 OutputColumnWidth 設大 (如 4000)，DataType 改為 DT_WSTR 或 DT_NTEXT。
3. 錯誤處理選擇 重新導向錯誤列 至錯誤輸出，而非忽略。
4. 目的地資料表預先建立，欄位長度足夠 (如 NVARCHAR(MAX))。

驗證結果

匯入後執行 SELECT MAX(LEN(LongTextCol)) FROM TargetTable 確認無截斷。

最佳實務建議

• 生產環境優先使用 SSIS 封裝 或 SqlBulkCopy 程式碼控制，避免精靈不確定性。
• Excel 來源欄位建議統一格式，避免混合型別。
• 大量資料分批匯入，監控記錄檔成長。