Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    PowerShell sort records

    Hi,

    I want to sort the data in flat text file, its not a csv.
    The sorting point starts from digit 10 block of 9 digits. Total 5000 rows. Skip first row its header.

    I tried in batch file
    Code:
    sort /+10 "c:\test\example.txt"
    But impossible to reach the completion its very very slow. How to achieve this in powershell

    111000100910044019161020140000000892200041962020100104810000000001>> upto 256
    222000322131044003161020140000000500000053548020100105211000000001>> upto 256
    333786214411044011161020140000001500000208826010100606311000000000>> upto 256
    111087652356044052161020140000000815749135335260100402611000000001>> upto 256
    RESULT:
    222000322131044003161020140000000500000053548020100105211000000001
    111087652356044052161020140000000815749135335260100402611000000001
    333786214411044011161020140000001500000208826010100606311000000000
    111000100910044019161020140000000892200041962020100104810000000001
    Last edited by foncesa; 2014-10-20 at 03:53.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Foncesa,

    Here's a script that works with the data you supplied (I supplied a header line).
    The original file is replaced but I'm sure you can change that if you desire to send it somewhere else.
    Code:
    Clear-Host
    
    $SourceFn = "G:\BEKDocs\scripts\TestSortSubstr.txt"
    
    $TempCont = Get-Content $SourceFn
    $SaveHdr = $TempCont[0]
    $TempCont[0] = "00000000000000000000000000000000000000"
    $TempCont = $TempCont | sort {$_.Substring(9,9)} 
    $TempCont[0] = $SaveHdr
    $TempCont | Set-Content $SourceFn
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,

    RG superb, Thanks its super fast.

    1 question is it possible to do sort on all the files in folder one by one, and they are all CSV files, sort on the 3rd column.
    sample : 44|2014|8736|2000|635743|847757993848|

    Thanks.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Foncesa,

    Yes, it is possible. However, I'll need more information:
    1. What is the separator for the columns?
    2. Are the text values enclosed in ""?
    3. Is there a header row?
    4. Does the sorted file get written back to the same file or a new file and if so how does it get named?


    Of course a sample file would be of great utility!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi RG,

    1.What is the separator for the columns? Pipe Delimiters (|)

    2.Are the text values enclosed in ""? No, they are not enclosed in " "

    3.Is there a header row? No, it does not have the header row

    4.Does the sorted file get written back to the same file or a new file and if so how does it get named? Will Sort the same file no need of having new file.
    Attached sample CSV file, overall.zip
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Foncesa,

    Ok here's a solution to the second question. I'm sure it can be written more efficiently using more pipelining but my brain's fried after wrangling with the fact the the Export-CSV cmdlet insists on adding the double quotes around all values and keeping the header line (which I thought would be temporary) I added in the Import-CSV to allow a column reference for sorting. Like I said it may not be pretty but it works!

    Code:
    $Headers = "A","B","C","D","E"        #One letter for each column!
    $FileLocation = "G:\BEKDocs\Scripts"  #Change to your location!
    $FileFilter = "overall*.csv"          #Change to your filename filter
    
    $FileList = Get-ChildItem $FileLocation -Filter $FileFilter 
    
    ForEach ($FSpec in $FileList) {
      #--- Load File as CSV and add Headers to allow Sorting ---
      $x = import-csv -Path $FSpec.FullName  -Delimiter '|' -Header $Headers
      $x | Sort-Object C | `
      Export-csv -Path $FSpec.FullName -Delimiter '|' -Force -NoTypeInformation
    
      # Reload File as Text to delete Headers and quotes around values
      # that were added by the CSV processing!
    
      $a = (get-content $FSpec.FullName)
      $a =  $a[1..($a.count - 1)]   #--- Get Rid of Header ---
      $Final = $a.replace('"',$null)   #--- Get Rid of "      ---
      Set-Content $FSpec.FullName $Final 
    }
    Test Data and Results:
    Code:
    Starting Data:
    
    Overall.csv
    1|Peter Schmitt|1133|1350|Brown Cty Comp
    2|Michael Gierlach|1108|1311|Independent
    3|Johnathan Van Lanen|1134|1267|West Bend HS
    4|Richard Bailey|1100|1188|Forestville Combined
    5|Tyler Johnson Schneider|1111|900|Sun Prairie Comp
    
    Overall2.csv
    1|Peter Schmitt|1109|1350|Brown Cty Comp
    2|Michael Gierlach|1108|1311|Independent
    3|Johnathan Van Lanen|1107|1267|West Bend HS
    4|Richard Bailey|1100|1105|Forestville Combined
    5|Tyler Johnson Schneider|1101|900|Sun Prairie Comp
    
    Overall3.csv
    1|Peter Schmitt|1345|1350|Brown Cty Comp
    2|Michael Gierlach|1213|1311|Independent
    3|Johnathan Van Lanen|1202|1267|West Bend HS
    4|Richard Bailey|1203|1188|Forestville Combined
    5|Tyler Johnson Schneider|1201|900|Sun Prairie Comp
    
    Results Data:
    
    Overall.csv
    4|Richard Bailey|1100|1188|Forestville Combined
    2|Michael Gierlach|1108|1311|Independent
    5|Tyler Johnson Schneider|1111|900|Sun Prairie Comp
    1|Peter Schmitt|1133|1350|Brown Cty Comp
    3|Johnathan Van Lanen|1134|1267|West Bend HS
    
    Overall2
    4|Richard Bailey|1100|1105|Forestville Combined
    5|Tyler Johnson Schneider|1101|900|Sun Prairie Comp
    3|Johnathan Van Lanen|1107|1267|West Bend HS
    2|Michael Gierlach|1108|1311|Independent
    1|Peter Schmitt|1109|1350|Brown Cty Comp
    
    Overall3.csv
    5|Tyler Johnson Schneider|1201|900|Sun Prairie Comp
    3|Johnathan Van Lanen|1202|1267|West Bend HS
    4|Richard Bailey|1203|1188|Forestville Combined
    2|Michael Gierlach|1213|1311|Independent
    1|Peter Schmitt|1345|1350|Brown Cty Comp
    HTH
    Last edited by RetiredGeek; 2014-10-22 at 16:45.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Hey Y'all,

    Here's a slightly improved version where I've gotten the CSV section of the code to work in a pipeline.
    Code:
    #Version: 1.1
    
    $Headers = "A","B","C","D","E"        #One letter for each column!
    $FileLocation = "G:\BEKDocs\Scripts"  #Change to your location!
    $FileFilter = "overall*.csv"          #Change to your filename filter
    
    $FileList = Get-ChildItem $FileLocation -Filter $FileFilter 
    
    ForEach ($FSpec in $FileList) {
      #--- Load File as CSV and add Headers to allow Sorting ---
      (import-csv -Path $FSpec.FullName  -Delimiter '|' -Header $Headers) | `
      Sort-Object C | `
      Export-csv -Path $FSpec.FullName -Delimiter '|' -Force -NoTypeInformation
    
      # Reload File as Text to delete Headers and quotes around values
      # that were added by the CSV processing!
    
      $a = (get-content $FSpec.FullName)
      $a =  $a[1..($a.count - 1)]      #--- Get Rid of Header ---
      $Final = $a.replace('"',$null)   #--- Get Rid of "      ---
      Set-Content $FSpec.FullName $Final 
    }
    I still am stymied by the Get-Content/Set-Content section. I just can't get this to work in a pipeline.headbang.gif
    Calling all PS Gurus can this be done?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Lounger
    Join Date
    Dec 2009
    Location
    Gillingham, Dorset, UK
    Posts
    30
    Thanks
    0
    Thanked 13 Times in 11 Posts
    RetiredGeek
    You could change the last three lines, following 'a = (get-content....)' with
    Code:
    $a[1..($a.count -1)] -replace '"' | Set-Content $fspec.fullname

  9. The Following 2 Users Say Thank You to Cliff.H For This Useful Post:

    foncesa (2014-10-25),RetiredGeek (2014-10-23)

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Cliff,



    Thanks works like a charm. I'm still working my way through learning PS and all its syntax variations.
    Thanks Again!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    foncesa (2014-10-25)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •