I have a CSV file with headers in it like below. 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))" }

Assuming you mean the top ten aggregated FOLDER sizes, insert this before your "foreach": $a = $a | Sort-Object Size -Descending | Select-Object -First 10

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

Accepted answer

Rich Matheisen 45,906 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
Please sign in to rate this answer.
Pavan Kumar Midde 21 Reputation points

2021-12-01T09:38:40.917+00:00

Also, please help me to remove the decimal values that we receive in output.

Output should be like below:
name=Custom Metrics|Test|Rows|App|Count,value=113428
name=Custom Metrics|Test|Rows|Docs|Count,value=11274

Rich Matheisen 45,906 Reputation points

2021-12-01T16:03:25.597+00:00

Does this work?

"name=Custom Metrics|Test|Rows|"+$($q.Name)+"|Count,value="+"$(([int]$q.Size))"

Pavan Kumar Midde 21 Reputation points

2021-12-09T17:08:33.1+00:00

Thanks for the above workaround, it worked.

Need another help on to fetch the values on Hourly and Minutes basis.
For Hourly, I can get the results using below Script 1.
But when trying to get the values for Hour and Minutes there is an issue that I'm unable to remove the Seconds from 'TIME' header.
This is because, my script runs for every minute and want to fetch the values for every minute after comparing the Date and Time with System Date and Time.

So Please help me in removing the Seconds from TIME header and fetch the values after comparing the Hours and Minutes with System Hours and Minutes.
Even written a Script 2 for this , but there is an issue in the script.
Issue here is, multiple outputs were printed as there are different seconds for same Hour and Mins.

Script 1 : Hourly Basis

$sfn = Import-Csv -Path 'C:\Apptier\Docs\Test.csv'
$sfn2 = $sfn|?{$.Date -ne '1970-01-01'} | select 'Hour','FileSize(KB)'
$a = $sfn2 | Group-Object Hour | Select-Object count, Name, @{ n='Size' ; e={ ($.Group | Measure-Object 'FileSize(KB)' -Sum).Sum}}
foreach($q in $a)
{
if($q.Name -eq (Get-Date -Format "HH"))
{
"name=Custom Metrics|Test|Rows|"+$($q.Name)+"|Count,value="+"$(([int]$q.Size))"
}
}

Pavan Kumar Midde 21 Reputation points

2021-12-09T18:41:46.05+00:00

Script2 : Hour and Minutes basis Number of Files

$sfn = Import-Csv -Path 'C:\Apptier\Docs\Test.csv'
$sfn2 = $sfn|?{$.SrcArrivalDate -ne '1970-01-01'} | select 'Time','FileName'
$a = $sfn2 | Group-Object Time | Select-Object count, Name, @{ n='Size' ; e={ ($.Group | Measure-Object 'FileName').Count}}
foreach($q in $a)
{
$k = $q.Name -replace ".{3}$"
if($k -eq (Get-Date -Format "HH:mm"))
{
"name=Custom Metrics|Test|Rows|"+$($k)+"|Count,value="+"$(([int]$q.Size))"
}
}

Note: There are four row values under Time Column
23:59:42
23:59:16
23:59:51
23:59:53

OutPut after Script Execution

name=Custom Metrics|Test|Rows|23:59|Count,value=1
name=Custom Metrics|Test|Rows|23:59|Count,value=1
name=Custom Metrics|Test|Rows|23:59|Count,value=1
name=Custom Metrics|Test|Rows|23:59|Count,value=1

Expected Output

name=Custom Metrics|Test|Rows|23:59|Count,value=4

Rich Matheisen 45,906 Reputation points

2021-12-09T20:58:41.54+00:00

Change the value of "Time" in the 2nd line of your script's "select-object" before you group the values.
Sign in to comment

Share via

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

Script 1 : Hourly Basis

Script2 : Hour and Minutes basis Number of Files

OutPut after Script Execution

Expected Output

0 additional answers