データとスプレッドシート · CSV クリーナー
Excel が CSV フィールドを静かに破壊する理由: 先頭のゼロ、日付、長い ID
· なぜそれが重要なのか
csv データクリーニング スプレッドシート
スプレッドシートで CSV を開くことは中立的ではありません。郵便番号はゼロを失い、コードは日付になり、長い識別子は科学表記になります。この投稿では、この問題が発生する理由、回復できないもの、および RAW ファイルを無傷で保存する方法について説明します。
誰かが開くまでは正常だったファイル - 郵便番号 01234 が 1234 になり、ダメージは保存されます
ファイルは郵便番号 01234 でソース システムから出て、スプレッドシートで開かれ、1234 でアナリストに返されます。元の CSV は不正な形式ではなく、先頭にゼロが付いているテキストが含まれていました。この違いは、プログラムがその列が数値であると推測し、その解釈された値を保存したときに発生しました。誰かがソース ファイルを上書きすると、後のクリーニング ツールは 1234 が 01234 を意味していたか、001234 を意味していたかを知ることができません。スプレッドシートで検査する前に、エクスポートをそのままにしておいてください。
開いたときに入力を推測 — スプレッドシートがテキストから数値、日付、数式をどのように推測するのか、そして CSV ではそれ以外のことを判断できない理由
CSV は行、区切り文字、引用符で囲まれたテキストを表現します。列ごとの番号、日付、または郵便番号タイプは宣言しません。スプレッドシートを直接開くと、推測する必要があります。一部のアプリケーションでは、数字で始まる値は数値になり、03 ～ 04 はロケールに従って日付として解釈され、= で始まる値は数式として解釈されます。 「01234」を引用符で囲むと、CSV 区切り文字が保護され、自動インポート ウィザードによる将来のテキストとしての分類は保護されません。
古典的な犠牲者 — 先頭のゼロ、長い数値識別子、日付に似たもの、等号で始まる値
郵便番号や SKU の先頭のゼロ、スプレッドシートの数値形式で保存できる有効桁数を超える識別子、日付のように見える曖昧な文字列は、典型的な被害者です。科学表記法自体は破損ではなく、表示上の選択です。しかし、数値変換後に長い ID を保存すると、正確な桁が失われる可能性があります。信頼できないデータからの数式のように見える値は、別のセキュリティ リスクです。 ToolAcre の CSV エクスポートには、リスクのある開始の前にプレフィックスを付ける数式注入セーフ オプションがありますが、意図的に計算されたセルが計算されなくなるというトレードオフがあります。
損傷が永続的な場合が多い理由 — 保存時に精度が失われ、別の形式で再シリアル化された日付は出力から元に戻すことができない
20 桁の顧客参照が数値解釈後に四捨五入されて保存されているとします。欠落している元の数字を新しい CSV から推測することはできません。内部日付に変換され、別のロケールにエクスポートされた日付は、ソースが 4 月 3 日を意味していたか、3 月 4 日を意味していたかが失われる可能性があります。これが、「後でクリーンアップします」が安全な最初のステップではない理由です。自動入力機能を備えたソフトウェアが生のバイトを置き換える前に、生のバイトを保存します。
代わりに生のテキストを処理します。スプレッドシートに何も触れていないコピーを保持し、インポート前にテキストとしてクリーンアップします。
セルを文字列として扱うパーサーを使用して、テキスト エクスポートのコピーを操作します。 ToolAcre の CSV Cleaner は、ブラウザーで区切り文字と引用符で囲まれたフィールドを解析し、余分なセルを黙って削除することなく行の警告を表示します。ヘッダーに元の先頭バイトがまだ含まれているかどうか、および識別子に正確なソース文字が含まれているかどうかを確認してください。スペースや重複の削除は、フィールドの意味の解釈とは別のものです。すべての文字が数字である場合でも、郵便番号は文字列のままである必要があります。
実用的な例 — SKU と郵便番号を含む製品のエクスポート。スプレッドシートのラウンドトリップで何が変更され、テキストレベルのクリーンで何が保持されるかを示します。
ヘッダー sku;postcode;description と行 00042;01234;"Red, small" および 00043;00105;"Blue,large" を含むエクスポート例を使用します。単純なカンマのインポートではセミコロン フィールドが折りたたまれますが、自動数値入力では 00042 と 01234 が 42 と 1234 に変わる可能性があります。CSV Cleaner のセミコロン解析では、各説明内の引用符で囲まれたカンマを含む 3 つのテキスト列が生成されます。クリーンなコピーをエクスポートしても、宛先のスプレッドシートに列をテキストとしてインポートする必要があります。購入に送信する前に、生のファイルとラウンドトリップされたファイルを比較します。
これでカバーされないもの — 以前の保存によってすでに破壊された値の復元、またはスプレッドシートのインポート ウィザードの構成
この投稿では、保存されたスプレッドシート ファイルで既に破壊されている識別子の数字や先頭のゼロを回復することはできません。また、テキスト レベルのクリーナーは、Excel のインポート ウィザード、同僚のロケール設定、データベース スキーマを構成しません。未加工ファイルが安全になったら、スプレッドシート ベンダーのコード列と ID 列のテキスト インポート手順に従います。正確な受信者のワークフローを通じて少数のサンプルをテストします。 「列に開く」ということは、「すべての文字列が保持される」ということを証明するものではありません。
スプレッドシートがファイルを認識する前にファイルを修正する — ToolAcre CSV Cleaner を使用して、エクスポートをスプレッドシートで開く前に区切り文字、エンコード、および引用符を修復する方法
スプレッドシートがファイルを認識する前にファイルを修正し、識別子をテキストのままにするインポート モードを選択します。 CSV Cleaner は、未変更のエクスポートの区切り文字、エンコード、および引用符をローカルで修復できます。スプレッドシートがユーザーに代わってフィールド タイプを推測することを停止するわけではないため、変更されていないオリジナルとインポート後の比較が最も重要な安全策として残ります。