Excel CSV輔助工具強化版-支援換行符號
3 |
避免Excel開啟CSV時截掉左補零的小工具是我三年前的作品,用來克服Excel開啟CSV時"00001"會變成"1"的問題。最近網友g提供了一個轉換失敗案例,引發我的興趣,檢查CSV後發現幾項問題:
- CSV內含日文,使用Shift-JIS編碼(ANSI)而非UTF8,當初將所有ANSI檔案視為BIG5,形成亂碼
- 部分欄位內容夾帶換行符號(如黃底所示),擾亂原本以"\r\n"分隔資料列的解析邏輯
- 程式未考慮CSV部分欄位自帶雙引號的情況,造成雙引號重複(=""…"")。
用小工具轉換開啟會變成這副德行…
編碼問題好解,但CSV欄位內真的可以夾換行符號嗎?
實測發現,問題CSV若用Google試算表開啟,沒有亂碼,換行符號正確,但跟Excel一樣,第一欄020的前導零被刪去,仍不算完整的解決方案。
Google試算表解析成功帶來一些信心,足以推斷CSV欄位夾帶雙引號是有解的!經過測試,我發現只需將CSV檔轉成UTF8編碼,Excel也能正確解析雙引號包夾的換行,有個但書-雙引號前方不可再加上等於符號,否則會識別失敗。另外透過實驗得知,雙引號包夾內容若要用到雙引號,要用連續兩個雙引號代替,逗號則可直接嵌入,不需跳脫字元。
搞懂規則,要讓小工具搞定換行符號就不是難事。我採行的策略是逐字元讀入,隨時掌握目前是否處於雙引號包夾範圍,若在包夾內容出現換行符號(\r\n),先置換成ASCII 0x07 (BEL字元,DOS時代顯示字串時會嗶一聲,它在CSV出現機率幾乎為零,應無撞碼疑慮),之後再依原邏輯切割處理。另外,逗號(,)也要比照先換成0x08(Backspace)之後再還原,以免影響解析。
調整後的程式核心如下:
using (StreamReader sr = new StreamReader(fn, encoding, true)) { StringBuilder sb = new StringBuilder(); bool quotMarkMode = false; string newLineReplacement = "\x07"; string commaReplacement = "\x08"; //支援CSV雙引號內含換行符號規則,採逐字讀入解析 //雙引號內如需表示", 使用""代替 while (sr.Peek() >= 0) { var ch = (char)sr.Read(); if (quotMarkMode) { //雙引號包含區段內遇到雙引號有兩種情境 if (ch == '"') { //連續兩個雙引號,為欄位內雙引號字元 if (sr.Peek() == '"') sb.Append((char)sr.Read()); //遇到結尾雙引號,雙引號包夾模式結束 else quotMarkMode = false; sb.Append(ch); } //雙引號內遇到換行符號,先置換成特殊字元,稍後換回 else if (ch == '\r' && sr.Peek() == '\n') { sr.Read(); sb.Append(newLineReplacement); } //雙引號內遇到逗號,先置換成特殊字元,稍後換回 else if (ch == ',') sb.Append(commaReplacement); //否則,正常插入字元 else sb.Append(ch); } else { sb.Append(ch); if (ch == '"') quotMarkMode = true; } } var fixedCsv = sb.ToString(); sb.Length = 0; string line; using (var lr = new StringReader(fixedCsv)) { while ((line = lr.ReadLine()) != null) { string[] p = line.Split(','); sb.AppendLine(string.Join(",", //若欄位以0起首,重新組裝成="...."格式 p.Select(o => o.StartsWith("0") ? string.Format("=\"{0}\"", o) : //還原換行符號及逗號 o.StartsWith("\"") ? o.Replace(newLineReplacement, "\r\n") .Replace(commaReplacement, ",") : o ).ToArray())); } } //調整結果另存為同目錄下*.fixed.csv檔 string fixedFile = Path.Combine( Path.GetDirectoryName(fn), Path.GetFileNameWithoutExtension(fn) + ".fixed.csv"); //一律存為UTF8 File.WriteAllText(fixedFile, sb.ToString(), Encoding.UTF8); //開啟CSV Process proc = new Process(); proc.StartInfo = new ProcessStartInfo(fixedFile); proc.Start(); } |
另外,我加了讓使用者選擇CSV檔案編碼的功能。原本想做到自動識別,但ANSI識別要做到完全正確難度頗高,不如開放使用者自己選,只增加一點點不便,換取腦細胞少死數千 XD
為測試效果,設計了一個刁鑽案例,欄位內有換行有逗號:
強化版小工具挑戰成功!
原始碼已上到Github,會寫C#的朋友可以取回自行編譯使用,也歡迎參考、改寫。
另外FB群組則有編譯好的執行檔供程式麻瓜同學取用,大家使用上如遇到問題請再回饋給我。
Comments
# by Fish
仍不算「完義」的解決方案 <-- 抓到typo囉
# by Jeffrey
to Fish, 恭喜尋獲「錯字彩蛋」!(感謝指正)
# by Gigi
外語是我工作中常常遇到要轉換編碼的問題,我再把這不錯的資訊分享給程式人員。順便分享一個好康,就是下載指定APP,答對一個問題,就可以抽獎金NT20000/10000或太和工房保溫杯等禮品,快來點選囉!http://events.rti.org.tw/ajax/2015/2015mapp/index.aspx