【潛盾機】避免Excel開啟CSV時截掉左補零的小工具

老問題一枚。使用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 更新:已推出支援語系編碼選擇及欄位內含換行的強化版

歡迎推文分享:
Published 12 April 2012 06:29 AM 由 Jeffrey
Views: 28,784



意見

# chuck said on 11 April, 2012 09:00 PM

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

# lkk said on 27 March, 2013 09:08 AM

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

# 洋二 said on 27 August, 2013 03:32 AM

你好:

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

# Jeffrey said on 30 August, 2013 12:23 AM

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

#said on 28 July, 2015 09:52 AM

您好

非常謝謝您提供這個轉換程式。

剛剛下載您編譯過的程式檔,可以解決匯入excel時,把0去掉的問題。

但是如果某1儲存格中,資料有空行,修正後的資料就會亂掉,

請問版主有解決過這樣的問題嗎?

例如A欄位資料內容

配送日期

20140523

請送到門口

這樣的話往後一行資料就會亂掉,

另外,版主的這個程式是不是遇到日文就會變成亂碼?

非常感謝為小妹的解答!

# Jeffrey said on 28 July, 2015 09:39 PM

to g, 不太明白,你是指「某欄的內容包含換行符號」嗎?要用CSV處理欄位內含的換行符號有點棘手,我會傾向改用其他方式解決。(例如:用EPPlus或ClosedXML直接匯出Excel檔)

日文變亂碼問題應與CSV編碼有關,我猜設對編碼就可以克服,你能提供範例檔案嗎?

# g said on 03 August, 2015 11:52 AM

您好 

非常謝謝您願意回答我的問題,

由於檔案是從網路商店下載的購買資料,

因此無法由我這邊確定用什麼方法匯出excel

我可以提供範例檔案,請問範例檔案要什麼提供給您呢?

謝謝

# Jeffrey said on 03 August, 2015 08:16 PM

to g,

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

# g said on 04 August, 2015 10:03 AM

您好 請問我可以使用google drive分享嗎?

我方便從FB 先給您一個訊息,再請您將您的gmail 給我,

我將範例檔案從google drive 分享給您嗎?

謝謝

# Jeffrey said on 04 August, 2015 08:25 PM

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

# g said on 06 August, 2015 03:56 AM

您好

我剛剛把檔案寄過去了喔!

請查收!

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<April 2012>
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication