大容量CSV - エクセルで三度失敗して気づいたこと
大容量CSVを受け取ったとき、私たちがする行動はいつも同じです。ダブルクリック。そして待つ。精算システムから落としたファイル一つが100MBを超えると、エクセルは応答なしに陥ります。
面白いのはその次です。このファイルと格闘する人は、たいてい三度行き詰まります。そして三つの失敗はそれぞれ違うことを教えてくれます。最後の失敗にたどり着いて初めて、そもそも接近の仕方が間違っていたことが見えてきます。
- 大容量CSVで起きる三つの症状の原因をそれぞれ区別できます。
- エクセルで開いて保存するとなぜ危険なのか、根拠とともにわかります。
- 開く代わりに抜き出す方式へ切り替える方法がわかります。
一つ目の失敗: 開きはするが作業できない
数分待てば開きはします。問題はそこからです。Ctrl+Fを一度押すだけでまた長く固まり、列が数十個あるので目当ての列を探すには横へ延々とスクロールすることになります。
ここでたいてい「パソコンが遅いのか」と考えますが、原因は性能ではありません。エクセルはファイル全体を画面に載せる道具だからです。ですからファイルが大きくなるほど遅くなるのは当然で、性能を上げても曲線の傾きが少し緩やかになるだけです。
必要なのは三つか四つの列と条件に合う数千行だけなのに、100万行を丸ごと載せてその中をさまよっているわけです。
二つ目の失敗: データが途中で切れている
これがいちばん危険な失敗です。先のものは不便なだけですが、これは気づかず通り過ぎてしまいます。
エクセルのワークシートには決まった限界があります。マイクロソフトの公式文書基準で最大1,048,576行×16,384列、旧来のxls形式は65,536行です。CSVファイル自体はただのテキストなので行数の制限がなく、限界を超えるファイルが実務にはふつうに出回っています。
| 形式 | 表示できる最大行 |
|---|---|
| エクセル xls(旧来) | 65,536行 |
| エクセル xlsx(現行) | 1,048,576行 |
| CSVファイル自体 | 制限なし(テキスト) |
限界を超えるファイルを開くと104万行までしか読み込まれず、残りは画面にありません。ところが画面は問題なさそうに見えます。ここで何気なく保存すると切れた分が欠けたファイルになり、元のファイルを上書きしていたら戻す方法がありません。
元のCSVにはデータがそのまま残っています。切れたのは画面に載った複製だけです。ですから規則は単純です。大きなファイルは開いて保存せず、元のファイルから必要なものだけ取り出します。確認が必要なら元のファイルを複製して作業してください。
三つ目の失敗: 文字が読めない記号に見える
やっと開いたのに名前や住所がすべて化けていると、ファイルが壊れたのかと思ってもう一度ダウンロードすることになります。ところが取り直しても同じです。
ファイルが壊れたのではなく文字を解釈する方式が合っていないのです。韓国語環境ではUTF-8とEUC-KR系が混ざって使われるため、保存された方式と異なる基準で開くと化けて見えます。元のファイルは無事で、開く側を変えれば済む問題です。
ただしここにもう一つ落とし穴があります。化けた状態で保存してしまうとそのときは本当に壊れたファイルになります。画面がおかしいときは保存の前にエンコーディングから確認してください。
大容量CSVで三度行き詰まって初めて見えること
三つの失敗を並べると共通点が現れます。すべて「ファイル全体を開こうとしたから」生じた問題です。遅いのも、切れるのも、化けるのも、すべて開く過程で起きています。
そこで方向を変えます。開かずに条件に合う行や必要な列だけを選んで新しいファイルとして受け取るのです。元がどれだけ大きくても結果は軽く、切れることもありません。
パワークエリやPythonでもできますが、覚える時間がなかなかかかります。急ぎの実務なら大容量CSVデータ抽出にファイルを上げるほうが早いです。エンコーディングを自動判別し、デスクトップ基準で250MBまで処理されます。モバイルはブラウザが使えるメモリが小さいため30MBに制限されるので、大きなファイルはデスクトップで開いてください。
条件の列と値を指定すると、一致する行だけを抜き出します。注文履歴から特定の商品コードが入った行だけ、精算内訳から特定の加盟店の分だけを選ぶ、という具合です。値を空にすると全行が対象になるので、何件あるか数える用途にも使えます。
数十個の列のうち必要なものだけを複数選び、その列だけを含むファイルとして受け取ります。注文番号・金額・日付だけを抜いて報告書に貼る場合が代表的です。重複を取り除いた固有値だけを抜くこともできるので、どんな値が入っているかを把握するときにも役立ちます。
顧客情報が入ったファイルなら
注文や会員のデータには氏名・連絡先・住所が入っていることが多くあります。ファイルをサーバーへアップロードして処理するサービスなら、そのデータが外部に出ることになるので、上げる前に処理方式を確認する習慣が必要です。ブラウザの中だけで処理されるかどうかが判断の基準です。ここで紹介した抽出ツールはファイルがブラウザの外に出ません。
抽出した結果を書類として提出する必要があるなら、PDF結合で他の証憑と一つのファイルにまとめる方法もあります。書類をまとめる順序は証憑書類のPDFの記事に整理してあります。
ひと言でまとめると
大容量CSVを前にして私たちが本当に欲しかったのは「ファイルを開くこと」ではなくその中の数行、数列でした。開こうとするのをやめて必要なものだけ取り出せば、三つの失敗が一度に消えます。
いま開かないファイルがあるなら、大容量CSVデータ抽出に上げてみてください。
参考資料
- Microsoft「Excel specifications and limits」: ワークシートは最大1,048,576行×16,384列。文書を見る
- Microsoft「What to do if a data set is too large for the Excel grid」: グリッドより大きなデータの扱いの案内。文書を見る