CODEWITHSUNDEEP
powershell
mws222
mws222

Reputation: 13

Powershell - Sum across rows without grouping

I'm running a powershell script on a csv file with a format similar to:

A,B,C,100
A,B,D,200
B,A,B,100
C,A,B,100

The script adds an additional column to generate keys for each line:

A,B,C,100,AB
A,B,D,200,AB
B,A,B,100,BA
C,A,B,100,CA

I'm getting stuck on the last step of the script, I need to sum across the file any rows that have the same key. The only issue is that I need to maintain the original layout of the file, so I need to output the sum across each line and can't roll data away by using grouping within powershell. My desired output is:

A,B,C,100,AB,300
A,B,D,200,AB,300
B,A,B,100,BA,100
C,A,B,100,CA,100

Any suggestions on how I can accomplish this?

Upvotes: 1

Views: 104

Answers (1)

Anthony Stringer
Anthony Stringer

Reputation: 2001

$a = @'
A,B,C,100,AB
A,B,D,200,AB
B,A,B,100,BA
C,A,B,100,CA
'@

$a = $a | ConvertFrom-Csv -Header 1,2,3,4,5

$a = $a | group '5' | % {$tot = 0; $_.group.4 | % {$tot += $_}; $_.group | select *, @{n='6';e={$tot}}}

$a | ft

Upvotes: 1

Related Questions