Ensure

CSV が Excel で正しく開かない理由

ロケールごとのカンマ、セミコロン、タブの使い分けに加えて、クォート、エスケープ、UTF-8 の BOM、そしてファイルが実際にどの区切り文字を使っているかを確実に見分ける方法を解説します。

Excel は .csv ファイルの中身を見て区切り方を判断したりはしません。ダブルクリックして開くと、オペレーティングシステムの地域設定にある リストの区切り文字 を使います。それがファイルと一致しなければ、どの行も 1 つの列に収まってしまいます。

英国と米国では、リストの区切り文字はカンマです。小数点をカンマで書くヨーロッパ大陸の大半とラテンアメリカの多くでは、リストの区切り文字はセミコロンです。そのため、ロンドンから送られてきた正しいカンマ区切りのファイルは、アムステルダム、ベルリン、マドリードでは 1 列に潰れて開きますし、セミコロン区切りのファイルでは逆方向に同じことが起きます。ファイルが壊れているわけではなく、誤った前提のもとで読まれているだけです。

正しく開くための 3 つの方法

  1. 開くのではなくインポートする。 Excel なら「データ」から「データの取得」または「テキストまたは CSV から」を選び、ダイアログで区切り文字とエンコーディングを指定します。完全に制御できるのはこの経路だけであり、別の国に送っても通用するのもこの方法だけです。
  2. ファイル名を .txt に変える。 .txt ファイルに対して Excel は推測を行わず、代わりにインポートウィザードを表示して区切り文字を尋ねてきます。
  3. 送る前に、受け取る側が想定する区切り文字に変換しておく。 相手がセミコロンのロケールだと分かっているなら、セミコロン区切りのファイルを送りましょう。

sep= 行

Excel と LibreOffice はどちらも、ロケールの設定を上書きする特別な 1 行目を理解します。

sep=;
name;email;country
Ada;ada@example.com;UK

これは実際に機能しますし、「取引先がエクスポートしたファイルを開けない」という問題への定番の対処です。ただし、これは表計算ソフトの慣習であって、CSV のいかなる仕様の一部でもないことに注意してください。同じファイルをスクリプトやデータベースのインポートに渡すと、sep=; は普通の 1 行目として読まれ、たいていは意味のない列見出しになります。使いどころは、表計算ソフトを持つ人間に渡すファイルであって、機械どうしのやり取りではありません。

クォートとエスケープ

CSV の非公式な拠り所は RFC 4180 ですが、これは規定するというより一般的な慣行を記述したものです。規則は短いものです。

  • フィールドが区切り文字、ダブルクォート、改行のいずれかを含む場合は、ダブルクォートで囲まなければなりません。
  • クォートで囲まれたフィールド内のダブルクォートは、2 つ重ねて書きます。
id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

1 行目のフィールドは 4 つではなく 3 つです。カンマがクォートの内側にあるからです。重ねられたクォートは、解析時に 1 つに戻ります。2 行目にはクォートで囲まれたフィールドの中に本物の改行が入っています。これは妥当であり、CSV を行数で数えて分割しても当てにならない理由でもあります。

バックスラッシュによるエスケープ (\, や \") は、一部のデータベースツールが使う別の慣習です。RFC 4180 ではなく、厳格なパーサーは理解しません。バックスラッシュを使うファイルなら、読み取る側にそのことを明示してください。

実際に最もよく見る破損は、文字列の連結で書き出されたファイルです。カンマや紛れ込んだクォートを含む値が、そもそもクォートされていないのです。その結果、ヘッダーよりフィールド数の多い行ができます。バリデーターが指摘するのは、まさにこうした箇所です。

BOM と、アクセント付きの文字が文字化けする理由

バイトオーダーマーク (BOM) は、ファイルの先頭に置かれる 3 バイトの不可視のバイト列で、そのファイルが UTF-8 であることを示します。Windows 版の Excel は歴史的にこれに頼ってきました。BOM がないと、従来の地域固有のエンコーディングにフォールバックすることがあり、Müller が Müller に、café が café になって表示されます。

引き換えに、他のパーサーが必ずしもこれを取り除いてくれるとは限りません。スクリプトが最初の列名を id ではなく \ufeffid と報告したり、"id" での参照が最初の列だけ不可解に失敗したりするなら、犯人は BOM です。エンコーディングに utf-8-sig を指定してファイルを読めば解消します。

目安はこうです。Excel で開くためのファイルには BOM を付け、ソフトウェアが解析するためのファイルには付けません。

改行コード

Windows のツールは各行の末尾に \r\n を書き、Unix のツールは \n を書きます。たいていのパーサーはどちらにも対応しますが、\n で素朴に分割すると、各行の最後のフィールドに復帰文字が貼り付いたまま残ります。見た目は UK である値が比較で "UK" と一致しないのは、これが理由です。

ファイルが実際に使っている区切り文字を見分ける

ファイルはプレーンテキストエディターで開いてください。表計算ソフトで開いてはいけません。表計算ソフトはすでに推測を済ませてしまっているからです。その上で次のようにします。

  1. 最初の 2 行か 3 行を見ます。明らかにフィールドの値と分かるものの間に現れているのは、候補となる文字 (カンマ、セミコロン、タブ、パイプ) のどれでしょうか。
  2. 複数の行で各候補の出現回数を数えます。本物の区切り文字は どの行でも同じ回数 現れ、その回数は列数より 1 つ少なくなります。ある行では 7 回、次の行では 2 回現れる文字は、構造ではなくデータです。
  3. この計数はクォートで囲まれた部分の外で行ってください。"Smith, Ada" のカンマは中身であり、これを素朴に数えてしまうことが、本当はカンマ区切りのファイルでカンマの数が不ぞろいに見える原因です。
  4. 小数点のカンマに注意してください。ヨーロッパのファイルでは、セミコロンの区切りと数値中のカンマが同居していることがよくあります。Ada;1.234,56;UK のような形です。カンマが数字と数字の間にしか現れないなら、それは区切り文字ではなく小数点です。
  5. 結論を出す前に、先頭のバイト列に sep= 行や BOM がないか確認してください。

タブについては特筆しておく価値があります。表計算ソフトに渡すものとしては、タブ区切りのファイルが一般に最も安全です。タブが実際の値の中に現れることはほぼありませんし、インポート経由なら Excel もうまく扱えます。難点は、タブが目に見えないことです。誰かが空白で列をそろえたファイルは見た目がそっくりなのに、解析しても使い物になりません。

区切り文字さえ分かれば、ファイルの変換は機械的な作業です。正しい区切り文字とクォートの規則で読み込み、送り先が想定する区切り文字で書き出します。あるいは JSON に変換して、区切り文字の問題そのものを回避してしまうこともできます。