manintheit.org

manintheit.org


Sorting by Specific Column in PowerShell

Hello Folks,

I needed a script in Poweshell which sorts the data by specific column. I wanted to share it on my blog as I do not see much examples over the Internet.

Let’s say you have data as below and you want to sort this data by specific column. In this example I sort the data by Elapsed Time

Fields are in the sample data delimited by multiple space–in Regex terms (\s+)

Fields are; Batch Name Status Stage Batch Batch Date Start Time End Time Elapsed Time Avg. Elapsed Time

BNK/TEST001 0 A100-Application 20140325 20:01:38 20:01:38 0 0.2
BNK/TEST002 0 R050-Reporting 20140325 21:23:50 21:23:51 1 0.3
BNK/TEST003 0 D110-Start-of-Day 20140325 00:17:34 00:17:34 0 0.9
BNK/TEST004 0 D110-Start-of-Day 20140325 00:17:33 00:17:33 0 0.5
BNK/TEST005 0 S920-System-Wide 20140325 21:09:41 21:09:41 0 0.0
BNK/TEST006 0 S920-System-Wide 20140325 21:18:46 21:18:47 1 0.4
BNK/TEST007 0 S920-System-Wide 20140325 21:18:48 21:18:48 1 0.7
BNK/TEST008 0 S920-System-Wide 20140325 21:18:48 21:18:48 0 0.0
BNK/TEST009 0 S920-System-Wide 20140325 21:18:48 21:18:48 0 0.1
BNK/TEST010 0 S920-System-Wide 20140325 21:10:38 21:18:46 544 508.3

Sorting Script

Get-Content sample_data.txt | ForEach-Object {
 $Line = $_.Trim() -Split '\s+'
 New-Object -TypeName PSCustomObject -Property @{
                batchName = $Line[0]
                #status = $Line[1]
                stage = $Line[2]
                batchDate = $Line[3]
                startTime = $Line[4]
                endTime = $Line[5]
                elapsedTime = [double]$Line[6]
                avgElapsedtime = [double]$Line[7]
  }

} | Sort-Object elapsedTime -Descending | Format-Table -Property batchName,stage,batchDate,startTime,endTime,elapsedTime  -AutoSize | Out-String -Width 4096 | Out-file results.txt -Encoding default

Results

batchName   stage             batchDate startTime endTime  elapsedTime
---------   -----             --------- --------- -------  -----------
BNK/TEST010 S920-System-Wide  20140325  21:10:38  21:18:46         544
BNK/TEST007 S920-System-Wide  20140325  21:18:48  21:18:48           1
BNK/TEST006 S920-System-Wide  20140325  21:18:46  21:18:47           1
BNK/TEST002 R050-Reporting    20140325  21:23:50  21:23:51           1
BNK/TEST009 S920-System-Wide  20140325  21:18:48  21:18:48           0
BNK/TEST001 A100-Application  20140325  20:01:38  20:01:38           0
BNK/TEST008 S920-System-Wide  20140325  21:18:48  21:18:48           0
BNK/TEST003 D110-Start-of-Day 20140325  00:17:34  00:17:34           0
BNK/TEST004 D110-Start-of-Day 20140325  00:17:33  00:17:33           0
BNK/TEST005 S920-System-Wide  20140325  21:09:41  21:09:41           0

Happy Scripting 🙂



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.