CODEWITHSUNDEEP
loopspowershellcsvsum
brotherchris
brotherchris

Reputation: 47

Powershell add like rows together value in column 2

I have been searching for a way to do this in PowerShell, but I have had no luck. I have a file that looks like this.

Column1,column2
A,1
B,2
C,3
D,4
A,2
B,3
D,6
A,3
C,2

I would like to find a way that I could sort through this file and add column2 and end up with a file that would look like this.

column1,column2
A,6
B,5
C,5
D,10

Any help would be greatly appreciated.

Upvotes: 1

Views: 1456

Answers (1)

Matt
Matt

Reputation: 46710

Combination of Group-Object and Measure-Object would accomplish this for you. Provided you have PowerShell 3.0 or higher...

Import-Csv c:\temp\test.csv | Group-Object Column1 | ForEach-Object{
    [pscustomobject][ordered]@{
        Column1 = $_.Name
        Column2 =  $_.Group.Column2 | Measure-Object -Sum | Select-Object -ExpandProperty Sum
    }
} | Export-Csv -NoTypeInformation C:\temp\testOutput.csv

If you have issues with either of these cmdlets please consult the documentation or debug my script to see what it is doing.

Could also use calculated properties with the similar logic and same output. Should work on lower PowerShell versions as well.

Import-Csv c:\temp\test.csv | 
    Group-Object Column1 | 
    Select @{Label="Column1";Expression={$_.Name}},@{Label="Column2";Expression={$_.Group.Column2 | Measure-Object -Sum | Select-Object -ExpandProperty Sum}} |
    Export-Csv -NoTypeInformation C:\temp\testOutput.csv

Upvotes: 1

Related Questions