How do I group and sum CSV rows by shared column value?

Question

I'm new with powershell and I am currently stuck with an issue.

I import a CSV file with 2 columns (ServerName, and Size) like this :

Server | Size
-------------
SRV1   | 140

SRV2   | 120

SRV1   | 100

SRV1   | 140

SRV2   | 200

I want to add all Size values for each server, for example:

SRV2 = 120+200
SRV1 = 140+100+140

I have no idea how to do it. I tried with a for loop, but the operation is done for each line, so my results are false. Could anyone help me ?

mklement0 · Accepted Answer · 2018-05-07T15:29:22.870

Use:

the Group-Object cmdlet to group the CSV rows by server name (Server)
then use Select-Object to construct a single output object per group,
containing the server name and the sum of all the associated rows' Size values, obtained via a calculated property that uses the Measure-Object cmdlet:

Import-Csv file.csv | Group-Object Server | 
  Select-Object Name, @{ n='Size'; e={ ($_.Group | Measure-Object Size -Sum).Sum } }

^{If you want the first output column to be named Server, replace Name with @{ n='Server'; e='Name' }}

With your sample data, the above yields:

Name Size
---- ----
SRV1  380
SRV2  320

score 0 · Answer 2 · answered May 07 '18 at 13:25

0

Here is an example how you could do it:

$Data = Import-Csv -Path "yourfilepath" -Delimiter ";"

$SortedData = $Data | Group {$_.server}
$Hashtable = @{}
$SortedData.group | ForEach-Object {
    if ($Hashtable.Contains($_.server)) {
        $Hashtable[$_.server] += ",$($_.size)"
    } else {
        $Hashtable.Add($_.server,$_.size)
    }
}

You need to change your delimiter in your case

answered May 07 '18 at 13:25

guiwhatsthat

2,349
1
12
24

Thanks for you answer. If my CSV contains more than 2 differents servers, will it still work ? Because in my example i have 2 servers, but in fact , it can be 3 or 4. – JohnDoe May 07 '18 at 14:02
it should work with any amount of servers, please mark the answer as solution when it solved your problem. – guiwhatsthat May 07 '18 at 14:09
I think the intent is to actually _sum_ the values (add them up and report the sum), not to generate a string representation of the addition operation. Also note that by using a hashtable the order of the entries is not guaranteed. – mklement0 May 07 '18 at 15:21

How do I group and sum CSV rows by shared column value?

2 Answers2