CSVがExcelで1列にまとまる原因と区切り文字の直し方

公開ツールJSONをCSVに変換

目次
  1. 問題: なぜこれが問題なのか
  2. 解決: こうやって確認します
  3. よくつまずくポイント
  4. まとめ
  5. よくある質問

JSONの配列をCSVに変換して相手に送ったのに、いざ相手がExcelで開いてみると、値がすべてA列の1つのセルにカンマまでそのままくっついて表示されることがあります。ファイルが壊れているわけではなく、ExcelがCSVを開く方法に関係する問題です。原因がわかれば、変換の段階で区切り文字を変えるだけでこの問題を避けられます。

要約 ① ExcelはCSVをダブルクリックで開くとき、ファイル内の区切り文字ではなく、OSの「リスト区切り文字」設定に従います。 ② 小数点記号にカンマを使う地域(ドイツ語・フランス語・スペイン語など)では、この設定の初期値がセミコロンです。 ③ 受け取る人の環境がわからない場合は、CSVをセミコロン区切りで作っておけば、ダブルクリックするだけで列が分かれます。

問題: なぜこれが問題なのか

APIのレスポンスやデータベースから取り出したJSONオブジェクトの配列をCSVに変換して、チームメンバーに送ることはよくあります。受け取った人はたいてい、ファイルのアイコンをダブルクリックしてそのままExcelで開きます。

ところが開いてみると、name,email,age のようなヘッダー1行がカンマごとA1セルにまとめて入ってしまい、B列とC列が空になっていることがあります。ファイルをメモ帳で開き直しても、カンマは確かに正しく入っています。

問題はファイルではなく、そのファイルを開いたExcel側の設定です。

解決: こうやって確認します

ExcelはCSVファイルをダブルクリックで開くとき、ファイルの拡張子だけを見て開き、実際の区切り文字が何かをファイルの内容から解析して判断することはありません。その代わり、OSに設定されている「リスト区切り文字」をそのまま使います。

Windowsでは、コントロールパネル → 地域 → 形式 → 追加の設定でこの値を確認できます。この初期値は地域ごとに異なります。

韓国語や英語(米国)のように小数点にピリオドを使う地域では、リスト区切り文字の初期値はカンマ(,)です。一方、ドイツ語・フランス語・スペイン語のように小数点そのものにカンマを使う地域では、数値の「3,14」と列の区切りで同時にカンマを使うと衝突するため、リスト区切り文字の初期値がセミコロン(;)に設定されています。

このような環境でカンマ区切りのCSVをダブルクリックすると、Excelはカンマを区切り文字として認識せず、1行全体を1つの値として扱います。他のOSでも、システムの地域設定によっては同じ原理で動作することがあります。

確認方法は2つあります。

  1. CSVをダブルクリックせず、Excelで新しいブックを開いてから データ → テキスト/CSVから を選ぶと、読み込み画面で区切り文字をカンマに直接指定できます。
  2. ファイルを送る前に、CSV自体を相手の環境に合った区切り文字で作っておけば、ダブルクリックするだけで列がすぐに分かれます。

2番目の方法のほうが、毎回読み込み画面を開くより簡単です。区切り文字を毎回手で選ぶのが面倒だと感じたら、Dynamic Labsの無料ツール「JSONをCSVに変換」で確認してみてください。

JSONをCSVに変換

無料・登録不要・ブラウザだけでJSONをCSVに変換

「JSONをCSVに変換」は、JSONオブジェクトの配列を貼り付けると、各オブジェクトのキーをヘッダーにしてすぐにCSVに変換します。区切り文字はカンマ・タブ・セミコロン・パイプから選べるので、受け取る人の地域がセミコロンを使う環境なら、変換時にセミコロンを選んで出力すれば済みます。

値の中に選択した区切り文字やダブルクォート、改行が含まれている場合は、その値全体を自動でダブルクォートで囲み、列がずれないように処理します。ダウンロードされるファイルにはUTF-8 BOMがすでに含まれています。この記事で扱っている区切り文字の問題と、韓国語が文字化けするエンコーディングの問題は、それぞれ別の原因です。

