sort combination of property and unique, for csv files without column names

Welcome Forums General PowerShell Q&A sort combination of property and unique, for csv files without column names

This topic contains 6 replies, has 4 voices, and was last updated by

 
Participant
7 months, 1 week ago.

  • Author
    Posts
  • #96921

    Participant
    Points: 0
    Rank: Member

    Hello,
    a csv file without attribute names is to be sorted with a primary key, and duplicates shall be removed.
    In the following example the first column is the primary key (in another file it is the fourth column),
    so in this example the fourth line (with ...secondname) is to be deleted:
    ...
    ab123; name; first-name; ...
    ab321; name2; first-name2; ...
    ab123; name ; firstname-secondname; ...
    ab456; ...

    Tried combinations of ...sort -property ... | sort -unique ... but I have too less experience with powershell.
    Thanks in advance!

  • #96950

    Participant
    Points: 0
    Rank: Member

    to hide headers | format-table -hideheaders format-table -HideTableHeaders

  • #96960

    Participant
    Points: 9
    Rank: Member

    I think you need to import your CSV with the -Header option

    #MyCSV file looks like this:
    #one,100,won
    #two,200,too
    #three,300,tree
    
    $header ="first","second","third"
    $file = import-csv -path .\MyCSV.csv -header $header
    
    #$file will look like this:first second third
    $file
    ----- ------ -----
    one   100    won
    two   200    too
    three 300    tree
    
    
    #then you can sort the file contents on the named column
    $file | Sort -property third
    
    #your output will look  like this:
    first second third
    ----- ------ -----
    two   200    too
    three 300    tree
    one   100    won
    
    

    Good luck

  • #96963

    Participant
    Points: 140
    Helping Hand
    Rank: Participant

    Take a look there: link

  • #97197

    Participant
    Points: 0
    Rank: Member

    Thank you very much!
    But I misunderstand the cmdlet principle it seems. Whether I include the headers in the sqlcmd already,
    or with the import, or with an extra header file like the following – the next step "sort"
    returns one line only, the first data row, without header, and without error or warning.

    powershell -command "(get-content accounts_?.out | Add-Content accounts_unsorted.out)"

    powershell -command "get-content accounts_header.csv, accounts_unsorted.out | sort Key -unique | Set-Content accounts_%TIMESTAMP%.csv"
    or
    powershell -command "(get-content accounts_header.csv, accounts_unsorted.out | sort Key -unique) | Set-Content accounts_%TIMESTAMP%.csv"

    Any help welcome!
    Best regards!

  • #97203

    Participant
    Points: 140
    Helping Hand
    Rank: Participant

    I don't know if I understood you right but if the question is how to properly import a CSV file without headers you could have just carefully reviewed the help for the cmdlet Import-CSV. Especially the example 5 shows how to do that. So it could be something like this.

    Import-Csv -Path 'csvFile.csv' -Delimiter ',' -Header 'Header1','Header2' ...  | 
        Sort-Object -Property Header2 -Unique

    BTW: As your name sounds a little bit German to me, there is a German Powershell forum as well. Sometimes it's easier to ask something you your native language. 😉 Or are you uwmuc? 😉

  • #98121

    Participant
    Points: 0
    Rank: Member

    Many thanks for the answers, and sorry for my poor English ;^)
    Indeed I consulted the German PS Forum too (as uwmuc)
    If I only had the time I'd search&read THE PS book...

The topic ‘sort combination of property and unique, for csv files without column names’ is closed to new replies.