乱雑な CSV ファイルをクリーンアップする方法

輸出品が壊れて届く理由

CSV は形式ではなく、一連の規則です。 2 つのアプリケーションは両方とも CSV サポートを主張し、区切り文字、引用スタイル、行末、文字エンコーディングについては同意しない可能性があります。

区切り文字は最も一般的な驚きです。小数点区切り文字としてカンマを使用する国で販売されているソフトウェアでは、カンマがすでに使用されているため、多くの場合、セミコロンで区切られたファイルがエクスポートされます。これらのファイルは依然として .csv で終わり、カンマを前提とするツールはすべての行を 1 つの列に折りたたむことになります。

エンコードは2番目です。 UTF-8 ファイルは、多くの場合、目に見えない 3 バイトのバイト オーダー マークで始まります。 Windows 上の Excel は、UTF-8 を認識するためにこれを使用します。他の多くのツールはこれを予期しないため、最初のヘッダー名に固定され、id と呼ばれる列が暗黙のうちにどの検索でも一致しない列になります。

3つの数字で診断する

自動検出は、各候補区切り文字でサンプルを解析し、最も一貫して長方形の結果を生成するものを選択します。そのため、セミコロンで区切られたファイルの引用符で囲まれたフィールド内のカンマによってだまされません。ほとんどの場合、それは正しいです。そうでない場合はプレビューでわかります。

• 列数。 1 の場合、区切り文字が間違っています。セミコロンを入力してからタブを入力してください。
• 行数。それが予想よりもはるかに高い場合は、ファイルには上流の何かがすでに破壊した引用符で囲まれた改行が含まれている可能性があります。
• 警告の数。各警告には行番号が付けられており、テキスト エディターでカウントされる方法でカウントされているため、確認することができます。

警告を読む

不規則な行は、行のセル数がヘッダーとは異なることを意味します。グリッドを整理するために何も破棄されることはありません。短い行には空白が埋め込まれ、長い行には余分な値が保持されます。テーブルを長方形に見せるために誰かのデータを黙って削除することは、クリーニング ツールが行うことのできる最悪の行為であるため、行われません。

閉じられていない引用符は、二重引用符が開かれ、決して閉じられていないことを意味するため、それ以降のすべてが 1 つの巨大なフィールドとして読み取られます。これはほとんどの場合、ソース内の本物の破損であり、ダウンストリームでパッチを適用するよりも、ファイルが生成された場所を修正する価値があります。

どちらのケースにもいくつかの正しい修正が考えられ、どれが適用されるかはユーザーだけが知っているため、このツールはこれらを修復するのではなく報告します。

正しい順序で掃除する

フォーミュラインジェクションセーフなエクスポートはデフォルトでオンになっており、ここでは真のセキュリティ上の結果をもたらす 1 つのオプションです。 =、+、-、または @ で始まるセルは、スプレッドシート アプリケーションでは数式として扱われるため、誰かがファイルを開いたときに、信頼できないソースから到着した値が実行される可能性があります。安全なエクスポートでは、これらのセルの前にアポストロフィが付けられ、計算が停止されます。これには、計算しようとしていたセルも含まれますが、これはバグではなく軽減策に固有のものです。

1. まず空白をトリミングします。末尾のスペースだけが異なる 2 つの行は、トリミングするまで重複しません。
2. 次に重複行を削除します。最初に出現したものが保持されるため、生き残る順序は変わりません。
3. ヘッダーの名前を変更し、エクスポートする列を選択します。
4. 必要に応じて並べ替えます。数値列が検出され、数値的に並べ替えられるため、9 は 10 の前になります。
5. ダウンロードする前にエクスポート オプション、特にフォーミュラ インジェクション設定を確認してください。

エクスポートエンコーディングの選択

ファイルの宛先が Windows 上の Excel である場合は、UTF-8 byte オーダー マークをオンにします。Windows では、エンコードを検出するためにそれが使用されます。これがないと、アクセント付き文字や非ラテン文字がダブルクリックで文字化けとして表示されることがあります。

ファイルがスクリプト、データベース インポート、または別のプログラムにフィードされる場合は、オフのままにしておきます。これは、厳密なパーサーによってはマークがデータとして扱われるためです。

実用的な例: 1 つの列として開く 12,000 行のエクスポート

ヨーロッパの CRM からの顧客エクスポートは、エディターで 12,000 行と 1 列として開きます。ヘッダー行には、名前、電子メール、国、サインアップ日が表示されます。

1. ファイルをロードします。自動検出レポートは 4 列です。そうでない場合は、区切り文字を手動でセミコロンに設定します。
2. 警告を読んでください。たとえば、418、2,905、7,110 にある 3 つの不規則な行です。
3. これらの 3 行を見てください。会社名の中にエスケープされていないセミコロンが含まれています。余分なセルは保持されるため、何も失われません。
4. 空白をトリミングし、重複を削除します。
5. ファイルは Excel を使用する同僚に送信されるため、区切り文字としてカンマを使用し、バイト オーダー マークをオンにして CSV としてエクスポートします。

結果: バイトオーダーマーク、4 列、重複行が削除されたカンマ区切りの UTF-8 ファイル。問題のある 3 つの行は余分なセルがそのまま残っており、テーブルを整理するために削除するのではなく、ソースで修正するようにフラグが付けられています。

ツールを開く

ブラウザで CSV をクリーンアップする

デバイス上の Web Worker で解析し、見つかったすべてのセルを保持し、推測できない問題を報告します。

これでカバーされないもの

• ファイル全体がデバイスのメモリに保持されます。上限は 50 MB であり、携帯電話はラップトップよりもかなり前に問題が発生します。
• すべての行がクリーンアップされてエクスポートされますが、プレビューには最初の 100 行のみが表示されます。
• エンコーディングの検出は、UTF-8 byte オーダー マークに限定されます。 Windows-1252 または Shift-JIS として保存されたファイルは UTF-8 として読み取られるため、置換文字が表示される場合があります。ソース アプリケーションから UTF-8 として再エクスポートします。
• UTF-8のみが書き込まれます。レガシーエンコーディングは生成されません。
• CSV にはシートの概念がないため、マルチシートはサポートされていません。
• 引用符がすでに破損しているファイルは修復されずに報告されます。
• 元に戻すことができるのは、最後の 20 操作までです。