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

Microsoft Excelは、非常に多くの日常業務に優れています。しかし、時々、その欠点の1つであるスプレッドシートのサイズに遭遇します。 Excelスプレッドシートを小さくする方法、または大きなCSVファイルを複数のファイルに分割する方法を確認するために読んでください。

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

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

100万行を超えると驚異的に聞こえます。ただし、特に特定のタスクでは、行制限に達するのは思ったより簡単です。

たとえば、電子メールでマーケティングを行っている場合、数百万の電子メールアドレスを含むCSVファイルをインポートできます。唯一の問題は、その数のアドレスを含むスプレッドシートをどのように管理するかということです。さらに、誰かが(別のプログラムから)すでに制限を超えているCSVを送信した場合はどうなりますか?

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

大きなCSVファイルは手元にありませんが、自宅で一緒にプレイしたいですか?例ではCOVID-19Open Research Datasetを使用しています。これは、ダウンロードして使用することもできます。

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

そこにいくつかの便利なCSVスプリッタープログラムがあります。ここに最高の2つがあります。ただし、これらのプログラムが動作しているときに、CSV分割プログラムで一般的な問題であるメモリの問題が発生することがあります。

無料の巨大なCSVスプリッター

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

CSVスプリッター

CSVスプリッターは2番目のツールです。少し洗練されたデザインですが、Free Huge CSVSplitterとほぼ同じ機能を提供します。 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

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

  • 「SETBFN =」は、分解する必要のあるCSVを指している必要があります
  • " SET LPF ="は、新しいファイルを制限する行数です
  • 「SETSFN =」は、分割ファイルの新しい命名スキームです

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

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

バッチファイルは、さまざまな日常のタスクに使用できます。ただし、PowerShellスクリプトは、特にこのタイプの処理と除算の場合は高速です。

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

まず、 CTRL + Xを押してWindowsの電源メニューを開き、 PowerShellを選択します。 PowerShellはオプションでない場合、入力は、スタートメニューの検索バーにPowerShellとベストマッチを選択します。

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

 $InputFilename = Get-Content 'C:filelocation'
$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. PowerPivotを使用して大きなCSVを分割する

大きなCSVファイルを小さなビットに分割する最後から2番目のソリューションは、実際にはそれを分割しません。むしろ、大量のCSVファイルをExcelにロードし、PowerPivotツールを使用して開くことができます。そのとおり; Excelの行制限を事実上無視して、プログラム内でファイルを管理できます。

これを実現するには、CSVファイルへのデータリンクを作成し、PowerPivotを使用してコンテンツを管理します。完全な説明とチュートリアルについては、プロセスの詳細を説明しているJoseBarretoのブログをお読みください。

つまり、Barretoは、「最大850万行、まったく問題なし」を使用してピボットテーブルを作成します。上の画像はブログ投稿からのもので、Excelで使用されている合計200万行を示しています。

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

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

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

Split CSVは、COVID-19データセットを適切に管理し、便利なチャンクに分割しました。他のツールと同様に、各ファイルの行数を定義し、ファイルを分割します。ただし、テスト用の大きなCSVファイルがなかったため、エクスペリエンスが異なる場合があります。

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

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

これで、CSVファイルを細かく分割して管理しやすくするための5つのソリューションができました。ソリューションの速度と管理できるCSVファイルのサイズはさまざまであるため、最適なソリューションを見つけるために実験が必要になる場合があります。

画像クレジット:lucadp /