Grouping CSV data

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Robbie Courtney Robbie Courtney 1 year, 11 months ago.

  • Author
    Posts
  • #28525
    Profile photo of beetleman08
    beetleman08
    Participant

    I was wondering if I could get some help with grouping data in a CSV file.

    For example:

    Number Date Amount
    ———– ——- ————

    1 08/01/2015 $1.00
    1 08/01/2015 $2.00
    1 08/03/2015 $3.00
    2 08/01/2015 $1.00
    2 08/03/2015 $2.00
    2 08/03/2015 $3.00
    3 08/01/2015 $1.00
    3 08/01/2015 $6.00

    Is there a way to get the following results:

    Number 1, spent $3.00 on 8/1/2015 and $3.00 on 8/3/2015
    Number 2, spent $1.00 on 8/1/2015 and $5.00 on 8/3/2015
    Number 3, spent $7.00 on 8/1/2015

    Basically I'm looking to group by name, then figure out how much was spent per day.

    Any help would be appreciated.

    Thanks!

  • #28528
    Profile photo of Robbie Courtney
    Robbie Courtney
    Participant

    This is my stab at your question, there is probably a more eloquent way to accomplish this but this is what I have.

    Now I'm not quite sure what you are trying to accomplish, if you are you just wanting to display text on the screen then this will work, but if you want to be able to manipulate this data a different approach should be used because I am just outputting this as an array of strings.

    Code

    $stringArray = @()
    $csv = Import-Csv c:\file.csv # where ever your cvs file is stored
    $PrimaryKey = $csv | select -ExpandProperty number -Unique
    
    foreach($key in $PrimaryKey) {
       $string = "Number $key, spent "
       $keyData = $csv | Where Number -EQ $key
       $keyData | foreach{$string +="$($_.Amount) on $($_.Date) "}
       $stringArray += $string
    }
    
    $stringArray
    

    Output

    Number 1, spent $1.00 on 08/01/2015 $2.00 on 08/01/2015 $3.00 on 08/03/2015 
    Number 2, spent $1.00 on 08/01/2015 $2.00 on 08/03/2015 $3.00 on 08/03/2015 
    Number 3, spent $1.00 on 08/01/2015 $6.00 on 08/01/2015
    

    Also there is a Cmdlet called Group-Object, that would probably be more useful if you wanted to further manipulate the data, it just doesn't output it like you stated you wanted it.

    Example

    $csv | Group-Object Number
    

You must be logged in to reply to this topic.