How can I sort by column in PowerShell?

13,574

Solution 1

Here's one potential solution:

Get-Content yourfile.txt | ForEach-Object {
    $Line = $_.Trim() -Split '\s+'

    New-Object -TypeName PSCustomObject -Property @{
        FirstName = $Line[0]
        LastName = $Line[1]
        FirstValue = [int]$Line[2]
        SecondValue = [int]$Line[3]
    }

} | Sort-Object FirstValue | Format-Table

This uses Get-Content to load your file, then uses a ForEach-Object loop to iterate through each line of that file (where the line is represented by the automatic variable $_).

It uses the .Trim() method on the line to remove the trailing whitespaces, then splits the line using the RegEx string \s+ which represents one or more spaces.

After this $Line is now an array with each element excluding the spaces, so we create a custom PowerShell object where we give each item a name and access it's corresponding value from $Line via the Array index operator [0]..[1] etc. We cast the value columns as [int] so that they will be sorted later correctly (vs it treating them like strings).

Now that we've output a PowerShell object, we can simply pipe out result to Sort-Object with the FirstValue property and then send that to Format-Table to get a table result.

Solution 2

You're almost there already. The reason why your code doesn't work the way you expect is that the Split() method splits the string at each space, so you're getting a lot of empty columns. What you actually want is to split at consecutive whitespace. To do the latter use the -split operator:

Get-Content namef | Sort-Object { [double]($_ -split '\s+')[-2] } -Descending

Solution 3

So you already have 2 answers on how to get the result, I am adding one on how to send the email, which was part of your original requirement. You edited the question removing it, but I already did most of the answer, so I am posting it regardless.

Use powershell's Send-MailMessage which comes standard on a windows system by running it in a batchfile:

NOTE!! It must be powershell 2.0 or later.

powershell Send-MailMessage 
           -From "[email protected]"
           -To "[email protected]"
           -Subject "Test email"
           -Body "This is a test"
           -SmtpServer Some_exhange_server_name

I broke down the above text using newlines for readability, but it should be a single line as per below.

Send-MailMessage -From "[email protected]" -To "[email protected]" -Subject "Test email" -Body "This is a test" -SmtpServer some_exhange_Server_name

All you have to do still is to replace the body text This is a test with the variables from the relevant solution you choose and from the other answers.

Share:
13,574
Hoda Fakharzadeh
Author by

Hoda Fakharzadeh

Updated on June 13, 2022

Comments

  • Hoda Fakharzadeh
    Hoda Fakharzadeh almost 2 years

    how can I sort a text file by 3rd column in PowerShell? That is part of the file:

        reza             zabihi          12     1
        maryam           joraee          17     2
        leyla            ahmadi          13     2
        farzin           farahbakhsh     16     1
        keyvan           maleki          8      1
        kaveh            ahangar         18     1
        nooshafarin      bakhtiari       13     2
        roya             afrashteh       13     2
        asghar           nazemi          18     1
        jaleh            nooruzi         16     2
        ali              samadi          6      1
        mohsen           adibi           3      1
        firooz           karimi          7      1
        mostafa          rostamian       18     1
        jafar            omidi           6      1
        shima            zagrosi         12     2
        somayyeh         modaberi        16     2
        shahram          zamani          16     1
        sayeh            rahmati         3      2
        shirin           rahjoo          12     2
    

    I tried

    Get-Content namef | Sort-Object { [double]$_.Split()[-2] } -Descending
    
    • Gerhard
      Gerhard about 6 years
      why does this seem like an assignment?
    • Gerhard
      Gerhard about 6 years
      anyhow, your best bet will be to sort by 3rd column using powershell and mail it using powershell. You however are not saying what the file type is.
    • Hoda Fakharzadeh
      Hoda Fakharzadeh about 6 years
      I want to sort it by 3rd column. can u help?
    • Benas
      Benas about 6 years
  • Hoda Fakharzadeh
    Hoda Fakharzadeh about 6 years
    Thank u very much! it was a great help!
  • lit
    lit about 6 years
    Using multiple lines is -much- more readable. Just use a backtick to indicate line continuation.