Add duplicate values in CSV in PowerShell

Question

I'm trying to do this in PowerShell. I've got a CSV file that looks like the following (note the ... means any number of values):

Name,...,Value
Adam,...,1
Bob,...,2
Chris,...,3
Adam,...,4
Bob,...,5
Chris,...,6

I want to add all the values for each person together so it outputs the below:

Name,...,Value
Adam,...,5
Bob,...,7
Chris,...,9

I'm using Group-Object to get a hash table.

$table = Import-CSV "c:\input-file" | Group-Object -Property Name -AsHashTable -AsString

But I'm not sure how to proceed to add the values up and then output to a CSV. Can anyone help? Or does anyone have another solution?

Using this [`Join-Object`](https://www.powershellgallery.com/packages/Join) cmdlet (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `$Csv | Merge $Add -On Name, '...' -Property *, @{ Value = { $Left.Value + $Right.Value } }` — iRon, Oct 12 '20 at 12:48

score 1 · Accepted Answer · answered Oct 12 '20 at 14:00

1

Just combine Group-Object, Select-Object and Measure-Object cmdlets to get what you want:

Import-Csv -Path "c:\input-file" | Group-Object Name | 
    Select-Object Name, 
                  @{Name = '...'; Expression = {($_.Group.'...')[0]}}, 
                  @{Name = 'Value'; Expression = { ($_.Group | Measure-Object Value -Sum).Sum }}

Output:

Name  ... Value
----  --- -----
Adam  ...     5
Bob   ...     7
Chris ...     9

^{P.S. In your example Chris,...3,, the comma should be before 3 as in Chris,...,3}

answered Oct 12 '20 at 14:00

Theo

57,719
8
24
41

Ah yes, good pick up on the comma. Corrected it now, thanks! – sjl Oct 12 '20 at 14:52
1

Yes, I know. I had to test it first ;) – sjl Oct 12 '20 at 15:06

score 0 · Answer 2 · answered Oct 12 '20 at 12:00

Hard to guess what the ... represents; taking it literally:

$csvIn = @'
Name,...,Value
Adam,...,1
Bob,...,2
Chris,...,3
Adam,...,4
Bob,...,5
Chris,...,6
'@ | ConvertFrom-Csv -Delimiter ',' # instead of `Import-CSV`
$csvOu = [System.Collections.ArrayList]::new()
$csvGr = $csvIn | Group-Object -Property Name
$csvGr | ForEach-Object {
    [void]$csvOu.Add(
        [pscustomobject]@{
            Name  = $_.Name
            '...' = '...'
            Value = $_.Group |
                Measure-Object -Property Value -Sum |
                Select-Object -ExpandProperty Sum
        }
    )
}
$csvOu #| ConvertTo-Csv -Delimiter ',' -NoTypeInformation

Output: .\SO\64316986.ps1

Name  ... Value
----  --- -----
Adam  ...     5
Bob   ...     7
Chris ...     9

The ... means any number of items. Sorry, it's more of a math term than programming term. — sjl, Oct 12 '20 at 14:50

Add duplicate values in CSV in PowerShell

2 Answers2