Day 2

高效能 CSV 轉換引擎

分隔符號解析與編碼衝突實戰

自動化作業工具系列培訓

議程概覽

  • 模組一：業務痛點與自動化需求
    為何我們需要「另存 CSV 批次工具」？
  • 模組二：工具架構與功能解析
    資料夾遞迴處理與動態命名規則
  • 模組三：技術挑戰 - 編碼衝突
    UTF-8 vs. Big5 的亂碼災難與 BOM 解決方案
  • 模組四：技術挑戰 - 分隔符號解析
    跳脫字元與內嵌逗號的智慧型切割演算法

業務痛點與場景

傳統人工作業

  • 巨量資料表：單一 Excel 包含數十個工作表 (Sheet)。
  • 手動操作繁瑣：需逐一切換工作表，點選「另存新檔」。
  • 人為失誤率高：容易漏存、覆蓋或選錯檔案格式。

自動化目標

  • 批次處理：一鍵轉換資料夾下所有檔案。
  • 自訂彈性：允許使用者自定義分隔符號 (如 `,` 或 `|`)。
  • 標準化輸出：統一檔名格式，無縫對接後續系統。

批次轉換引擎：運作流程

1. 參數設定

指定來源資料夾、自訂分隔符號、目的資料夾

2. 遞迴掃描

自動尋訪資料夾內所有試算表檔案

3. 工作表解析

逐一讀取 Sheet 內容，處理資料列

4. 格式化輸出

套用編碼與分隔符，匯出獨立 CSV

工具參數與模式設定

系統提供兩種彈性的執行模式，適應不同作業規模：

  • 單一檔案模式：點選 轉換成CSV(單一檔案)。針對臨時性、單次任務，快速拆解單一試算表。
  • 批次資料夾模式：點選 轉換成CSV(資料夾)。適合定期報表彙整，無人職守自動化轉檔。

關鍵參數： 工具允許自定義 分隔符號，打破傳統 CSV 只能使用逗號的限制。

輸出命名規範與聚合

為了解決多個檔案、多個工作表轉換後的辨識問題，引擎實作了嚴格的自動化命名邏輯。

標準檔名產出格式

[原檔案名稱] _ [工作頁名稱] .csv
  • 避免不同試算表中的同名工作表 (例如都叫 "Sheet1") 在輸出資料夾中發生覆蓋。
  • 讓後續的資料庫匯入程式 (ETL) 能透過字串切割，自動解析資料來源檔與類別。

核心挑戰一：編碼地雷

當系統遇到非預期的文字編碼時...

資料轉換中最常見、也最致命的問題，就是字元編碼不一致導致的亂碼災難。

UTF-8 vs. Big5 的本質差異

Big5 (大五碼)

  • 傳統繁體中文作業系統預設。
  • 雙位元組字集。
  • 缺點：不包含簡體字、日文等，罕見字容易出現「缺字」或變成問號。
  • 舊版系統與傳統銀行電文常用。

UTF-8 (萬國碼)

  • 現代網頁與資料庫標準。
  • 可變長度編碼 (1~4 bytes)。
  • 優點：支援全球幾乎所有語系與符號，相容性極高。
  • 資料交換與開源系統的絕對主流。

常見的亂碼災難場景

症狀：打開 CSV 驚見「銝剖」

當使用巨集或腳本直接以 UTF-8 匯出 CSV，但在未經特殊設定的 Windows Excel 點擊開啟時，Excel 預設會以 ANSI (Big5) 進行解碼，導致全盤亂碼。

技術癥結點：

  • 原生的 VBA Open File For Output 指令預設使用系統 ANSI 編碼。
  • 若強制寫入 UTF-8，卻未加入辨識標記，多數桌面軟體無法自動判定。

解決方案：BOM 表頭機制

為確保產出的 CSV 既是 UTF-8 又能被 Excel 正確識別，我們必須在檔案最前端寫入 BOM (Byte Order Mark)。

' 概念展示：使用 ADODB.Stream 物件處理 UTF-8 寫入
Dim objStream As Object
Set objStream = CreateObject("ADODB.Stream")

With objStream
    .Type = 2          ' 指定為文字資料 (adTypeText)
    .Charset = "UTF-8" ' 設定編碼為 UTF-8
    .Open
    .WriteText strData ' 寫入轉換後的字串
    
    ' ADODB.Stream 儲存 UTF-8 時，預設會自動加上 BOM (EF BB BF)
    .SaveToFile strFilePath, 2 ' 覆寫檔案 (adSaveCreateOverWrite)
    .Close
End With

藉由 COM 物件 ADODB，我們跨越了原生語法的限制，確保編碼的精確控制。

核心挑戰二：分隔符號解析

「不就是用逗號分開而已嗎？」

這往往是初階開發者寫 CSV 解析器時，踩到的最大陷阱。

逗號不是唯一的選擇

考量以下這筆典型的金融或財報資料：

"公司名稱", "資本額", "備註"
"科技股份有限公司", "1,000,000", "穩定成長"
  • 如果直接使用簡單的字串切割函數 Split(text, ",")。
  • 錯誤結果： 金額欄位會被意外切成 "1、000、000" 三個無效欄位。
  • 這就是為什麼工具必須支援自定義分隔符，或具備智慧跳脫邏輯。

演算法：狀態機與跳脫邏輯

在轉換引擎的底層，我們不能依賴無腦切割，必須實作狀態機 (State Machine) 概念來組裝字串：

  1. 初始狀態： 逐字元讀取。
  2. 遇到雙引號 "： 進入「引號內」狀態。
    在此狀態下，所有遇到的分隔符號 (如逗號) 都視為一般文字，不執行切割。
  3. 再次遇到雙引號： 退出「引號內」狀態。
  4. 遇到自訂分隔符： 僅在「非引號內」狀態時，才將已讀取的緩衝區字串輸出為一個欄位。

架構回顧與效能優化

效能關鍵點

  • 記憶體管理： 在處理包含數萬筆紀錄的 Sheet 時，避免字串頻繁相加 (String Concatenation)，改用陣列或 Buffer 暫存，最後一次性 Join 輸出。
  • 物件釋放： 迴圈處理多個檔案與工作表後，確實關閉 ADODB.Stream 與 Excel Workbook 物件，避免記憶體洩漏 (Memory Leak)。
  • 背景執行： 關閉畫面更新 (ScreenUpdating = False) 大幅縮短巨集執行時間。

最佳實踐與防呆機制

穩定運作的自動化工具，必須包含完善的錯誤處理：

防呆檢查 (Foolproofing)

  • 檢查來源與目的資料夾是否存在。
  • 檢查分隔符號是否為空。
  • 檔案唯讀或已被開啟時的例外處理。

日誌紀錄 (Logging)

  • 紀錄成功轉換的檔案數量與名稱。
  • 捕捉轉檔失敗的具體原因並回報，而非讓程式直接崩潰。

Q & A

技術交流與討論

感謝聆聽 / 歡迎提問