日本語

データとスプレッドシート · CSV クリーナー

Excel が CSV フィールドを静かに破損する理由: 先頭のゼロ、日付、長い ID

· なぜそれが重要なのか

投稿ナビゲーション ToolAcre · 編集レビュー

csv データクリーニング スプレッドシート

スプレッドシートで解釈された数値の横に先頭のゼロを保持する生のテキスト識別子
オリジナル ToolAcre ベクトル イラスト

スプレッドシートで CSV を開くことは中立的ではありません。郵便番号はゼロを失い、コードは日付になり、長い識別子は科学表記法に変わります。この投稿では、この問題が発生する理由、回復できないもの、および RAW ファイルを無傷で保存する方法について説明します。

誰かが開くまでは正常だったファイル — 郵便番号 01234 が 1234 になり、損傷は保存されます

ファイルは郵便番号 01234 でソース システムから出て、スプレッドシートで開かれ、1234 でアナリストに戻ります。元の CSV は不正な形式ではなく、先頭にゼロが付いているテキストが含まれていました。この違いは、プログラムがその列が数値であると推測し、その解釈された値を保存したときに発生しました。誰かがソース ファイルを上書きすると、後のクリーニング ツールは、1234 が 01234 を意味していたのか、001234 を意味していたのかを知ることができません。スプレッドシートで検査する前に、エクスポートをそのままにしておきます。

オープン時の型推測 - スプレッドシートがテキストから数値、日付、数式をどのように推測するか、そして CSV がそれ以外の判断を与えない理由

CSV は行、区切り文字、および引用符で囲まれたテキストを表します。列ごとの番号、日付、または郵便番号タイプは宣言しません。スプレッドシートを直接開くと、推測する必要があります。一部のアプリケーションでは、数字で始まる値は数値になることがあり、03-04 はロケールに従って日付として解釈され、= で始まる値は数式として解釈されることがあります。 「01234」を引用符で囲むと、自動インポート ウィザードによる将来のテキストとしての分類ではなく、CSV 区切り文字が保護されます。

古典的な犠牲者 — 先頭のゼロ、長い数値識別子、日付に似たもの、等号で始まる値

郵便番号や SKU の先頭のゼロ、スプレッドシートの数値形式で保持できる有効桁数を超える識別子、および日付が曖昧な文字列は、典型的な被害者です。科学表記法自体は破損ではなく、表示上の選択です。しかし、数値変換後に長い ID を保存すると、正確な桁が失われる可能性があります。信頼できないデータからの数式のように見える値は、別のセキュリティ リスクです。 ToolAcre の CSV エクスポートには、危険な開始の前にプレフィックスを付ける数式注入セーフ オプションがありますが、意図的に計算されたセルは計算されなくなるというトレードオフがあります。

損傷が永続的な場合が多い理由 — 保存時に精度が失われ、別の形式で再シリアル化された日付は出力から元に戻すことができません

20 桁の顧客参照が数値解釈後に丸められ、保存されたとします。欠落している元の数字を新しい CSV から推測することはできません。内部日付に変換され、別のロケールでエクスポートされた日付は、ソースが 3 4 月を意味しているのか、4 3 月を意味しているのかが失われる可能性があります。このため、「後で掃除します」というのは安全な最初のステップではありません。自動入力機能を備えたソフトウェアが生のバイトを置き換える前に、生のバイトを保存します。

代わりに生のテキストを処理します — スプレッドシートに何も触れていないコピーを保持し、インポート前にテキストとしてクリーンアップします。

セルを文字列として扱うパーサーを使用して、テキスト エクスポートのコピーを操作します。 ToolAcre の CSV Cleaner は、ブラウザ内の区切り文字と引用符で囲まれたフィールドを解析し、余分なセルを黙って削除することなく行の警告を表示します。ヘッダーに元の先頭バイトがまだ含まれているかどうか、および識別子に正確なソース文字が含まれているかどうかを確認してください。スペースや重複の削除は、フィールドの意味の解釈とは別のものです。すべての文字が数字である場合でも、郵便番号は文字列のままである必要があります。

実用的な例 — SKU と郵便番号を含む製品のエクスポート。スプレッドシートのラウンドトリップによって何が変更され、テキストレベルのクリーンによって何が保持されるかを示します。

ヘッダー sku;postcode;description と行 00042;01234;「赤、小」および 00043;00105;「青、大」を含むエクスポート例を使用します。単純なカンマインポートではセミコロンフィールドが折りたたまれますが、自動数値入力では 00042 および 01234 が 42 および 1234 に変わる可能性があります。 CSV Cleaner のセミコロン解析では、各説明内の引用符で囲まれたカンマを含む 3 つのテキスト列が生成されます。クリーンなコピーをエクスポートしても、宛先のスプレッドシートに列をテキストとしてインポートする必要があります。購入に送信する前に、生のファイルとラウンドトリップされたファイルを比較します。

これでカバーされないもの — 以前の保存によってすでに破壊された値の復元、またはスプレッドシートのインポート ウィザードの構成

この投稿では、保存されたスプレッドシート ファイルですでに破壊されている識別子の数字や先頭のゼロを回復することはできません。また、テキスト レベルのクリーナーは、Excel のインポート ウィザード、同僚のロケール設定、データベース スキーマを構成しません。未加工ファイルが安全になったら、スプレッドシート ベンダーのコード列と ID 列のテキスト インポート手順に従います。正確な受信者のワークフローを通じて少数のサンプルをテストします。 「列に開く」ということは、「すべての文字列が保持される」ということを証明するものではありません。

スプレッドシートが認識する前にファイルを修正する — ToolAcre CSV Cleaner を使用して、エクスポートをスプレッドシートで開く前に区切り文字、エンコーディング、および引用符を修復できる方法

スプレッドシートがファイルを認識する前にファイルを修正し、識別子をテキストのままにするインポート モードを選択します。 CSV クリーナーは、未変更のエクスポートの区切り文字、エンコード、および引用符をローカルで修復できます。スプレッドシートがユーザーに代わってフィールド タイプを推測することを停止するわけではないため、変更されていないオリジナルとインポート後の比較が最も重要な安全策として残ります。