Powershell: Sorting of a text file having unevenly spaced columns

Question

I need to sort a text file which has uneven width and spacing,with column 5 being descending, and column 6 and 7 as ascending, with sorted file in same format. Column 7 contains alphanumeric character.

GSASS   21321      5          10.000    Q   236333 AB5   4IW332 1111 2/24/2015
DSASS   53155      111100     1.000     B   237140 AB5   4IW332 3223 2/24/2015
GAA     43453      111190     2.000     B   237140 AB1   4IW332 2222 2/24/2015
AASAD   23173      111191     1.000     B   237140 AB11  4IW332 2222 2/24/2015
RASS    23173      2          4.000     Q   235445 AB5   4IW332 1114 2/24/2015

I know about sort-object and split cmdlets, but i am unable to end with required result. The nearby result i could get is from below command:

get-content C:/filename.txt| For each {"$(($_ -split '\s+',10)[0..9])"}|Sort-object {$_.split(" ")[+4,+5,+6]} > newfile.txt

The main issue with above one it removes the spacing in order to sort, and I need sorted file in same format. And column 7 is aplhanumeric, so it sort like AB1, AB11 over AB5.

Below are the stack-overflow links I could find useful: Extracting columns from text file using PowerShell PowerShell: How do I sort a text file by column? Powershell ,Read from a txt file and Format Data( remove lines, remove blank spaces in between)

