Home > Enterprise >  Consolidate csv/array data in powershell
Consolidate csv/array data in powershell

Time:12-07

topic,counter,details1,details2

books,3,green,red
books,5,green,yellow
books,1,green,red
books,2,green,red
books,8,blue,grey
house,1,green,green
house,2,red,red
house,5,green,green

If columns 1, 3, 4 of a line are equal and there are duplicated in list then they should consolidated.

So results should look like. So line 3,4,8 are eliminated and the counters for line 1 and 6 increment.

books,6,green,red
books,5,green,yellow
books,8,blue,grey
house,6,green,green
house,2,red,red

Any idea how to realize this in PowerShell?

CodePudding user response:

Use the Group-Object cmdlet to group objects by common property values, then sum the counter property from each object within each group:

Import-Csv ./path/to/file.csv |Group-Object topic,details1,details2 |ForEach-Object {
    # for each group of distinct objects, create 1 new object where the `counter` value is the sum of all values in the group
    [pscustomobject]@{
        topic = $_.Group[0].topic
        topic = ($_.Group |Measure-Object counter -Sum).Sum
        details1 = $_.Group[0].details1
        details2 = $_.Group[0].details2
    }
} |Export-Csv ./path/to/output.csv -NoTypeInformation
  • Related