ConvertBox Logo
ConvertBox

ExcelでCSVが崩れる理由:文字コード、区切り文字、消えるゼロ

CSVファイルには文字コードも区切り文字も列の型も書かれていないため、ダブルクリックで開くとExcelがその三つをすべて推測します。[データ]タブから取り込むか、全セルを文字列として保存した.xlsxに変換すれば、推測は入りません。

ConvertBox公開

CSVにはメタデータがなく、Excelは推測する

CSVはプレーンテキストで、区切り文字で分けられた値が行ごとに並んでいるだけです。どの文字コードを使ったのか、区切り文字がカンマなのかセミコロンなのか、00123が数値なのかコードなのかは、ファイルのどこにも書かれていません。RFC 4180が一般的な形を説明していますが、拘束力のある標準ではなく、実際の出力はまちまちです。.csvをダブルクリックすると、ExcelはWindowsの地域設定と各値の見た目をもとに、確認を求めることなくこれらの疑問を推測で解決します。CSVに関する不満のほとんどは、この三つの推測のどれかに行き着きます。

文字化け:文字コードとバイトオーダーマーク(BOM)

現在、多くのシステムはCSVをUTF-8で出力します。ところがWindows版のExcelは、ダブルクリックで開いたファイルがバイトオーダーマーク(BOM)であるEF BB BFの3バイトで始まっていないと、地域ごとの従来のコードページ、日本語のWindowsではShift_JIS(CP932)だとみなします。BOMがないと、ASCII以外の文字はすべて誤って読まれます。UTF-8ではかなや漢字は1文字3バイトですが、Shift_JISとして読むと別の区切りで解釈されるため、「東京」は「譚ア莠ャ」と表示されます。

逆方向の失敗もあります。古いExcelが地域のコードページで保存したCSVは有効なUTF-8ではないため、UTF-8を前提とするツールでは置換文字が表示されます。当サイトのCSVビューアーとCSV to Excel変換ツールはファイルをUTF-8としてのみ読み込むので、従来形式のファイルは先に「CSV UTF-8」形式で保存し直してください。両ツールがダウンロードさせるCSVはBOMなしのUTF-8であり、Windows版Excelでダブルクリックすると同じ文字化けが起きます。一方、.xlsxの出力では、ブック形式が自身の文字コードを宣言しているため、この問題は起きません。

すべてがA列に入る:区切り文字とリスト区切り記号

Excelはカンマを前提にしていません。Windowsの地域設定にあるリスト区切り記号で分割し、ドイツ、フランス、スペインのようにカンマを小数点として使う地域では、この記号がセミコロンになります。そうしたPCでカンマ区切りのファイルを開くと全体がA列に入り、日本や米国の設定のPCでセミコロン区切りのファイルを開いても同じことが起きます。小数点の記号も同じ設定に従うため、1,5はあるコンピューターでは1.5であり、別のコンピューターでは二つのフィールドです。

Excelだけに通用する回避策として、1行目にsep=;またはsep=,と書く方法があります。Excelはこの行で区切り文字を判断しますが、ほかのプログラムはデータとして扱うので、スクリプトが読むファイルには入れないでください。当サイトのCSVビューアーは先頭の数行を調べてカンマ、タブ、セミコロン、パイプを自動で判別し、ダウンロードは常にカンマ区切りです。CSV to JSON変換ツールでは、同じ四つの中から自分で選びます。

Excelが書き換える値:先頭のゼロ、長い数値、日付

既定の「標準」書式では、Excelは数値や日付に見えるものをすべて変換します。セルには変換後の値が入り、CSVとして保存し直すとその値が書き込まれるため、元のテキストは取り戻せません。

