Power Shell: String Aggregation with count of occurrence

Posted: November 5, 2015 in PowerShell
String Aggregation in power shell using Group by (with count of occurrence)

I have the below file

A B C
103 DA 3
101 A 1
103 BE 3
103 DE 3

Required Output:-

A B C COUNT
103 DA,BE,DE 3 3
101 A 1 1

$Headers = ‘A’,’B’,’C’ #One letter for each column!#—–If the file don’t have header add one as below———-#

$FileLocation = “H:\Desktop\ASSET_POST_DT.txt” #Change to your location!
#$FileFilter = “overall*.csv” #Change to your filename filter

$FileList = Get-ChildItem $FileLocation #-Filter $FileFilter

ForEach ($FSpec in $FileList) {
#— Load File as CSV and add Headers to allow Sorting —
$x = import-csv -Path $FSpec.FullName –Delimiter ‘,’ –Header $Header

#—- Group by on A and C column and do string aggregation on B column
$Y = $x | Group-Object “A”,”C” | Select-Object @{name=“A”;expression={$_.values[0]}}, @{name=“B”;expression={($_.Group | % { $_.B }) -join ‘;’}} ,@{name=“C”;expression={$_.values[1]}}, @{ Label = “Sum”;expression={$_.Count}}

$Y
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s