The fact that PowerShell gives you the ability to a build tasks in many different ways, it’s sometimes intimidating. But, you’ll find the way to improve your coding as time goes. I try, the best I can, to keep things simple.
So, here’s another approach on How-To “Sum” items in a CSV file:
My Data
[sourcecode language=”powershell”]
## sbData.csv:
# Date,Item,ItemType,Amount,Company
# 12/12/11,Ben Aipa 8’10,Longboard,1500,BenAipa
# 12/12/11,Walden 9’6",Longboard,1100,Walden
# 12/13/11,Channel Island 6′,Shortboard,800,ChannelIsland
# 12/13/11,Mark Richards 6’8",Shortboard,750,Surftech
# 12/14/11,Walden 7’6",Funboard,800,Walden
# – End of CSV file –
[/sourcecode]
The request is to sum the amount of the items group by Date and ItemType. And here’s the solution:
[sourcecode language=”powershell”]
## – Working process to Sum amount in a CSV file by grouping columns:
$gdata = Import-csv sbData.csv | Group-Object -Property Date,ItemType;
## – BUild your new object with the sum items:
[Array] $newsbData += foreach($gitem in $gdata)
{
$gitem.group | Select -Unique Date,ItemType, `
@{Name = ‘SumAmount’;Expression = {(($gitem.group) | measure -Property Amount -sum).sum}}
};
## – Build back the CSV file with the Sum items
$newsbData | Export-Csv NewsbData.csv -NoTypeInformation;
ii NewsbData.csv;
[/sourcecode]
So, you can substitute the group items in ” -property ” parameter with the ones you want to be grouped on, then use the “Select -unique …” to include the grouped items you want to list. The magic happens when including the “Measure” command against the already grouped items so you can sum the ‘Amount’ field. Now, this code could become a template to sum up items when needed.
The Result:
[sourcecode language=”powershell”]
# – My CSV new Output data:
# "Date","ItemType","SumAmount"
# "12/13/11","Longboard","2600"
# "12/13/11","Shortboard","1550"
# "12/14/11","Funboard","800"
# – end of file –
[/sourcecode]
Happy PowerShelling!!