猫の手道具箱ロゴ

猫の手道具箱

このサイトは社内情シスやPGに役立ちそうなツールを公開しています。

English

100万行を超えるCSVをExcelで開く方法|分割と文字化けの対処

公開: / 執筆: 猫の手工房

この記事の要点

  • Excel の1シートは、1,048,576行 × 16,384列までです。超えた行は、警告が出たうえで読み込まれません。
  • 大きな CSV は、行数を指定して分割し、ヘッダーを各ファイルに付けると、Excel で開けます。
  • 実測: 150万行(約71MB)の CSV は、1,000,000行ずつの2ファイルに、1秒かからず分割できました。
  • 文字化けは、ほとんどが文字コード(UTF-8 と Shift_JIS)の食い違いです。直し方は、3章に書きました。
  • セルの中に改行がある CSV は、行数で分けると途中で切れます。事前の確認方法は、4章に書きました。

1. Excel で開けないのは、なぜか

Excel の1つのシートには、入る大きさの上限があります。行は1,048,576行(2の20乗)、列は16,384列(XFD 列)までです。CSV の行数がこれを超えると、Excel は上限までを読み込んで、残りは読み込みません。

気をつけたいのは、警告を閉じて、そのまま保存してしまうことです。上限を超えた分は、画面にも保存したファイルにも入っていません。元の CSV に上書き保存すると、超えた行が消えます。大きな CSV は、元のファイルをコピーしてから扱ってください。

売上データ、アクセスログ、システムからの出力などは、1年分をまとめると100万行を超えることがあります。Excel で開く前に、行数を確かめる習慣をつけると安全です。

2. 行数で分割して開く手順

ここでは、架空の売上データ(項目は「注文ID・日付・店舗・商品・金額」の5列、150万行、約71MB)を使って、実際に分割しました。

  1. CSV分割ツールを開き、CSV ファイルをドラッグ&ドロップします。ファイル名、サイズ、総行数が表示されます。この例では、「総行数: 1500001行」と表示されました。ヘッダーの1行を含む数です。
  2. 「ヘッダー行を各ファイルに含める」にチェックを入れます。各ファイルの先頭に、項目名の行が付きます。
  3. 分割方法は、「1ファイルあたりの行数を指定」を選び、1000000 と入力します。
  4. 「CSV分割実行」を押します。「ファイル1をDL」「ファイル2をDL」というボタンが出るので、順に押して保存します(split_1.csv、split_2.csv)。
  5. Excel で、分割したファイルを1つずつ開きます。

分割した結果(実測)

ファイルデータの行数サイズ先頭のデータ(注文ID)
元の sales.csv1,500,000行(+ヘッダー1行)約71.0MB1
split_1.csv1,000,000行(+ヘッダー1行)約47.0MB1
split_2.csv500,000行(+ヘッダー1行)約24.1MB1000001

2つのファイルの行数を足すと、元のデータの1,500,000行と一致します。split_1.csv の最後の注文IDは1000000で、split_2.csv は1000001から始まるので、順番も保たれています。所要時間は、ファイルの読み込みが約0.6秒、分割が約0.3秒でした(このサイトの検証用パソコンのブラウザで測定。お使いの端末によって変わります)。

1ファイルの行数は、いくつにするか

ヘッダーを含めても Excel に収まる上限は、データ1,048,575行です。ただし、上限いっぱいにする必要はありません。上の例の split_1.csv は約47MB あり、100万行のシートは、並べ替えや数式で動作が重くなりやすいからです。作業するパソコンの性能に合わせて、50万行〜100万行のあいだで決めるのがおすすめです(これは、筆者の目安です。環境ごとの測定結果ではありません)。

ファイルの数が多くなるときは: このツールは、分割したファイルを1つずつ保存します。まとめて保存する機能はありません。数十個になる設定では、手間がかかります。行数を増やして、ファイルの数を減らす方法を考えてください。

3. 文字化けを直す

CSV を Excel で開いたときの文字化けは、ほとんどが文字コードの食い違いです。日本語の CSV には、主に2種類があります。

種類よくある出どころExcel でダブルクリックして開くと
Shift_JIS(CP932)日本語版 Windows の古い業務システム、Excel の「CSV(コンマ区切り)」での保存正しく開く
UTF-8(BOM なし)Web システム、プログラム、このサイトのツールの出力文字化けすることがある
UTF-8(BOM 付き)Excel の「CSV UTF-8」での保存正しく開く

UTF-8 の CSV を、Excel で正しく開く

CSV分割ツールの出力は、BOM なしの UTF-8 です。分割したファイルを Excel のアイコンでダブルクリックして開くと、日本語が化けることがあります。ファイルを直接開かずに、次の手順で読み込みます。

  1. Excel で、新しいブックを開きます。
  2. 「データ」タブの「テキストまたは CSV から」を選び、分割したファイルを指定します。
  3. 読み込みの画面で、「元のファイル」(文字コード)を「65001: Unicode (UTF-8)」にします。プレビューで、日本語が読めることを確認します。
  4. 「読み込み」を押します。

