巨大なCSV Excelスプレッドシートを個別のファイルに分割する方法

Microsoft Excelは、日常の多くのタスクに優れています。しかし、時々、スプレッドシートのサイズという短所に出くわします。Excelスプレッドシートを小さくする方法や、大きなCSVファイルを複数のファイルに分割する方法を説明しており、選択できる方法がいくつかあります。

大きなCSVを複数のファイルに分割する理由

「なぜ大きなExcelファイルを複数の小さなファイルに分割する必要があるのか」と思うかもしれません。Excelのスプレッドシートの行の制限が1,048,576であることを考えると、興味深い質問です。

100万行を超える行数は驚異的に聞こえます。しかし、特に特定のタスク中は、行の制限に達するのは思ったより簡単です。たとえば、メールでマーケティングを行っている場合、何百万ものメールアドレスを含むCSVファイル（CSVファイルとは？）をインポートすることがあります。唯一の問題は、そのアドレスを何個も入力したスプレッドシートをどのように管理するかです。さらに、誰かがすでに制限を超えているCSV（別のプログラムから）を送信してきたらどうでしょうか。

それが直面する可能性のある問題のように聞こえる場合は、大きなCSVまたはExcelファイルを複数の小さなファイルに分割する次の5つの方法を確認してください。

大きなCSVファイルが手元にないけれど、家で試してみたいですか？例では、ダウンロードして使用できるCOVID-19 Open Research Datasetを使用しています。

1. プログラムを使用してCSVファイルを分割する

役立つCSVスプリッタープログラムがいくつかあります。ここではそのうちの2つを紹介します。ただし、これらのプログラムは動作しますが、メモリの問題が発生することがあります。これは、CSV分割プログラムの一般的な問題です。

Free Huge CSV Splitter

Free Huge CSV Splitterは、基本的なCSV分割ツールです。分割するCSVファイル、使用する行数を入力し、[Split File]を選択します。行数によって、最終的に出力されるファイルの数が決まります。

CSV Splitter

CSV Splitterは2つ目のツールです。Free Huge CSV Splitterとほぼ同じ機能を提供しますが、デザインが少し洗練されています。CSVを小さなチャンクにすばやく分割し、使用する行数を決定できます。

2. バッチファイルを使用する

次に、プログラム可能なバッチファイルを作成します。バッチファイルを使用して、CSVを小さなチャンクに処理し、ファイルをカスタマイズして異なるチャンクを提供できます。

新しいテキストドキュメントを開き、次の内容をコピーして貼り付けます。

@echo off
setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=HCAHPSHospital.csv
REM Edit this value to change the number of lines per file.
SET LPF=2500
REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=HosptialSplitFile
REM Do not change beyond this line.
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F "delims==" %%l in (%BFN%) Do (
SET /A LineNum+=1
echo %%l >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
)
)
endlocal
Pause

実行する前にバッチファイルを構成する必要があります。各コマンドの機能を説明します。バッチファイルのサイズと必要な出力に合わせて変更できます。

「SET BFN =」は、分割する必要があるCSVを指す必要があります
「SET LPF =」は、新しいファイルを制限する行数です
「SET SFN =」は、分割されたファイルの新しい命名規則です

変数を入力したら、[ファイル] > [名前を付けて保存]に進みます。ファイル名を選択し、[保存]を選択します。次に、新しく保存したテキストファイルを選択してF2を押して名前を変更します。.txt拡張子を.batに置き換え、警告が表示されたら[OK]を押します。これで、大きなCSVファイルを小さな出力ファイルに分割できます。

3. PowerShellスクリプトを使用してCSVファイルを分割する

バッチファイルは、さまざまな日常業務に使用できます。しかし、PowerShellスクリプトは、特にこの種の処理や分割には高速です。

次のスクリプトは、大きなCSVをすばやく小さなファイルに分割します。

まず、CTRL + Xを押してWindowsの電源メニューを開き、[PowerShell]を選択します。PowerShellがオプションでない場合は、スタートメニューの検索バーにpowershellと入力し、最良の一致を選択します。

次に、次のスクリプトをコピーして貼り付けます。

$InputFilename = Get-Content 'C:\ファイル\場所'
$OutputFilenamePattern = 'output_done_'
$LineLimit = 50000
$line = 0
$i = 0
$file = 0
$start = 0
while ($line -le $InputFilename.Length) {
if ($i -eq $LineLimit -Or $line -eq $InputFilename.Length) {
$file++
$Filename = "$OutputFilenamePattern$file.csv"
$InputFilename[$start..($line-1)] | Out-File $Filename -Force
$start = $line;
$i = 0
Write-Host "$Filename"
}
$i++;
$line++
}

最初の行のファイルの場所をCSVファイルに置き換え、スクリプトを実行します。スクリプトは、ユーザーディレクトリに小さなCSVファイルを出力します。たとえば、私のCSVファイルはC:\Users\Gavinにあり、ファイル名はoutput_done_1.csvです。$OutputFilenamePattern = 'output_done_'行を変更することで、出力名を変更できます。

元のスクリプトはSPJeffにあります。

4. Power Pivotを使用して大きなCSVを分割する

大きなCSVファイルを小さなビットに分割するという最後から2番目の解決策は、実際にはそれを分割しません。むしろ、巨大なCSVファイルをExcelにロードして、Power Pivotツールを使用して開くことができます。そうです。Excelの行の制限を効果的に無視して、プログラム内でファイルを管理できます。

CSVファイルへのデータリンクを作成し、Power Pivotを使用してコンテンツを管理することで、これを実現します。詳しい説明とチュートリアルについては、Jose Barretoのブログでプロセスを詳しく説明しています。

簡単に言えば、Barretoは「最大850万行を問題なく」使用してピボットテーブルを作成します。上の画像はブログ記事からのもので、Excelで合計200万行が使用されていることがわかります。

このプロセスでは、CSVは小さなチャンクに分割されません。ただし、CSVをExcelで操作できるようになるため、非常に便利な代替手段となります。さらにヒントが必要な場合は、データ分析にピボットテーブルを使用する方法を確認してください。

5. Split CSVを使用してオンラインで大きなCSVを分割する

大きなCSVファイルを小さなビットに分割するオンラインサービスもあります。そのようなオプションの1つは、無料のオンラインCSVスプリッターであるSplit CSVです。

Split CSVは、COVID-19のデータセットをうまく管理し、便利なチャンクに分割しました。他のツールと同様に、各ファイルの行数を定義し、それを分割します。ただし、テストする大きなCSVファイルが手元にありませんでした。そのため、状況は異なる場合があります。

Split CSVには、プレミアムオプションも含まれています。サブスクリプション料金を支払うことで、カスタム区切り記号を使用したり、出力ファイルの種類を選択したり、出力ファイルから特定の文字を削除したり、重複する行を削除したりできます。

CSVファイルを管理しやすいチャンクに分割する

これで、CSVファイルを小さなビットに分割し、管理しやすくするための5つのソリューションが用意されました。ソリューションは速度と管理できるCSVファイルのサイズが異なるため、自分に最適なソリューションを見つけるために試行錯誤する必要があるかもしれません。そして、何よりも素晴らしいのは？これらのCSV分割手法は、Windows 10とWindows 11で機能します。macOSとLinuxでもオンラインCSV分割ツールを使用できます！

パソコンの友達