Merge .CSV-rows based on a column's content - sum another colum

Question

I've managed to export some data from several files and create a single .csv using the Export-Csv command in PowerShell. My current .csv file looks like this now:

CA;ArtNr;Menge;CD;CE;CF;CG;CH
RN;42450;7;H;10200;some Text;another column;more text
RN;72250;2;H;10200;some Text;another column;more text
RN;42450;3;H;10200;some Text;another column;more text
RN;72250;2;H;10200;some Text;another column;more text
RN;42450;3;H;10200;some Text;another column;more text

Now in the last step I want to merge all rows that have the same value in column CB and sum up their value of CC. So my desired outcome would be:

CA;ArtNr;Menge;CD;CE;CF;CG;CH
RN;42450;13;H;10200;some Text;another column;more text
RN;72250;4;H;10200;some Text;another column;more text

Anyone able to help me (in a PowerShell-newby-freindly way)?

Thank you in advance!

EDIT If I adjust your code into a script to add it as a next step, it just gives me an empty .csv-file.

$csv = "X:\folder\file.name.csv" | ConvertFrom-Csv -Delimiter ';'
$csv | Group-Object ArtNr | ForEach-Object {
    $_.Group[0].Menge = ($_.Group.Menge | Measure-Object -Sum).Sum
    $_.Group[0]
}
$csv | Export-Csv -Path "x:\folder\file.name.new.csv" -Delimiter ";" -NoTypeInformation

What do I do wrong?

Answer 1

Here is one way you can do it. This will take the first value of each group, however, as in my comment, if the values will not be the same on each item of each group (excluding CB & CC) you need to specify how to handle that.

$csv = @'
CA;CB;CC;CD;CE;CF;CG;CH
RN;42450;7;H;10200;some Text;another column;more text
RN;72250;2;H;10200;some Text;another column;more text
RN;42450;3;H;10200;some Text;another column;more text
RN;72250;2;H;10200;some Text;another column;more text
RN;42450;3;H;10200;some Text;another column;more text
'@ | ConvertFrom-Csv -Delimiter ';'

$csv | Group-Object CB | ForEach-Object {
    $_.Group[0].CC = ($_.Group.CC | Measure-Object -Sum).Sum
    $_.Group[0]
} | Export-Csv -Path "x:\folder\file.name.new.csv" -Delimiter ";" -NoTypeInformation

Result looks like this:

CA CB    CC CD CE    CF        CG             CH
-- --    -- -- --    --        --             --
RN 42450 13 H  10200 some Text another column more text
RN 72250  4 H  10200 some Text another column more text

Edit:

If you were importing the data from a CSV it would look like this:

$csv = Import-Csv "X:\path\to\file.csv" -Delimiter ';'

$csv | Group-Object CB | ForEach-Object {
    $_.Group[0].CC = ($_.Group.CC | Measure-Object -Sum).Sum
    $_.Group[0]
} | Export-Csv -Path "x:\folder\file.name.new.csv" -Delimiter ";" -NoTypeInformation

Or you can directly pipe the import to the script, like this:

Import-Csv "X:\path\to\file.csv" -Delimiter ';' | Group-Object CB |
ForEach-Object {
    $_.Group[0].CC = ($_.Group.CC | Measure-Object -Sum).Sum
    $_.Group[0]
} | Export-Csv -Path "x:\folder\file.name.new.csv" -Delimiter ";" -NoTypeInformation