当サイトのCSVビューアーは型変換を行わず、各セルのテキストをそのまま表示するので、ファイルに実際に何が入っているかを手早く確認できます。CSV to Excelはすべてのセルを文字列として書き込むため、00123や16桁の数値が保持されます。その代わり、数値の列は「数値に変換する」を実行しないとSUMの対象になりません。CSV to JSONは既定で逆の動作をします。自動の型変換は、引用符で囲んだ値であっても007を7にするので、コードや長いIDがある場合はオフにしてください。

ファイルの内容Excelでダブルクリックした後理由
00123123数値として読まれ、先頭のゼロは値に影響しない
12345678901234561.23457E+15と表示され、1234567890123450として保存数値の有効桁数は15桁まで
1E51.00E+05100000の指数表記として解釈
1-21月2日のような日付今年の月と日に見える
MAR1日付(3月1日)月の略称の後に数字が続く形
03/04/20243月4日または4月3日日と月の順序は地域設定に従う

フィールド内の引用符、カンマ、改行

区切り文字、二重引用符、改行を含むフィールドは二重引用符で囲み、その中の引用符は二つ重ねて書きます。7,"Smith, Ann","She said ""hi"""というレコードのフィールドは三つで、7、次にSmith, Ann、次にShe said "hi"です。引用符で囲まずに値をカンマでつないだだけのファイルは、住所やコメントに初めてカンマが現れた時点で列がずれます。

引用符の中の改行は規則どおりの書き方であり、ExcelでAlt+Enterによる改行を含むセルがあると、そのように保存されます。このときレコードは物理的に2行にまたがるため、行数を数える、スクリプトで改行ごとに分割するといった行単位の処理では、壊れた行に見えます。当サイトのCSVビューアーは、そのようなフィールドを一つのセルに保ちます。CSV to JSON変換ツールは違います。引用符を調べる前にテキストを行に分割するため、複数行のセルはずれた行として出力されます。

ダブルクリックせずに安全に取り込む

推測を避ける方法は二つあります。一つは、CSVを全セルが文字列の.xlsxに変換することです。もう一つはExcel自身の取り込みダイアログで、ダブルクリックが黙って答えてしまう三つの質問を尋ねてくれます。現行のWindows版Excelでの手順は次のとおりです。

  • 空白のブックを開き、ファイルを直接開く代わりに[データ]>[テキストまたはCSVから]を選びます。
  • [元のファイル]を65001: Unicode (UTF-8)に設定し、プレビューで日本語が正しく表示されることを確認します。
  • ファイルが実際に使っている区切り記号を選びます。
  • [データ型検出]を[データ型を検出しない]にするか、[データの変換]を選んでコード、ID、電話番号の列をテキスト型に指定してから読み込みます。
  • 元のCSVには手を加えず、作業結果は.xlsxで保存します。CSVに保存し直すと、Excelがその時点で各セルに保持している値が書き込まれます。

他人のソフトウェアでも崩れないCSVを作る

ファイルを作る側であれば、いくつかの選択で後工程の問題の大半を防げます。まずデータを整えます。値の前後の余分な空白、完全に空の行、完全に重複した行は、取り込んだ後より前に取り除くほうが簡単です。当サイトのデータクリーナーはこの三つを決まった順序で処理し、重複を削除するときは行全体を比較します。

  • UTF-8でエンコードします。BOMを付けるのは、受け取る人がExcelでダブルクリックして開く場合だけにします。ほかのパーサーには、BOMを最初の列名の先頭にある見えない文字として読むものがあります。
  • 日付はISO 8601(2024-03-09)で書きます。どの地域設定でも順序を取り違えて読まれることがありません。
  • 数値は桁区切りを付けず、小数点にはピリオドを使い、その方針を文書に記します。
  • 区切り文字、引用符、改行を含む可能性のあるフィールドはすべて引用符で囲み、すべての行のフィールド数をそろえます。
  • 受け取るのがプログラムではなくExcelの利用者なら、.xlsxで送ります。型と文字コードがファイルに明示的に保存されます。

このガイドで扱ったツール

関連ガイド

ガイド一覧