1

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!

Improve this question
2
  • 1
    You mention that you've tried using group-object but don't show the code you've tried, it's best to show your code (even if it doesn't work) so that we know you've at least tried to solve this issue and aren't just expecting us to write all you code for you. Commented Nov 23, 2017 at 15:20
  • I understand - I did originally cut and paste my several attempts, but removed as it just confused the post a little (I didn't want elements of my code to be corrected if I was ultimately doing it the wrong way!) Commented Nov 23, 2017 at 16:31

1 Answer 1

Reset to default
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.
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thank you very much Mark - I'm going to try now (also - much appreciate the additional explanation!)
Hi Mark. I've tried this, and PowerShell is prompting me for Process[0]. I've added in a write-host $file to check it actually has the file in question, but can't seem to get past this ..... I'm probably missing something very obvious here
Did you maybe put the { after the ForEach-Object on a new line? Because you can't do that with ForEach-Object.
(bit before specifies the $files= get-childitem <myinputdir and file mask>) $csvfile=import-csv $file $csvfile | group-object "Report Submit Date" | ForEach-Object { $EntryDate = (Get-Date ($_.Group.'Report Submit Date' | Select -First 1) -F 'yyyyMMdd') write-host "Entry Date : " $EntryDate "{0};<some header text>;{1}" -f $_.Name, $EntryDate ForEach ($Item in $_.Group) { "{0},{1}" -f $Item.'Employee ID',$Item.'Amount Due' } }
Just in case this assists anyone - my typing must have been poor : ForEach ($Item in $_.Group ) { if ($item.'Journal Account Code' -ne '') { "L;5;;0;0;{2},{0},{1}" -f $Item.'Employee ID',$Item.'Report Entry Description', $item.'Journal Account Code' } } - This seemed to work
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.