Powershell script to group by and count the total values for a given cloumn/s in a CSV file

Pamidd 21 Reputation points
2021-11-30T07:03:37.83+00:00

I have a CSV file with headers in it like below.

153597-image.png

Now, I would like to sum the values in Row 'FileSize(KB)' by grouping the values in 'Folder', with few conditions as well.

Condition 1: Would like to retrieve 'Folder' Name and FileSize(KB) total count when Date is not equal to or not like '1970'.
Output should be like below.

name=Custom Metrics|Test|Rows|App|Count,value=113428.0244
name=Custom Metrics|Test|Rows|Docs|Count,value=11274.086

I have created below code and everything is working fine.
All I need is to select only top 10 FileSize(KB) in descending order.

$sfn = Import-Csv -Path 'C:\Apptier\Docs\Test.csv'
$sfn2 = $sfn|?{$.Date -ne '1/1/1970'} | select 'Folder','FileSize(KB)'
$a = $sfn2 | Group-Object Folder | Select-Object count, Name, @{ n='Size' ; e={ ($
.Group | Measure-Object 'FileSize(KB)' -Sum).Sum}}
$b = $a | Get-Member -type NoteProperty
foreach($q in $a)
{
"name=Custom Metrics|Test|Rows|"+$($q.Name)+"|Count,value="+"$(($q.Size))"
}

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,270 questions
{count} votes

Accepted answer
  1. Rich Matheisen 43,846 Reputation points
    2021-11-30T15:52:36.023+00:00

    Assuming you mean the top ten aggregated FOLDER sizes, insert this before your "foreach":

    $a = $a | Sort-Object Size -Descending | Select-Object -First 10
    

0 additional answers

Sort by: Most helpful