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 cmdlet (see also: In Powershell, what's the best way to join two tables into one?): $Csv | Merge $Add -On Name, '...' -Property *, @{ Value = { $Left.Value + $Right.Value } } — iRon
– iRon, Commented Oct 12, 2020 at 12:48

Theo · Accepted Answer · 2020-10-12 14:00:25Z

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, 2020 at 14:00

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sjl Over a year ago

Ah yes, good pick up on the comma. Corrected it now, thanks!

sjl Over a year ago

Yes, I know. I had to test it first ;)

JosefZ · Accepted Answer · 2020-10-12 12:00:43Z

0

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

answered Oct 12, 2020 at 12:00

JosefZ

30.5k6 gold badges52 silver badges96 bronze badges

1 Comment

sjl Over a year ago

The ... means any number of items. Sorry, it's more of a math term than programming term.

Collectives™ on Stack Overflow

Add duplicate values in CSV in PowerShell

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related