Powershell sorting columns

Question

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.

Current
Notice
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?

If the sort order is Current > Notice > Future then why you're showing us Mary > Future when we can see that she has Notice from your input. That's not clear. — Santiago Squarzon
– Santiago Squarzon, Commented Aug 11, 2021 at 17:59

Santiago Squarzon · Accepted Answer · 2021-08-11 19:47:02Z

4

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

edited Aug 11, 2021 at 19:47

answered Aug 11, 2021 at 18:13

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sage Pourpre Over a year ago

FYI, you can replace your -replace by importing from CSV and specifying tab delimiter directly like this: ` ConvertFrom-Csv -Delimiter "t"

Santiago Squarzon Over a year ago

@SagePourpre yup, I just copy pasted OP's table to my code and added those test users hence why I'm using the replace there. But you're totally right if the CSV was export like that.

mklement0 Over a year ago

Kudos for the clever chaining of the Sort-Object calls. Note that you could simplify to $csv | Sort-Object { @{ Current = 0; Notice = 1; Future = 2}[$_.Status] } | Sort-Object ID -Unique

Santiago Squarzon Over a year ago

@mklement0 I knew there was a more clever way of doing it, you're always surprising me!

Collectives™ on Stack Overflow

Powershell sorting columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related