ExcelでCSVが壊れる理由
郵便番号の先頭の0が消え、商品コードが日付になり、長いIDがいつの間にか丸められる。これは保存時ではなく開いた時点で起きるため、多くの人が原因をつかめずにいます。
最終確認日：
ダメージは開いた瞬間に起きる
この問題の原因がつかみにくいのは、まさにここです。CSVはテキストファイルで、中の値はすべて文字にすぎず、それぞれが何を意味するかはファイルのどこにも書かれていません。Excelは開くときに列ごとにデータ型を推測し、値をそれに合わせて変換し、その瞬間から変換後のデータを保持します。そのまま保存すると、変換結果がファイルに書き戻されます。
つまり、元のファイルは問題なかったのに、保存したファイルは壊れていて、その間に一度も警告は出ません。多くの人はエクスポートが壊れていたと考えて出力し直し、同じ結果になります。破損はダウンロードの後、自分のパソコン上で起きているからです。
ダメージを引き起こす4つの変換
先頭の0が削除される。 01234のような郵便番号、フランスの県番号、口座番号、0から始まる電話番号は、どれも数値に見えるため、0は意味のないものとして捨てられます。しかし意味がないわけではなく、識別子の一部です。
長い数値が丸められる。 スプレッドシートは数値を有効桁数約15桁の浮動小数点数として保持するため、注文番号、クレジットカード番号、IMEI、64ビットのIDは黙って丸められ、末尾の桁が0になります。セルは数値に見えますが、間違った数値です。
日付に見えるものはすべて書式が変わる。 しかも地域の慣習に従って。03/05はある国では3月、別の国では5月を意味するため、同じファイルを2つのオフィスで開くと2つの異なるデータになります。さらに悪いことに、日付ではなかった値まで変換されます。SEPT1のような遺伝子名が日付になってしまう問題はあまりにも根深く、遺伝学者たちは戦い続けるのをあきらめて、最終的に遺伝子の名前のほうを変えました。
アクセント付きの文字が文字化けする。 CSVには文字コードの記録がないため、UTF-8で保存されたファイルを古いコードページとして読み込むと（あるいはその逆でも）、アクセント付きの名前を含む行だけがきっちり崩れます。
解決策：開くのではなく、インポートする
大事なCSVは決してダブルクリックで開かないでください。 ダブルクリックはExcelに推測する許可を与えることになり、Excelは自信満々に推測します。
代わりに「データ」→「テキストまたはCSVから」を使いましょう。インポート画面では、読み込み前に各列のデータ型を指定できます。郵便番号の列やIDの列を「文字列」に指定すれば、ファイルにあるとおりのまま読み込まれます。区切り文字や文字コードも、推測に任せず自分で指定できます。20秒で済み、これだけで問題はすべて解決します。
Googleスプレッドシートでも同じです。「ファイル」→「インポート」を選び、「テキストを数値、日付、数式に変換する」をオフにしてください。LibreOffice Calcはデフォルトで列の型を指定する画面を表示します。これは、LibreOfficeのほうが素直に行儀よく振る舞う数少ない場面の1つです。
インポートの前に中身を確認する
すでに問題が起きてしまったときは、まずスプレッドシートに触れさせずにファイルを見るのが有効です。CSVを表示すれば、バイト列が示すとおりの値がそのまま表示されます。先頭の0も残り、長い数値も完全なまま、日付も書かれたとおりのテキストのままです。これで、エクスポートとインポートのどちらが間違っていたのかがすぐに分かります。
CSVが自分では決して示さない2つの情報も確認できます。1つは区切り文字で、カンマ、セミコロン、タブのいずれかです（ヨーロッパの地域設定ではカンマが小数点なので、セミコロンで出力されます）。もう1つは文字コードです。インポート前にこの2つが分かっていれば、残りの推測作業はほとんどなくなります。
CSVを作る側の場合
エクスポートする側でいくつか判断しておけば、後工程でのこうした問題をすべて防げます。
Windows版Excelで開かれるファイルなら、BOM付きUTF-8で書き出してください。 ExcelはBOMを手がかりにUTF-8を判別し、BOMがないとアクセント付きの文字を文字化けさせます。BOMを付けることが迷惑ではなく正解になる、数少ないケースです。
誤解されそうなフィールドはすべて引用符で囲み、ID列は常に囲んでください。 引用符で囲んでも、開いたときのExcelの変換は止められませんが、ほかのすべての利用者に対して意図が明確になります。
そもそもCSVを使わないことも検討しましょう。 受け取る側がJSONや本物のスプレッドシートを扱えるなら、どちらもデータ型を明示的に持っていて、こうした失敗は一切起きません。CSVの長所はあらゆるものが読めること、短所はその中身の解釈について何ひとつ意見が一致しないことです。
よくある質問
保存した後でダメージを元に戻せますか？
確実には戻せません。削除された先頭の0や丸められた桁は失われており、ファイルには復元の手がかりが残っていません。元のデータから出力し直してください。だからこそ、元のエクスポートファイルに手を付けずに残しておく価値があるのです。
CSVが1つの長い列として開かれるのはなぜですか？
区切り文字がスプレッドシートの想定と合っていないからです。多いのは、セミコロン区切りのファイルをカンマ区切りの地域設定で開いた場合、またはその逆です。インポート画面で区切り文字を指定できるので、システムの地域設定を変えるより手早く解決できます。
最初の列名の先頭にある妙な文字は何ですか？
BOM（バイトオーダーマーク）が、文字コードの目印ではなくテキストとして読まれたものです。Excelでアクセント付きの文字を正しく表示させるのと同じ印なので、便利でもあり厄介でもあります。まともなCSVパーサーの多くは自動的に取り除きます。
CSVに標準規格はありますか？
2005年に、多くのツールの動作をまとめた参考仕様があるだけで、それとは違う動作をするソフトもたくさんあります。だからこそ区切り文字、引用符、改行コード、文字コードがまちまちで、決め打ちせずに判別することが、実際のファイルで通用する唯一の方法なのです。
テキストエディターで1行が複数行に分かれてしまうのはなぜですか？
引用符で囲まれたフィールドには、改行を含めることが正式に認められているからです。典型的なのは住所欄です。レコードとしては1行のままなので、改行でファイルを分割する処理はまさにそうした行を壊してしまいます。カンマや改行で分割するのがCSVの正しい読み方ではない理由はここにあります。