簡単だけど、面倒くさい

「このテキストファイルを〇〇別に集計して」∑(ﾟдﾟlll)

こんな依頼や作業も少なくない。
本当はもう少し下っ端にやって欲しいところだけど、人手不足でそうも言ってられず。

一回きりであれば、Excelで取り込んでピボット集計してハイ終わりなんだけど、
ファイル数が多かったり、同じファイルでも何度もやっているととても面倒くさい！

それこそ、元ファイルサイズが 数十MB とか、いちいちExcelでやってられない。
パソコン固まるし。

世の中そんな悩みを抱えるシステム屋さんも多いはず（あれ？オレだけ？）

まぁツール探せばあるかもしれないが、今回はPowerShellでのやり方。
Windows7以降なら基本入っているので、ツールインストール出来ない環境などでは使えるかな。

固定長ファイルを集計してCSV出力する

まずはヘッダーがないテキストファイルで想定。
indata.txt

A         1    
B         2    
A         3    
B         4    
C         5

先にざっくり流れを書くと、 固定長ファイル→CSV形式→グルーピング→集計→CSVファイル化 を1コマンドでやってしまおうということ。

1. 固定長を無理やりCSV形式に変換（ConvertFrom-Csv）

PS F:\test> cat ./indata.txt | %{ $_.substring(0, 10).trim() + "`t" + $_.substring(10, 5).trim() } | ConvertFrom-Csv -header item, qty -Delimiter "`t"
item        qty  
----        ---
A           1
B           2
A           3
B           4
C           5

前半cat ./indata.txt | %{ $_.substring(0, 10).trim() + "`t" + $_.substring(10, 5).trim() }は固定長データをタブ文字区切りのデータ部に変換します。

ExcelでCSVファイルを開くと、先頭ゼロや、空白が自動で削られる事があるので、"=`"" + $_.substring(0, 10).trim() + "`""みたいにしておくと、いい感じにExcelで使える。

後半の| ConvertFrom-Csv -header item, qty -Delimiter "`t"では、タブ文字区切りしたデータ部にヘッダー名を付与してCSV形式に変換かけてます。

2. 項目別にデータをグループ化（Group-Object）

グループコマンドレットで先ほど指定したヘッダー名itemでグルーピングしてみると。。。

PS F:\test> cat ./indata.txt | %{ $_.substring(0, 10).trim() + "`t" + $_.substring(10, 5).trim() } | ConvertFrom-Csv -header item, qty -Delimiter "`t" | group item

Count Name                      Group
----- ----                      -----
    2 A                         {@{item=A; qty=1}, @{item=A; qty=3}}
    2 B                         {@{item=B; qty=2}, @{item=B; qty=4}}
    1 C                         {@{item=C; qty=5}}

おぉ～いい感じでitemでグルーピングしている。
後は合計部を作りこめばOK

3. 項目別に合計する（Select-Object、Measure-Object）

おもむろに下記コマンドを実行します。

PS F:\test> cat ./indata.txt | %{ $_.substring(0, 10).trim() + "`t" + $_.substring(10, 5).trim() } | ConvertFrom-Csv -header item, qty -Delimiter "`t" | group item | select @{Name="sumitem";Expression={$_.Name}}, @{Name="sumqty";Expression={($_.group | measure -sum qty).sum}}

sumitem    sumqty
-------    ------
A               4
B               6
C               5

見事に項目別に集計されてます。

4. CSVに出力する（Export-Csv）

ま、最後は簡単でExport-CSV

PS F:\test> cat ./indata.txt | %{ $_.substring(0, 10).trim() + "`t" + $_.substring(10, 5).trim() } | ConvertFrom-Csv -header item, qty -Delimiter "`t" | group item | select @{Name="sumitem";Expression={$_.Name}}, @{Name="sumqty";Expression={($_.group | measure -sum qty).sum}} | Export-Csv -NoTypeInformation outdata.csv

中身を確認するとちゃんとCSV形式で出力されてます。

PS F:\test> cat .\outdata.csv
"sumitem","sumqty"
"A","4"
"B","6"
"C","5"

補足

項目別に合計するのところでぶっ飛ばしましたがselect @{Name="sumitem";Expression={$_.Name}}, @{Name="sumqty";Expression={($_.group | measure -sum qty).sum}}でやっていることはそれほど難しくないです。

@{Name="sumitem";Expression={$_.Name}}
最終出力するヘッダー名が Name になるのが気に食わなかったので、別名にしているだけ。
@{Name="sumqty";Expression={($_.group | measure -sum qty).sum}}
グループ内のqty値を集計しているだけです。
もし集計したい項目がqty1, qty2などの場合は、@{Name="sumqty1";Expression={($_.group | measure -sum qty1).sum}}, @{Name="sumqty2";Expression={($_.group | measure -sum qty2).sum}}になるんでしょうか（適当

暇つぶし

PowerShellでテキストファイル（CSV）を集計してみる