元の CSV が Shift_JIS のとき

CSV分割ツールは、ファイルを UTF-8 として読みます。Shift_JIS の CSV をそのまま入れると、日本語の部分が化けた状態で分割され、出力にも化けた文字が残ります。分割する前に、UTF-8 に変換してください。

Linux や Mac では、iconv で変換できます。次のコマンドで、変換できることを確かめました。

iconv -f CP932 -t UTF-8 sales_sjis.csv > sales_utf8.csv

Windows で、ファイルが小さいときは、メモ帳で開き、「名前を付けて保存」の「文字コード」で UTF-8 を選ぶ方法があります。ただし、数十MB を超えるファイルは、メモ帳では開きにくくなります。この記事では、Windows の大きなファイルの変換は確認していません。

4. 分割する前に確認すること

セルの中に改行があるか

CSV では、ダブルクォートで囲んだセルの中に、改行を入れられます。備考欄や、問い合わせの内容の欄で、よくあります。CSV分割ツールは、改行を「1行の区切り」として数えるため、セルの中の改行でも、行が分かれたと数えます。

実際に、4件のデータ(うち2件の備考に改行あり)を、2行ずつに分割しました。

ファイル中身状態
split_1.csv1,"1行目↵2行目",100正しい(偶然、境目が合った)
split_2.csv2,通常,200
3,"A
3件目が途中で切れている
split_3.csvB",300
4,通常,400
先頭の行が、前のファイルの続きになっている

4件のデータが、ツールの表示では「総行数: 7行」(ヘッダーを含む)になりました。件数の数え方が、データの件数と合わなければ、セルの中に改行があるサインです。

対処: 改行のあるセルが少なければ、先に Excel で、備考などの列の改行を置き換えます(Ctrl+H で、検索する文字に Ctrl+J を入れ、空白などに置き換え)。改行を含む CSV を、安全に分割する必要があるなら、CSV の構造を理解できるツールやプログラミング言語(Python の csv モジュールなど)を使ってください。

元のデータに空行がないか

CSV分割ツールは、空行を取り除きます。空行を「データの区切り」に使っているファイルは、分割後に区切りが消えます。

5. 分割したあとの作業

全体の集計は、1つの表にならない

分割したファイルは、それぞれ別のブックです。合計や、ピボットテーブルで全体を集計したいときは、ファイルごとの結果を、別に足し合わせることになります。全体を1つの表で扱いたい場合は、Excel の Power Query(データモデルへの読み込み)や、データベース、Python のようなプログラムを使う方法があります。この記事では、Power Query での100万行超の読み込みは、確認していません。

重複を確認する

注文ID のように、重複してはいけない列があるときは、重複を除去ツールで、重複の件数を確認できます。Excel で ID の列だけをコピーして貼り付けると、「N 件の重複が除去されました。」と、除いた件数が表示されます。

  • 使えるのは、1列だけのデータです。このツールは、カンマも区切りとして扱うため、CSV の1行をそのまま貼り付けると、項目がばらばらになります。
  • 10万行のデータは、約1秒で処理できました。100万行は、試していません。
  • ファイルをまたぐ重複は、分割後は見つけられません。確認したいときは、分割する前に、全体の ID の列で確認してください。

6. よくある質問

1,048,576行ちょうどの CSV は、開けますか?
ヘッダーの1行を含めて1,048,576行以内なら、収まります。ヘッダーを含めて1,048,577行になると、最後の1行が読み込まれません。
列が16,384を超えるときは、どうしますか?
CSV分割ツールは、行を分けるだけで、列を分ける機能はありません。列数が多すぎるデータは、プログラムなどで列を分けてください。
分割したファイルを、Excel のファイル(.xlsx)にしたいです。
Excel で開いたあとに、「名前を付けて保存」で .xlsx を選びます。.xlsx にすると、文字コードの問題もなくなります。
ファイルは、サーバーに送られますか?
送られません。ファイルの読み込みも、分割も、保存も、ブラウザの中だけで行います。顧客情報や売上データを含むファイルでも、外部には出ません。
どのくらい大きなファイルまで、分割できますか?
ツール側の上限はありません。ファイル全体をブラウザのメモリに読み込むため、端末のメモリで決まります。この記事では、約71MB(150万行)まで確認しました。数百MBを超えるファイルでは、ブラウザが止まることがあります。

CSV分割ツールを開く

この記事で使ったツール

特記のないツールは、入力した内容をブラウザの中だけで処理し、サーバーへ送りません。詳しくはプライバシーポリシーをご覧ください。

ほかのガイド

ガイドの一覧を見る

記事の内容に誤りや古い点があれば、お問い合わせからお知らせください。 ツールの動作の確認方法や運営方針は、運営者情報にまとめています。