Grouping CSV data

PowerShell Help
1

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!

2

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