「すいません、このテキストファイルのデータを集計したいのですが・・・」
おもむろに見せられたテキストファイルは10GB超え。
あれ？桁数を勘定し間違えたかな？
1GB超えくらいかな？
いえいえ、間違いなく10GBを超えておりました。
そんな超ド級のテキストファイルからデータを抽出したい、
と言われたときの絶望感。
しかしシステム担当はそんな悩みにも応えなければならない。
大丈夫。
私にはPowerShellがついている。
（この記事ではPowerShellを使った大容量テキストファイルの扱い方について解説します。）
環境
Excel 2019
Access 2019
PSVersion 5.1.18362.145
結論
メモ帳では開けないような大容量のデータもPowerShellを使えば見ることができます。
また中身を見るだけではなく条件を指定してデータを抽出することもできますよ～という話です。
データを見る
テキストファイルの中身を見るときは、PowerShellのGet-Contentコマンドレットを使用します。
Get-Contentコマンドは一度にファイルを読み込むことはせず、1行ずつ読み込むためファイルのサイズは関係なし！というとても便利なやつです。
the content is read one line at a time
『Get-Content (Microsoft.PowerShell.Management) - PowerShell | Microsoft Docs』より
以下、Get-Contentコマンドレットの、よく使うであろう例です。
（以下の例では、C:\test.txt(又はtest.csv)が大容量のテキストファイルだと思ってください。）
Get-Contentで先頭10行だけ見たい場合
Get-Content 'C:\test.txt'-TotalCount 10
-TotalCount 10の10を見たい行数に変更します。
Get-Contentで末尾の10行だけ見たい場合
Get-Content 'C:\test.txt'-Tail 10
-Tail 10の10を見たい行数に変更します。
PowerShellでデータを抽出する
PowerShellではテキストファイルからレコードを抽出することも可能です。
歴史のある基幹システムからデータを抽出する場合は、出力されたデータ形式が固定長(レコードの長さが決まっている形式)の場合も多々あります。
比較的最近の基幹システムならCSVファイルなど、可変長で、区切り文字があるファイルが一般的かも知れません。
固定長のデータで条件を満たす行だけ見たい場合
例えば、先頭の4文字が「test」、5～6文字から「01」or「02」に一致するデータだけを抽出したい場合は下記のコマンドで抽出できます。
Get-Content 'C:\test.txt' -TotalCount 2|where-object{$_.substring(0,4) -eq "test"}|where-object{$_.substring(5,2) -match '01|02'}
固定長の場合、１行をひとつの長い文字列と見立てて何文字目から何文字目が何になっているかを調べることでデータを抽出します。
Substringメソッドと正規表現を駆使します。
正規表現は色々な指定ができるので柔軟な条件指定が可能なのもうれしいところ。
CSVデータから抽出する
次にCSVデータのような区切り文字があるデータからレコードを抽出する場合の例です。
この中から血液型がAのものだけを抽出するとします。
Import-Csv C:\test.csv |where{$_.血液型 -eq 'A'}
Import-Csvコマンドレットは1行目をプロパティとするカスタムオブジェクトを出力します。
その出力をwhere-objectにパイプしてあげます。
パイプを受け取ったコマンドは「.血液型」のようにプロパティを参照することができます。
固定長の場合よりも直感的に条件を指定できるところが○。
また、Import-CsvコマンドレットはCsvと言っていますが、-Delimiterオプションを使うことで、区切り文字を指定することもできるので状況に応じて柔軟に対応することができます。
抽出したデータを別ファイルに出力する
上記のコマンドを実行すると、すべてPowerShellの標準出力(デフォルトではコンソール)に出力されます。
これだと二次利用ができないので、ファイルに出力してあげます。
ファイルに出力したい場合は、Out-File ファイルにパイプしてあげると簡単です。
import-Csv C:\test.csv |?{$_.血液型 -eq 'A'} |Out-File sorted.csv-Encoding default
上述のコマンドの最後の出力をOut-Fileコマンドレットにパイプするとファイルに出力することができます。
ここでひとつ注意なのがOut-Fileコマンドレットの文字コードです。
デフォルトの設定だと、Unicodeで出力されます。
Excelなどを使って二次利用する場合にUnicodeだと手間がかかるので、SJISで出力できると便利です。
その場合は、defaultを指定してあげます。
もう一つの注意点として、抽出結果が膨大だと相応の時間はかかります。
（それでも数分で終わります）
とある依頼
基幹システムから抽出したデータをExcelなどで集計することはよくあります。
基幹システム側で柔軟に集計できたらいいでしょうが、なかなかうまくはいきません。
特に古いシステムだと、お金も工数も膨大だし、一度作っても要件がどんどん変わっていくので
あまり意味のないものとなってしまうことが多いからです。
そのため、逃げの口上として、基幹システムのデータをエクスポートできるようにして、後は自分で何とかしてねということがよくあります。
エクスポートしたデータをExcelなりAccessなりで自分で集計して資料とすると。
今回の依頼は、数年分のデータから特定の条件を満たすレコードを抽出し、クロス集計したいというもの。
データ量が少なければExcelやAccessでピボットテーブルを駆使すれば済む話ですが、今回はそうはいきません。
ExcelやAccessでは扱えない
Excelは2019の時点で扱える最大1,048,576行なので大容量のデータの扱いには向きません。
また、行数が増えてくると、動きがもっさりしてかなりのストレス。
時にはそのままフリーズしてしまうこともよくあります。
ではAccessはどうかというと、おおよそ2GBまでのデータが扱えるとのこと。
Excelよりは大容量データを扱えますが、それでも10GBを超えるようなデータは扱えません・・・。
SQLSever等のデータベースを立てることも一案ですが、エンドユーザーにこまでさせるのかいって感じです。
自分のPCにそんなもの立てたくないですし・・・そのためのサーバーもないし・・・。
もしかしたらMicrosoftが押している、Power BIがいいんじゃない？と思って調べてみましたが、無料版は10GBまでだそうです。
これでは10GBを超えるファイルを扱えないので今回は見送り。
さて、どうするか。
PowerShellの出番だ
もう、そういうときは自分でプログラムを作ってしまうのが一番です。
といっても一時の使い捨てに時間を割きたくないのでワンライナーで書きたい。
Windowsでワンライナーと言えば、PowerShell先生です。
PowerShellを使えば行数やデータ容量を気にせず、大容量データを扱うことができます。
結論に書いてある命令をパイプで組み合わせてあげて、データを抽出することができました。
固定長のデータで、最初の2文字が「TT」で、10文字目から2文字が01～08のどれかという抽出条件でしたので、
このようになりました。
Get-Content "C:\data.txt" |?{$_.substring(0,2) -eq "TT"}|?{$_.substring(10,2) -match '01|02|03|04|05|06|07|08'} |Out-File -Encoding default -FilePath "out.txt"
結果をSJISの文字コードでout.txtというファイルに出力しました。
幸い抽出したデータが100万行以内に収まっていたので、そのデータをExcelでピボット集計してもらいました。
抽出したデータが1GBくらいであれば、Accessを使えばいい感じですね。
それ以上の場合は・・・またそのとき考えます。
PowerShellを使えば大容量のデータも比較的楽に扱えますよというお話でした。
終わりに
デジタル化がどんどん進み、データ量がどんどん増えてきている昨今。
今回はなんとかPowerShellで乗り切ることができましたが、このような依頼が増えてくることは明らか。
有償のBIツールはありますが、経営陣の頭が固いと「収益につながらん！！」といって中々導入は厳しいもの・・・
というか基幹システム刷新を考えてください
それでもありものでなんとか間に合わせようと知恵を絞るのも楽しいですけどね・・・
配られたカードで勝負することが大事です！！
偉そうに言ってますが大体はスクリプトでなんとかなっちゃいますけどね。
それも知っていればのこと。
知らなければ始まらないので日々勉強です。
ということで、ここまでお読みいただき、ありがとうございました。
