Excel 資料匯入 Oracle 實戰：SQL*Loader 完整操作指南

前言

將 Excel 試算表資料批次匯入 Oracle 資料庫是常見需求。SQL*Loader 作為 Oracle 高效能資料載入工具，支援多種格式、錯誤處理與平行載入。本文以 Excel 2000 為例，詳細說明從另存文字檔、建立控制檔、執行載入到驗證結果的完整流程。

操作流程概覽

1. Excel 準備資料並另存為 Tab 分隔文字檔
2. 資料庫建立目標表結構
3. 編寫 SQL*Loader 控制檔
4. 命令列執行 sqlldr 載入
5. 查詢驗證與日誌分析

步驟 1：Excel 資料準備與轉檔

1. 開啟 Microsoft Excel 2000，建立新活頁簿
2. 輸入測試資料（範例：流水號、用戶名、密碼、建立日期）
3. 另存為 test.xls 備份
4. 再次「另存新檔」，存檔類型選 文字檔 (Tab 分隔) (*.txt)，檔名 text.txt，存放於 C:\

關鍵點：Tab 分隔檔的分隔符為 X'09' (ASCII 9)，SQL*Loader 控制檔需對應設定。

步驟 2：建立目標表結構

-- 以 system/manager 登入
CONN system/manager

CREATE TABLE test (
  id          NUMBER,           -- 序號
  username    VARCHAR2(10),     -- 用戶名
  password    VARCHAR2(10),     -- 密碼
  created_dt  VARCHAR2(20)      -- 建立日期
);

建議：日期欄位優先使用 DATE 型別，匯入後再轉型，避免格式不符導致錯誤記錄。

步驟 3：編寫 SQL*Loader 控制檔

建立 C:\input.ctl，內容如下：

LOAD DATA
INFILE 'C:\text.txt'
APPEND INTO TABLE test
FIELDS TERMINATED BY X'09'
(id, username, password, created_dt)

控制檔關鍵字說明

資料載入模式對照

• INSERT（預設）：表必須為空
• APPEND：追加新記錄，表可有資料
• REPLACE：刪除舊資料再載入（TRUNCATE 表）
• TRUNCATE：同 REPLACE，但使用 TRUNCATE 指令更快

步驟 4：執行 SQL*Loader 載入

開啟 DOS 命令提示字元：

C:\> sqlldr userid=system/manager control=C:\input.ctl

遠端資料庫連線

C:\> sqlldr userid=system/manager@serviceName_192.168.1.248 control=C:\input.ctl

預設輸出檔案

• 日誌檔：input.log — 載入統計、錯誤摘要
• 壞記錄檔：input.bad — 格式錯誤、約束違規的記錄
• 捨棄檔：input.dsc — 不符合 WHEN 條件的記錄（若有指定）

步驟 5：驗證載入結果

CONN system/manager
SELECT * FROM test;
SELECT COUNT(*) FROM test;

對比 input.log 中「Rows successfully loaded」與原 Excel 筆數，檢查 input.bad 確認異常資料。

進階技巧與常見問題

1. 日期格式處理

-- 控制檔中指定日期格式
created_dt DATE 'YYYY-MM-DD HH24:MI:SS'

2. 處理欄位含分隔符

-- 使用選擇性封閉字符
FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"'

3. 平行載入大幅提速

sqlldr userid=system/manager control=input.ctl PARALLEL=TRUE DIRECT=TRUE

• DIRECT=TRUE：直接路徑載入，繞過 SQL 層，速度更快
• PARALLEL=TRUE：多進程平行載入，需配合分區表或多個資料檔

4. 錯誤容忍與繼續

ERRORS=1000  -- 允許最多 1000 筆錯誤才停止

5. 常見錯誤代碼

• SQL*Loader-308：欄位長度超過定義 → 擴大 VARCHAR2 長度或截斷資料
• SQL*Loader-605：資料型別不相容 → 檢查控制檔資料型別指定
• ORA-01400：NOT NULL 欄位為空 → 資料檔缺值或映射錯誤

結語

SQL*Loader 是 Oracle 資料載入的利器，掌握控制檔語法、載入模式、直接路徑與平行載入技巧，能高效完成從 Excel、CSV、定寬檔等各類來源的資料遷移。生產環境建議先在測試環境驗證控制檔與資料樣本，確認無誤後再排程執行，並妥善保存日誌與壞記錄檔供稽核追蹤。