Powershell sorting columns

user_16643304 0
Viewed 59

I was able to sort the column and remove duplicates using -Unique

Import-Csv -Path $csvPath -Header $h | sort id -Unique

My problem is that I can't remove a specific row using the status property

ID Name Status
1 John Current
1 John Future
2 Mary Future
2 Mary Notice
3 Paul Future

I want the rows to be unique by status order.

  1. Current
  2. Notice
  3. Future

Example: If there's no Current but have Notice and Future, Notice will be selected.

ID Name Status
1 John Current
2 Mary Notice
3 Paul Future

Would you please let me know what I need to add on my script?

powershell sql
1 Answers

Thanks @mklement0 for the more clever approach (as always hehe):

Import-Csv -Path $csvPath -Header $h |
Sort-Object { @{ Current = 0; Notice = 1; Future = 2}[$_.Status] } |
Sort-Object ID -Unique

Try with this:

Import-Csv -Path $csvPath -Header $h |
Sort-Object { $_.Status -eq 'Current' }, { $_.Status -eq 'Notice' } -Descending |
Sort-Object ID -Unique


Assuming the sort priority is Current > Notice > Future, I think this should work, though I'm not sure if it's possible to do it on one go, as you can see I'm pipping Sort-Object to Sort-Object again. Would love to know if it's possible to sort and get unique on one go.

$csv = @'
ID  Name    Status
1   John    Current
1   John    Future
2   Mary    Future
2   Mary    Notice
3   Paul    Future
4   TestUser    Notice
4   TestUser    Future
4   TestUser    Current
5   TestUser2   Notice
5   TestUser2   Current
'@ -replace ' +',',' | ConvertFrom-Csv

# Use this (much better):
$csv | Sort-Object { @{ Current = 0; Notice = 1; Future = 2}[$_.Status] } |
Sort-Object ID -Unique

$csv | Sort-Object { $_.Status -eq 'Current' },
                   { $_.Status -eq 'Notice' } -Descending |
       Sort-Object ID -Unique

This results in:

ID Name      Status 
-- ----      ------ 
1  John      Current
2  Mary      Notice 
3  Paul      Future 
4  TestUser  Current
5  TestUser2 Current
user_153395440
Related