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  Ulrich Wendl 1 month, 2 weeks ago.

  • Author
    Posts
  • #96921

    Ulrich Wendl
    Participant

    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

    Vans
    Participant

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

  • #96960

    JohnM
    Participant

    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

    Olaf Soyk
    Participant

    Take a look there: link

  • #97197

    Ulrich Wendl
    Participant

    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

    Olaf Soyk
    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

    Ulrich Wendl
    Participant

    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...

You must be logged in to reply to this topic.