避免Excel開啟CSV時截掉左補零的小工具是我三年前的作品,用來克服Excel開啟CSV時"00001"會變成"1"的問題。最近網友g提供了一個轉換失敗案例,引發我的興趣,檢查CSV後發現幾項問題:

  1. CSV內含日文,使用Shift-JIS編碼(ANSI)而非UTF8,當初將所有ANSI檔案視為BIG5,形成亂碼
  2. 部分欄位內容夾帶換行符號(如黃底所示),擾亂原本以"\r\n"分隔資料列的解析邏輯
  3. 程式未考慮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

Post a comment