CODEWITHSUNDEEP
powershell
sjl
sjl

Reputation: 53

Add duplicate values in CSV in PowerShell

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?

Upvotes: 0

Views: 254

Answers (2)

Theo
Theo

Reputation: 61068

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

Upvotes: 1

JosefZ
JosefZ

Reputation: 30113

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

Upvotes: 0

Related Questions