CSVとExcel — 実際に何が違うのか

CSVは文字です。形式はそれで全部です。

CSVはカンマで区切られた文字の行です。それ以外には何もありません — セルの型も、数式も、書式も、複数シートも、列幅もありません。CSVの中の`007`は三文字であって、数値でも文字列でもありません。ファイルはどちらかを言わないのです。

.xlsxは正反対です。セルごとにどんな値でどう見えるべきかを記録した圧縮された束です。だから大きく、だから開いて保存しても変わらずに残ります。

ExcelがCSVを開くときデータにすること

ファイルが型を教えないのでExcelが推測します。平均的には妥当で、特定の場合には破壊的です。

• 先頭のゼロが消えます。`007`が`7`になります。郵便番号・口座番号・品番がいつも犠牲になります。
• 日付らしきものが日付になります。`1-2`が1月2日になります。ExcelがSEPT2という遺伝子名を日付に変え続けたせいで、遺伝学者が命名規則を変えた有名な話があります。
• 長い数値が指数表記になり、末尾を失います。16桁の識別番号が`1.23457E+15`で返り、あとから書式を変えても失った桁は戻りません。
• そして保存すると破損が保存されます。元のファイルは正しく、Excelが書き戻したファイルが間違っているのです。

文字化けする、あるいは全部が一列に入る理由

別々の二つの問題ですが、どちらも「ファイルが宣言できない前提」に関するものです。

エンコーディング。 CSVは自分の文字エンコーディングを記録しません。Windows版Excelは長らくUTF-8ではなく地域のコードページを仮定してきました。だから日本語やアクセント付きの文字が化けて入ります。ファイルの先頭にUTF-8のBOMを付ければたいてい直ります。

区切り記号。 カンマを小数点に使う国では、表計算ソフトがカンマの代わりにセミコロンを書き、またそれを期待します。そういうファイルを別の場所で開くと、すべての行が一列に入ります。ファイルが壊れているのではなく、二つのプログラムがカンマの意味について食い違っているのです。

ファイルが単に大きすぎるとき

表計算ソフトには約1,048,576行という固い天井があり、そこに届くずっと手前から遅くなります。すべて編集できるようにするため丸ごとメモリに載せるからです。

ある大きさを超えたら、ファイルを開くのをやめてファイルに問うのが正解です。必要な列をいくつか選び、絞り、まとめ、数えれば数秒で答えが出ます — 見るつもりもなかった何百万ものセルを機械が描こうと苦労する必要はありません。

Parquetについて短く

CSVに何度も痛い目を見ているなら、分析の世界が移った先がParquetです。型がファイルの中にあるので推測するものがありません。行ではなく列単位で保存するので、五十列のうち三列だけ読むならおよそ三列分のバイトだけを読みます。圧縮も効き、同じCSVの5〜10分の1になることがよくあります。

代償はテキストエディタで開けないことです。目で見る形式ではなく、問う形式です。