Search code examples
powershellcsvgrouping

How can I find matching string name in the same object and then add value that is contain on each of the matching property?

I created a powershell command to collect and sort a txt file.

Input example:

a,1  
a,1  
b,3  
c,4  
z,5

The output that I have to get:

a,2  
b,3  
c,4  
z,5

Here is my code so far:

$filename = 'test.txt'
Get-Content $filename | ForEach-Object {
    $Line = $_.Trim() -Split ','
    New-Object -TypeName PSCustomObject -Property @{
        Alphabet= $Line[0]
        Value= [int]$Line[1]
    }
}

example with negative value input

a,1,1
a,1,2
b,3,1
c,4,1
z,5,0

Solution

    • Import your text file as a CSV file using Import-Csv with given column names (-Header), which parses the lines into objects with the column names as property names.

    • Then use Group-Object to group the objects by shared .Letter values, i.e. the letter that is in the first field of each line.

    • Using ForEach-Object, process each group of objects (lines), and output a single string that contains the shared letter and the sum of all .Number property values across the objects that make up the group, obtained via Measure-Object -Sum:

    @'
a,1
a,1
b,3
c,4
z,5
'@ > ./test.txt

Import-Csv -Header Letter, Number test.txt |
  Group-Object Letter |
  ForEach-Object {
    '{0},{1}' -f $_.Name, ($_.Group | Measure-Object -Sum -Property Number ).Sum
  }

    Note: The above OOP approach is flexible, but potentially slow. Here's a plain-text alternative that will likely perform better:

    Get-Content test.txt |
  Group-Object { ($_ -split ',')[0] } |
  ForEach-Object {
    '{0},{1}' -f $_.Name, ($_.Group -replace '^.+,' | Measure-Object -Sum).Sum
  }

    See also:

    Grouping with summing of multiple fields:

    It is easy to extend the OOP approach: add another header field to name the additional column, and add another output field that sums that added column's values for each group too:

    @'
a,1,10
a,1,10
b,3,30
'@ > ./test.txt

Import-Csv -Header Letter, NumberA, NumberB test.txt |
  Group-Object Letter |
  ForEach-Object {
    '{0},{1},{2}' -f $_.Name, 
                  ($_.Group | Measure-Object -Sum -Property NumberA).Sum, 
                  ($_.Group | Measure-Object -Sum -Property NumberB).Sum
  }

    Output (note the values in the a line):

    a,2,20
b,3,30

    Extending the plain-text approach requires a bit more work:

    @'
a,1,10
a,1,10
b,3,30
c,4,40
z,5,50
'@ > ./test.txt

Get-Content test.txt |
  Group-Object { ($_ -split ',')[0] } |
  ForEach-Object {
    '{0},{1},{2}' -f $_.Name, 
                  ($_.Group.ForEach({ ($_ -split ',')[1] }) | Measure-Object -Sum).Sum, 
                  ($_.Group.ForEach({ ($_ -split ',')[2] }) | Measure-Object -Sum).Sum
  }