老問題一枚。使用Excel開啟CSV檔案,會l將純數字組成的字串視為數字型別處理,導致"000123"之類的左補零數字編碼變成"123"(如下圖所示),對必須補零到固定長度的編碼欄位來說,莫名被截掉部分內容,常會造成困擾。所幸,透過簡單的CSV花式技巧,在CSV中寫成="000123",就可強迫Excel將其視為文字處理,避免前方的零被截除。

遇到一個棘手情境: 某CSV檔需符合其他系統上傳規格,不能輸出成="000123"的形式,偏偏使用者又常需用Excel開啟CSV檔編輯後另存CSV再上傳。要符合上傳規格CSV內容就必須直接輸出000123,當使用者以Excel開啟時會有左補零被截除的困擾;若輸出成="000123",若使用者拿來直接上傳其他系統則會出錯。

面對兩難情境,想到幾個解決辦法:

  1. 提供"直接上傳"及"Excel編輯後上傳"兩種匯出版本,供使用者視用途下載。但這麼做有點把純Client端的Excel問題拉到Server端處理,還要防止使用者用錯版本,有讓問題複雜化的傾向。
  2. 請使用者不要直接點選CSV開啟,改用Excel的資料匯入功能,即可指定欄位資料型別。
     
    缺點是匯入文字檔操作步驟頗多,且須逐欄指定為文字格式,自己操作下來都嫌煩了,更甭提要求使用者將點兩下開啟的習慣改成一長串操作,別鬧了。
  3. 寫隻VBA巨集,透過程式讀入CSV檔也是個辦法,但在部署時有調整巨集安全設定的需求,將增加一點點客服負擔。

最後我決定寫一個.NET Windows Form小工具,讓使用者放在桌面上,當需要開啟這類含左補零數字CSV時,不要直接點選CSV開Excel,而是透過小工具選取檔案,小工具會讀取CSV內容,將所有欄位內容重新包裝成="…"格式,全部當成字串,再另存成*.fixed.csv(修正版),最後啟動Excel開啟修正後的CSV,避開左補零被截除的問題。

這個超迷你WinForm專案,完全不需要Form1.cs,在Program.cs裡花50行程式就可搞定。

using System;
using System.Linq;
using System.Windows.Forms;
using System.IO;
using System.Text;
using System.Diagnostics;
 
namespace NumCsvOpener
{
    static class Program
    {
        #region 偵測Encoding
        static Encoding big5Enc = Encoding.GetEncoding(950);
        public static bool IsBig5Encoding(string file)
        {
            if (!File.Exists(file)) return false;
            byte[] data = File.ReadAllBytes(file);
            return data.Length == 
                big5Enc.GetByteCount(big5Enc.GetString(data));
        }
        #endregion
 
        [STAThread]
        static void Main()
        {
            try
            {
                //由使用者挑選CSV檔
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "*.csv|*.csv";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    string fn = ofd.FileName;
                    //偵測檔案編碼再決定要用BIG5或UTF-8
                    Encoding enc =
                        IsBig5Encoding(fn) ? big5Enc : Encoding.UTF8;
                    using (StreamReader sr = new StreamReader(fn, enc))
                    {
                        StringBuilder sb = new StringBuilder();
                        string line;
                        while ((line = sr.ReadLine()) != null)
                        {
                            string[] p = line.Split(',');
                            sb.AppendLine(string.Join(",",
                                //重新組裝成="...."的格式
                                p.Select(o => string.Format("=\"{0}\"", o)).ToArray()
                                ));
                        }
                        //調整結果另存為同目錄下*.fixed.csv檔
                        string fixedFile = Path.Combine(
                            Path.GetDirectoryName(fn), 
                            Path.GetFileNameWithoutExtension(fn) + ".fixed.csv");
                        File.WriteAllText(fixedFile, sb.ToString(), enc);
                        //開啟CSV
                        Process proc = new Process();
                        proc.StartInfo = new ProcessStartInfo(fixedFile);
                        proc.Start();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
        }
    }
}

操作示意如下:

  1. 點選桌面上的CSV開啟工具程式
  2. 選取CSV檔案
  3. 程式呼叫Excel開啟修正後的CSV,克服左補零被截除的問題

2015-08-09 更新:已推出支援語系編碼選擇及欄位內含換行的強化版


Comments

# by chuck

這真的是很常見的問題...讚~

# by lkk

個人也很需求此協助,懇求賜教

# by 洋二

你好: 小弟是程式的外行人,但又需要您寫的這個功能,不知道有寫好的小程式可以分享嗎?謝謝~

# by Jeffrey

to 洋二,提供執行檔的風險較多,我通常會避免。不過如果你真的找不到會寫.NET的朋友幫忙,我放了一份已編譯的程式檔在FB群組: https://www.facebook.com/groups/darkthread/508244422592910/ 希望對你有幫助。

# by

您好 非常謝謝您提供這個轉換程式。 剛剛下載您編譯過的程式檔,可以解決匯入excel時,把0去掉的問題。 但是如果某1儲存格中,資料有空行,修正後的資料就會亂掉, 請問版主有解決過這樣的問題嗎? 例如A欄位資料內容 配送日期 20140523 請送到門口 這樣的話往後一行資料就會亂掉, 另外,版主的這個程式是不是遇到日文就會變成亂碼? 非常感謝為小妹的解答!

# by Jeffrey

to g, 不太明白,你是指「某欄的內容包含換行符號」嗎?要用CSV處理欄位內含的換行符號有點棘手,我會傾向改用其他方式解決。(例如:用EPPlus或ClosedXML直接匯出Excel檔) 日文變亂碼問題應與CSV編碼有關,我猜設對編碼就可以克服,你能提供範例檔案嗎?

# by g

您好  非常謝謝您願意回答我的問題, 由於檔案是從網路商店下載的購買資料, 因此無法由我這邊確定用什麼方法匯出excel 我可以提供範例檔案,請問範例檔案要什麼提供給您呢? 謝謝

# by Jeffrey

to g, 若CSV檔案來自第三方提供,調整自己的轉換程序看來是較簡便的做法。至於範例檔案,如果你有Dropbox,可利用連結分享功能( https://www.dropbox.com/zh_TW/help/167 ),其中如涉及個資或敏感資料在分享前記得先手動改掉。

# by g

您好 請問我可以使用google drive分享嗎? 我方便從FB 先給您一個訊息,再請您將您的gmail 給我, 我將範例檔案從google drive 分享給您嗎? 謝謝

# by Jeffrey

to g, 檔案可寄以下暫用信箱:tempbox@darkthread.net

# by g

您好 我剛剛把檔案寄過去了喔! 請查收!

Post a comment