none
Working with CSV files -Powershell

    Question

  • Hello all,

                 Here is a senario where am struck for past 2 days and i thought some one here can help.

    I am trying to get unique lines out of a csv file based on one particular column. Lets take a rough example , below is a sample csv i have pasted.. where i need to get only one last row from each category

    Problem Severity Category
    1 Warning Event
    2 critical Alert
    3 Warning Alert
    4 Warning Event
    5 Warning Alert


    So the end result should have the complete row of problem 4&5 as that is the last appearing of unique data. Just to add an information that my csvhas 25 columns and as big as 11MB of data. Any thoughts would be greatly appreciated.


    S.Arun Prasath HP ARDE TEAM


    Saturday, July 21, 2012 9:49 PM

Answers

  • Or:

    import-csv 'c:\temp\test.csv' | 
    group category |
    foreach {$_.group -select -last 1}


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    Monday, July 23, 2012 5:06 PM

All replies

  • If I understand your scenario, this does the job...

    $testdata=import-csv 'c:\temp\test.csv'
    $categories=$testdata | Select Category -Unique
    foreach($cat in $categories){
        $testdata | Where-Object{$_.Category -eq $cat.Category} | Select -Last 1
    }

    Result:

    Problem                                                   Severity                                                 Category                                               
    -------                                                   --------                                                 --------                                               
    4                                                         Warning                                                  Event                                                  
    5                                                         Warning                                                  Alert  


    Just a humble SysAdmin

    Saturday, July 21, 2012 11:22 PM
  • For large data files (granted, 11MB is not that large) I prefer to use the pipeline. Using a hash table is a very efficient way to get the unique data you need:

    Import-Csv 'C:\temp\test.csv' | foreach {$hash=@{}}{$hash.($_.Category)=$_}{$hash.Values}

    Regards,

    Arnoud

    Monday, July 23, 2012 4:24 PM
  • Or:

    import-csv 'c:\temp\test.csv' | 
    group category |
    foreach {$_.group -select -last 1}


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    Monday, July 23, 2012 5:06 PM
  • Thank you!

    S.Arun Prasath HP ARDE TEAM

    Wednesday, July 25, 2012 5:37 AM
  • Hello,

    I'm new on Powershell. I'm getting error when executing this script.

    import-csv 'c:\temp\test.csv' | 
    group category |
    foreach {$_.group -select -last 1}

    You must provide a value expression on the right-hand side of the '-' operator.

    I need to get last result from each of the category, but this does not work for me. Where can be a problem?

    Thanks!

    Edit: I was moved the question to the new thread.


    • Edited by techcons Tuesday, July 15, 2014 7:44 AM
    Monday, July 14, 2014 3:55 PM