よくつまずくポイント

症状原因確認ポイント
値がすべてA列の1セルにまとまるリスト区切り文字とCSVの区切り文字が異なる区切り文字をセミコロンに変えて再出力する
値の中のカンマで列が分割される値の中のカンマが区切り文字と重なる値に区切り文字があると自動でダブルクォートに囲まれる
タブで出力したのに列が分かれないコピー&ペーストの過程でタブが空白に変わるテキストをコピーせず、CSVファイルをダウンロードして渡す
韓国語が文字化けして見えるリスト区切り文字とは無関係のエンコーディング問題ダウンロードボタンから取得したファイルか確認する(BOMを含む)

まとめ

CSVの値がA列の1セルにまとまって表示されるのは、ファイルが壊れているからではなく、ダブルクリックで開いたときにExcelがファイル内の区切り文字ではなくOSのリスト区切り文字設定に従うためです。

受け取る人の地域設定がわからない場合は、読み込み画面で区切り文字を直接指定するか、CSVを作る時点でセミコロンのようなより安全な区切り文字を選んでおけば、この問題を避けられます。

JSONをCSVに変換

無料・登録不要・ブラウザだけでJSONをCSVに変換

よくある質問

区切り文字をセミコロンに変えると、他のプログラムでも問題なく開けますか?

表計算ソフトやデータベースの取り込みツールは、たいてい区切り文字を直接指定するオプションがあるため、セミコロン区切りで作っても問題なく読み込めます。ただし、区切り文字が固定値(カンマ)であることを前提にした単純なスクリプトの場合は、受け取る側がどのようにファイルを読むのかを先に確認したほうが安全です。

値の中にセミコロンが入っている場合はどうなりますか?

区切り文字にセミコロンを選んでいる状態で、値そのものにセミコロンが含まれている場合は、その値全体が自動的にダブルクォートで囲まれます。反対に区切り文字をカンマにしておけば、セミコロンは単なる文字として扱われ、値の中のカンマだけが囲まれます。

JSONがオブジェクトの配列ではなく、文字列や数値だけの配列だった場合はどうなりますか?

["a", "b", "c"] のように、オブジェクトではない値の配列を入れると、ヘッダーなしで「value」という名前の列が1つだけ作られ、それぞれの値が1行ずつその列に入ります。

この記事は韓国語の原文をもとに作成しています。 韓国語の原文を読む

関連記事

  1. CSVのカンマで列がずれる原因と引用符のルールCSVをカンマで分割すると列がずれるのはなぜか、RFC 4180の引用符ルールで解説します。値に含まれるカンマ・二重引用符・改行の扱いと、自分でパースして確認する手順まで。
  2. SQL INSERTのシングルクォートエスケープが必要な理由CSVの値にシングルクォート(アポストロフィ)が入っていると、なぜSQL INSERT文が壊れるのか。二重に書いてエスケープする仕組みを例で解説します。
  3. CSV連絡先をiPhoneに一括登録するvCard変換手順Excelで整理した連絡先がiPhoneで開かないのは、連絡先アプリがCSVに対応しておらずvCard(.vcf)しか読み込めないためです。CSVをvCardに変換して1つのファイルにまとめる方法を解説します。
  4. アイゼンハワーマトリクス4象限|緊急と重要の分け方アイゼンハワーマトリクスの1〜4象限がそれぞれ何か、緊急と重要をどんな質問で分けるかを整理。緊急だが重要でない第3象限に時間が偏る理由も説明します。
  5. Excel CSVの文字化けはUTF-8 BOM 3バイトが原因ExcelでCSVを開くと文字化けする原因をバイト単位で解説。UTF-8 BOMの3バイトが何をしているのか、ファイルを加工せずに開く方法までまとめました。
  6. Excel CSV 文字化けの原因とUTF-8変換方法ExcelでCSV保存したファイルを他のプログラムで開くと文字が「?」や記号に化ける原因(文字コードの違い)を説明し、UTF-8で文字化けしないCSVに変換してダウンロードする方法を紹介します。

関連ツール