Powershell Grouping of csv import file

Question

Hopefully I'm just missing something that is simple...

I have a csv file similar to this :

Employee ID, Entry Date, Product Code,Amount Due
0001,20/11/2017,A001,10
0001,20/11/2017,Q003,13
0001,20/11/2017,H001,8
0002,20/11/2017,P003,12
0002,20/11/2017,A001,7

and what I want as an output is similar to this :

0001;<some header text>;200171120
A001;10
Q003;13
H001;8
0002;<some header text>;200171120
P003;12
A001;7

So that each detail section is grouped by the Employee ID it relates to

I have tried piping the group-object ("Employee ID") after using an import-csv ... but I can't get it to work correctly

Any help much appreciated!

Answer 1

I think this does what you need:

$CSV | Group-Object 'Employee ID' | ForEach-Object {

    $EntryDate = (Get-Date ($_.Group.'Entry Date' | Select -First 1) -F 'yyyyMMdd')

    "{0};<some header text>;{1}" -f $_.Name, $EntryDate

    ForEach ($Item in $_.Group) {
        "{0},{1}" -f $Item.'Product Code',$Item.'Amount Due'
    }
}

Explanation:

• Uses Group-Object to group the results by Employee ID and then ForEach-Object to iterate through the collection.
• Gets the Entry date from the first entry in the group, converts it to a date object with Get-Date and then formats it as year/month/day (this part assumes you don't care if there are other/differing dates in the collection).
• Outputs the header string you wanted, using {0},{1} as placeholders for the variables passed via -f (there are multiple ways to do this but this seemed tidiest for your scenario).
• Uses ForEach to iterate through the Group property, which contains the grouped items. Again using string replacement to output the product code and amount due fields in the format you desired.