記事
なぜExcelはCSVファイルを壊してしまうのか
先頭に3つのゼロが付いた患者ID。SEPT2という名前の遺伝子。16桁の口座番号。これらをプレーンなCSVファイルとして表計算ソフトで開くと、Excelのインポート機能は単に表示するだけでなく書き換えてしまいます。ゼロは消え、遺伝子名は日付になり、口座番号は末尾の数桁を失います。これは厳密にはバグではなく、汎用の数値・日付推測機能が、数字や日付に見えるだけのテキストと出会ったときに起きることです。ExcelがCSVファイルを開いた瞬間に実際に何が起きるのか、そしてそれが起きる前に止める方法を見ていきます。
先頭のゼロが消える
セルに02134と入力すると、Excelの既定の列型「標準」はそれを数値として読み取り、先頭のゼロを落として2134にします。これは数字として保存されているが実は数量ではないあらゆるコード、米国のZIPコード、フランスの郵便番号、社員番号や請求書番号、電話番号に影響します。ワークブックを保存するまでは、技術的には元のCSVファイルから何も失われていません。セルの隣のテキストは変わらないままです。しかし保存した瞬間、Excelは表示していたものをそのまま書き戻すため、ゼロは永久に失われます。修正方法は、Excelが推測する前に列がテキストであることを伝えることです。データ > テキスト/CSVから（または データの取得 > ファイルから）、各列のデータ型を明示的に設定できます。あるいは、単一セルの値の前にアポストロフィを付けてテキストを強制することもできます。
確認もせずにテキストが日付に変わる
同じ推測機能は日付らしい形のテキストも探します。MAR1やSEPT2というセルは、3月1日や9月2日として読み取られ、警告もなく日付の値に変換され再フォーマットされます。これは仮定の話ではありません。2020年、ヒト遺伝子命名委員会（HGNC）はSEPT2やMARCH1を含む約27のヒト遺伝子記号を、まさに表計算ソフトがデータセットを開くたびにそれらを日付に変えてしまうという理由でリネームしました。2016年のゲノミクス論文の調査では、Excelの補助ファイルを含む論文のおよそ5本に1本にこうしたオートコレクトの誤りが見つかったほど深刻な問題です。この教訓は遺伝子名を超えて一般化できます。誰かがあなたのCSVを表計算ソフトで開いた瞬間、日付のように見えるだけの短い英数字コードは、自分のものに限らずリスクにさらされます。
非常に長い数字が末尾の桁を失う
Excelはすべての数値を15桁の有効数字精度を持つ浮動小数点値として保存します。これはマイクロソフトが直接文書化している制限です。4111111111111111のような16桁のカードや口座番号は、収まるように丸められるため、末尾の1、2桁が静かにゼロに変わり、値は入力したものとは違うものになります。あとからセルの書式を変えても失われた桁は戻りません。すでに保存されている数値自体が丸められてしまっているためです。唯一の修正方法は、Excelが数値として扱う前に列をテキストとしてインポートするか、値の前にアポストロフィを付けることです。
アクセント付き文字が文字化けする
エンコーディングは自動修正とは別の失敗モードです。バイトオーダーマークのないCSVファイルは単なるバイト列です。UTF-8として保存されていた場合、Windows版のExcelは通常システム自体のコードページを想定して開いてしまうため、アクセント付き文字や通貨記号、絵文字は文字化けとして表示され、caféのような文字列がcaféに近いものとして表示されます。Excel用にCSVを作成する際の安全な方法は、CSV UTF-8として保存するか、UTF-8のバイトオーダーマークを追加することです。読み込む側の安全な方法は、ダブルクリックで開いて推測に任せるのではなく、データ > テキスト/CSVからインポートし、ソースのエンコーディングを明示的に選ぶことです。
Excelで開かずに形式間でデータを移動する
これは厳密にはExcelのバグではなく、表計算ソフトの数値・日付推測機能が作られた通りに動いているだけであり、Excelに限らずファイルの開き方に関係なく当てはまります。CSVファイルを整形したり、表計算ソフトに届く前に列が本当にテキストのままであることを確認したりする最も安全な方法は、直接処理することです。当サイトのデータ変換ツールは、標準的な引用符対応のパーサーでCSVを解析し、値の型を再解釈することは決してありません。そのためZIPコードや遺伝子名は入力したとおりのテキストのまま保たれます。すべてブラウザ内で完結し、貼り付けたものがどこかにアップロードされることはありません。ただしこれは変換ステップだけを守るものです。出力があとで表計算ソフトに開かれる予定であれば、そちらでも機密の列をテキストとしてマークしてください。
この記事で紹介するツール
よくある質問
ExcelがCSVから先頭のゼロを削除するのを止めるにはどうすればいいですか？
ファイルをダブルクリックで開かないでください。代わりにデータ > テキスト/CSVから（または データの取得から）インポートすると、列ごとのデータ型選択画面が表示されます。インポートを完了する前に、ZIPコード、ID、電話番号の列をテキストに設定してください。セル単位の簡単な修正方法は、'02134のように値の前にアポストロフィを入力することです。表示は変えずにテキストを強制できます。
なぜExcelは私のIDやコードを日付に変えてしまったのですか？
Excelの「標準」列型は、短い単語と数字の組み合わせや、ダッシュやスラッシュで区切られた2つの数字といった、日付らしい形のテキストを積極的に探し、確認なしに一致するものすべてを変換します。これはまさに2020年にSEPT2やMARCH1といった遺伝子記号のリネームを引き起こした原因です。先頭のゼロの場合と同じように、列をテキストとしてインポートすれば、この推測が起こる前に止められます。
CSVを別の方法で変換すれば、これらの問題を完全に避けられますか？
その変換ステップに関しては避けられますが、永久にではありません。当サイトのデータ変換ツールのように、すべてのフィールドをプレーンテキストとして扱うパーサーは、CSVをJSONに変換する際にZIPコードや遺伝子名を書き換えることはありません。しかし出力があとで表計算ソフトで開かれる場合、あなた自身であれ受け取った相手であれ、そのプログラム自身のインポート機能が値を再解釈する機会を再び得ることになるため、機密の列はそこでもテキストとしてマークしておく必要があります。