Please can you give the initial text file in a correct format ? — JPBlanc, Jul 01 '15 at 03:58
I still need to earn 10 points in order to post image, i couldn't figure any other way to share file. Stackoverflow removes the spacing if i put text manually. Could you suggest any other way.Thanks. — Bawa, Jul 01 '15 at 04:02
The text file is at http://pastebin.com/Tk5JhKhM (I know this is not a good practice to direct to third party website but I can't share my question clearly otherwise). — Bawa, Jul 01 '15 at 04:19
For sorting alphanumeric data take a look at this [answer](http://stackoverflow.com/a/5429048/323582). — Roman Kuzmin, Jul 01 '15 at 05:13

Frode F. · Accepted Answer · 2015-07-01T06:13:45.697

Try something like this to extract the required columns and sort. I don't think it's possible to combine ascending and descending without multiple Expressions, so I replaced -split with a common regex.

$data = @"
GSASS   21321      5          10.000    Q   236333 AB5   4IW332 1111 2/24/2015
DSASS   53155      111100     1.000     B   237140 AB5   4IW332 3223 2/24/2015
GAA     43453      111190     2.000     B   237140 AB1   4IW332 2222 2/24/2015
AASAD   23173      111191     1.000     B   237140 AB11  4IW332 2222 2/24/2015
RASS    23173      2          4.000     Q   235445 AB5   4IW332 1114 2/24/2015
"@ -split [environment]::NewLine

$regex = '^(?:[^\s]+?[\s]+){4}([\w+]*?)\s+?(\w+?)\s+?(\w+?)(\d+)'

$data | Sort-Object @{expression={ if($_ -match $regex) { $Matches[1] } };Descending=$true},
@{expression={ if($_ -match $regex) { [int]$Matches[2] } };Ascending=$true},
@{expression={ if($_ -match $regex) { $Matches[3] } };Ascending=$true},
@{expression={ if($_ -match $regex) { [int]$Matches[4] } };Ascending=$true}

Output:

RASS    23173      2          4.000     Q   235445 AB5   4IW332 1114 2/24/2015
GSASS   21321      5          10.000    Q   236333 AB5   4IW332 1111 2/24/2015
GAA     43453      111190     2.000     B   237140 AB1   4IW332 2222 2/24/2015
DSASS   53155      111100     1.000     B   237140 AB5   4IW332 3223 2/24/2015
AASAD   23173      111191     1.000     B   237140 AB11  4IW332 2222 2/24/2015

It do work,but it require data to be entered, can't it read directly from a file. Trying to work on it using this.. — Bawa, Jul 01 '15 at 12:42
Instead of data variable gave the content of file to sort object , it worked..Thanks — Bawa, Jul 01 '15 at 16:24
Yes. `$data` in this sample is an array of strings, just like the result of `Get-Content` would be, so simply remove the sample data (everything before `$regex =` and use ` Get-Content ... | Sort-Object`. Remember to mark this as answer using the checkbox on the left side if you consider it to be the solution to your question. — Frode F., Jul 02 '15 at 05:39

score 1 · Answer 2 · answered Jul 01 '15 at 07:09

Another solution, maybe easier to read (?). You can use a specific format to output the result of Format-Table in the way you want:

$content = Get-Content "d:\temp\test.txt"
$table = @()

$content | %{
    $line = $_

    $values = $line -split '\s+'

    $index = 1
    $row = New-Object Object
    $values | %{
         $row | Add-Member -MemberType NoteProperty -Name "col$($index)" -Value $_
         $index++
    }

    $table += $row
}

$table | Format-Table

$prop1 = @{Expression='col5'; Descending=$true }
$prop2 = @{Expression='col6'; Ascending=$true }
$prop3 = @{Expression={[regex]::Replace($values[6], '\d+',{$args[0].Value.Padleft(20)})}; Ascending=$true }

$table | Sort-Object $prop1, $prop2, $prop3 | Format-Table

column 5, 6 are right, but column 7 not correct, order is coming like AB5, AB1 AB11 ... — Bawa, Jul 01 '15 at 13:06
Also column header is adding as an extra, with modified spacing. The file need to read further so can't change format — Bawa, Jul 01 '15 at 13:38

score -1 · Answer 3 · answered Jul 02 '15 at 02:02

Another solution. First, I define the variables:

$sb1={(${global:#$%}=($_-split'\s+'))[4]}
$sb2={${#$%}[5]}
$sb3={${#$%}[6]-replace'(\D)(\d)',"`$1$(' '*(16-${#$%}[6].length))`$2"}

Then, I determine the output, using this pipeline:

$lines|sort @{e=$sb1;d=$true},{&$sb2},{&$sb3},{rv '#$%' -sc 1}

The output is found to be this:

RAXS    23173      2          4.000     QAA   235445 AB3   4IW332 1114 2/24/2015
GSAXS   21321      5          10.000    QAA   236333 AB5   4IW332 1111 2/24/2015
GSAXS   21321      5          10.000    QAA   236333 BB0   4IW332 1111 2/24/2015
AXSAD   23173      111191     1.000     BAA   237140 AA9   4IW332 2222 2/24/2015
GAA     43453      111190     2.000     BAA   237140 AB1   4IW332 2222 2/24/2015
DSAXS   53155      111100     1.000     BAA   237140 AB5   4IW332 3223 2/24/2015
AASAD   23173      111191     1.000     BAA   237140 AB30  4IW332 2222 2/24/2015
AASAD   23173      111191     1.000     BAA   237140 AB111 4IW332 2222 2/24/2015

where $lines are defined this way:

$lines=@'
GSAXS   21321      5          10.000    QAA   236333 BB0   4IW332 1111 2/24/2015
AXSAD   23173      111191     1.000     BAA   237140 AA9   4IW332 2222 2/24/2015
AASAD   23173      111191     1.000     BAA   237140 AB111 4IW332 2222 2/24/2015
AASAD   23173      111191     1.000     BAA   237140 AB30  4IW332 2222 2/24/2015
GSAXS   21321      5          10.000    QAA   236333 AB5   4IW332 1111 2/24/2015
DSAXS   53155      111100     1.000     BAA   237140 AB5   4IW332 3223 2/24/2015
GAA     43453      111190     2.000     BAA   237140 AB1   4IW332 2222 2/24/2015
RAXS    23173      2          4.000     QAA   235445 AB3   4IW332 1114 2/24/2015
'@-split"`r`n"

Powershell: Sorting of a text file having unevenly spaced columns

3 Answers3