Sum rows from csv

PowerShell Help
1

Given the following input .csv contents:

Path               	    Hard	Used
/server/IT330/share1	20	    100
/server/IT330/share2	20	    200
/server/IT330/share3	10	    400
/server/IT330/share4	0	      0
/server/IT330/share5	0	    300
/server/FI150/share6	0	    300
/server/FI150/share7	5	    233

I need to summarize the “Hard” and “Used” capacities each Financial code. I’m looking for the following output:

Department    Hard    Used
IT330         50      1000
FI150          5       533

So far, I have the following code:

$IdentityColumns = @('Path')
$ColumnsToSum = @('Hard', 'Used')
$CSVFileInput = '.\test.csv'
$ZonePath = '/server/'
    
Import-Csv -Path $CSVFileInput |
Group-Object -Property $IdentityColumns |
ForEach-Object { 
    $Trimmed = $_.Name.TrimStart($ZonePath).Split('/')[0]
    $resultHT = @{ Path = $Trimmed } 
    @($_.Group | Measure-Object -Property $ColumnsToSum -Sum ) | 
    ForEach-Object { $resultHT[$_.Property] = $_.Sum }
    return [PSCustomObject]$resultHT
    } |   # End of ForEach-Object by groups
    Select-Object @($IdentityColumns + $ColumnsToSum)

However, it seems my Measure-Object is not working.
I’m not getting the expected results.

This is what I get:

Path  Hard Used
----  ---- ----
FI150    0  300
FI150    5  233
IT330   20  100
IT330   20  200
IT330   10  400
IT330    0    0
IT330    0  300

Any tips greatly appreciated.

2

I’m not completely sure …

$CSVInput = @'
Path,Hard,Used
/server/IT330/share1,20,100
/server/IT330/share2,20,200
/server/IT330/share3,10,400
/server/IT330/share4,0,0
/server/IT330/share5,0,300
/server/FI150/share6,0,300
/server/FI150/share7,5,233
'@ |
ConvertFrom-Csv |
Select-Object -Property *,
@{Name = 'Department'; Expression = { ($_.Path -split '\/')[2] } }

$GroupedInput = $CSVInput | Group-Object -Property Department
$Result =
foreach ($Group in $GroupedInput) {
    [PSCustomObject]@{
        Department = $Group.Name 
        Hard       = ($Group.Group.Hard | Measure-Object -Sum).Sum
        Used       = ($Group.Group.Used | Measure-Object -Sum).Sum
    }
}
$Result

… does that fit?

BTW: When you post sample data you should post them in a way where we do not need to edit them manually. :wink:

3

I did similar to @Olaf:

$csvData = @"
Path,Hard,Used
/server/IT330/share1,20,100
/server/IT330/share2,20,200
/server/IT330/share3,10,400
/server/IT330/share4,0,0
/server/IT330/share5,0,300
/server/FI150/share6,0,300
/server/FI150/share7,5,233
"@

$csv = $csvData | ConvertFrom-Csv

foreach ($row in $csv) {
    $row.Path = ($row.Path -Split '/')[2]
} 

$csv | Group-Object -Property Path | 
    Foreach-Object {
        [PSCustomObject] @{
            Department = $_.Name
            Hard       = ($_.Group | Measure-Object -Property Hard -Sum).Sum
            Used       = ($_.Group | Measure-Object -Property Used -Sum).Sum
        }
    }
4

That works! Looks like I got too complicated, while your solution is much simpler.

Thank you.

PS. I will format data as you suggested. Thanks again.

5

And yet another approach.

$csv = @"
Path,Hard,Used
/server/IT330/share1,20,100
/server/IT330/share2,20,200
/server/IT330/share3,10,400
/server/IT330/share4,0,0
/server/IT330/share5,0,300
/server/FI150/share6,0,300
/server/FI150/share7,5,233
"@ | ConvertFrom-Csv

$grouped = $csv |
    Group-Object -Property {$_.path -replace '^.+?/|/.+$'}

$grouped | ForEach-Object {
    [PSCustomObject]@{
        Department = $_.Name
        Hard = [Linq.Enumerable]::Sum([int[]]$_.group.hard)
        Used = [Linq.Enumerable]::Sum([int[]]$_.group.used)
    }
}

Since it’s using ForEach-Object you can just pipe this directly into Export-Csv or whatever you need.

6

Thanks to everyone for the suggestions. Learned a lot.