Author Posts

August 19, 2018 at 2:57 pm

Is there a way to sum (i.e., total) the values of a specific property by "sub-groups" of objects in a CSV file?  An example is best.  The CSV file looks like the ff:

ID,   Amount

10,   110

10,   40

12 ,  50

12,   40

12 ,  10

...

I'd like to create a file that looks like:

10,150

12,100

...

I did it by looping through each object with Foreach ... using the $_ cursor and additional variables to keep track of the changes.

I also thought Measure-object's -SUM could do the job, but it seems it can only sum the CSV file's Amount property.  I'm looking for a "Powershell way" to use existing cmdlets and pipeline processing.

Would be grateful for any hints, suggestions, tips.

August 19, 2018 at 3:23 pm

Group-Object is your hero here.

#CSV data
id value
— —–
10 110
10 40
12 50
12 40
12 10

$Data = Import-Csv -Path c:\Temp\Test.csv
$Data | Group-Object -Property id | ForEach-Object -Process {
    $Sum = $_.group | measure -sum -Property Value
    [pscustomobject]@{id=$_.Name ; value = $Sum.sum}
   
    #if the PowerShell version is 2.0, then below code has to be used instead of [PSCustomObject] way
    New-Object -TypeName PSObject -Property @{id=$_.Name ; value = $Sum.sum}
}

August 19, 2018 at 4:35 pm

Many thanks, Mr kvprasoon.  Much appreciated!

August 19, 2018 at 5:23 pm

Hello Mr kvprasoon,

I touched up the code you recommended and got the message:

ForEach-Object : Missing an argument for parameter 'Process'. Specify a parameter of type
'System.Management.Automation.ScriptBlock[]' and try again.

It seems there is a syntax error somewhere in the line:  Foreach -process.

Also, I'm not clear on what the line

[PSCustomobject]@{id=$_.Name; value = $Sum.sum}

does.  Looks like a hahs table, but am unclear on what it does.

Thanking you in advance.

August 19, 2018 at 5:30 pm

Seems like some issue occurred when you copied directly from the web. Process is a parameter for ForEach-Object cmdlet which accepts scriptblock.

About PSCustomObject, here is a great blog post about it by Kevin

August 19, 2018 at 7:21 pm

Very well, Mr. kvprasoon.

I retyped the code just to be sure, and it works beautifully.

Thanks also for the article about [PSCustomObject], it was very informative and useful to my learning Powershell.

Many thanks.