如何清理凌亂的 CSV 文件

為什麼出口到貨時破損

CSV 是一系列約定而不是一種格式。兩個應用程式都可以聲稱支援 CSV，但在分隔符號、引用樣式、行結束符和字元編碼方面存在分歧。

分隔符號是最常見的驚喜。在使用逗號作為小數點分隔符號的國家/地區銷售的軟體通常會匯出分號分隔的檔案，因為逗號已被佔用。這些檔案仍然以 .csv 結尾，並且假設逗號的工具將每一行折疊成一列。

編碼是第二個。 UTF-8 檔案通常以不可見的三位元組順序標記開始。 Windows 上的 Excel 使用它來識別 UTF-8；許多其他工具並不期望它，因此它會粘在第一個標頭名稱上，並且名為 id 的列默默地成為任何查找都不會匹配的列。

透過三個數字進行診斷

自動偵測使用每個候選分隔符號解析樣本，並選擇產生最一致的矩形結果的樣本，這就是為什麼它不會被分號分隔檔案的引用欄位內的逗號所欺騙。大多數時候這都是對的；預覽會告訴您何時不是。

• 列數。如果為1，則分隔符號錯誤。嘗試使用分號，然後使用製表符。
• 行數。如果它遠高於預期，則該檔案可能包含上游已損壞的引用換行符。
• 警告計數。每個警告都會命名一個行號，按照文字編輯器的計數方式進行計數，以便您可以查看。

閱讀警告

參差不齊的行意味著該行的單元格數量與標題不同。不會丟棄任何內容來整理網格：短行用空白填充，長行保留其額外值。默默地刪除某人的資料以使表格看起來是矩形是清理工具可以做的最糟糕的事情，所以它沒有這樣做。

未閉合的引號意味著雙引號被打開並且從未閉合，因此它後面的所有內容都被讀取為一個巨大的欄位。這幾乎總是原始碼中真正的損壞，值得修復檔案的生成位置，而不是在下游修補。

該工具會報告這些問題而不是修復它們，因為這兩種情況都有幾種可能的正確修復方法，並且只有您知道哪一種適用。

按正確順序清潔

「防公式注入」匯出預設啟用，也是此處唯一具有實際資安影響的選項。試算表應用程式會把以 =、+、- 或 @ 開頭的儲存格視為公式，因此來自不受信任來源的值可能在他人開啟檔案時執行。安全匯出會在這些儲存格前加上單引號，使其停止運算；您原本希望計算的儲存格也會一併停止，這是此緩解措施本身的取捨，而不是錯誤。

1. 首先修剪空白。僅尾部空格不同的兩行在修剪前不會重複。
2. 其次刪除重複的行。保留第一次出現的情況，因此存活順序不變。
3. 重新命名標題並選擇要匯出的列。
4. 如果需要的話排序。數字列被偵測並按數字排序，因此 9 排在 10 之前。
5. 下載前請檢查匯出選項，尤其是「防公式注入」設定。

選擇匯出編碼

如果檔案的目的地是 Windows 上的 Excel，則將 UTF-8 byte-order 標記打開，Windows 使用它來偵測編碼；如果沒有它，重音字元和非拉丁腳本可以在雙擊時顯示為 mojibake。

如果檔案提供腳本、資料庫匯入或其他程序，請將其保留為關閉，因為一些嚴格的解析器將標記視為資料。

工作範例：作為一列開啟的 12,000 行匯出

來自歐洲 CRM 的客戶匯出在編輯器中開啟為 12,000 行和一列。標題行顯示姓名；電子郵件；國家/地區；註冊日期。

1. 加載檔案。自動偵測報告4列；如果沒有，請手動將分隔符號設定為分號。
2. 閱讀警告：假設 418、2,905 和 7,110 處有 3 個參差不齊的行。
3. 看看那三行。它們在公司名稱中包含一個未轉義的分號 - 保留了額外的單元格，因此不會丟失任何內容。
4. 修剪空白，然後刪除重複。
5. 匯出為 CSV，使用逗號作為分隔符號並打上位元組順序標記，因為該檔案將發送給使用 Excel 的同事。

結果： 以逗號分隔的 UTF-8 檔案，帶有位元組順序標記、四列，並且重複的行消失了。三個有問題的行仍然存在，其額外的單元格完好無損，標記為供您在源頭修復，而不是刪除以使表格整潔。

開啟工具

在瀏覽器中清理 CSV

在您裝置上的 Web Worker 中進行解析，保留它找到的每個儲存格，並報告它無法猜測的問題。

這不包括什麼

• 整個檔案保存在您的裝置記憶體中。上限為 50 MB，手機比筆記型電腦更容易陷入困境。
• 儘管每行都已清理並匯出，但預覽中僅顯示前 100 行。
• 編碼偵測僅限於 UTF-8 byte 順序標記。儲存為 Windows-1252 或 Shift-JIS 的檔案將讀取為 UTF-8，並可能顯示替換字元；從來源應用程式將其重新匯出為 UTF-8。
• 只寫UTF-8。不產生舊編碼。
• 不支援多工作表，因為 CSV 沒有工作表的概念。
• 報告其引用已損壞的檔案，但不進行修復。
• 撤銷僅限於最近 20 次操作。