Powershell Import/Export-csv group & join cells

Question

I have a csv file which I need to group and join various cells together and have them then separated by a semi-colon in the csv layout is as such

CSV Layout

I have managed to join the cells 'price' & 'functionnumber' together and separate them with a ';' but for 'datedespatched' and 'username' which just need to display the 1 name or date the cells return with 'System.Object[]' displayed in the condensed cell, the 'referencenumber' cell appears to condense down fine. This is my current code and the output I receive from it

 Import-Csv 'C:\temp\test.csv' | Group-Object ReferenceNumber |ForEach-Object {
 
 [PsCustomObject]@{

        ReferenceNumber = $_.Name

        userName = $_.Group.userName

        DateDespatched = $_.Group.DateDespatched 

        functionnumber = $_.Group.functionnumber -join '; '

        Price = $_.Group.Price -join '; '
        
        }
 } | Export-Csv 'C:\temp\test-export.csv' -NoTypeInformation 
 

Code-output

Thank you in advance

Answer 1

When you create your custom PS object you are joining the output of functionnumber and Price but not userName and DateDespatched. The effect this has is that the $_.Group.userName becomes an array, and you're sending the array as the object to output. If you add

-join ';' 

to both username and DateDespatched it will work. I had a similar issue with this that I got some good help on in this post.

If you want to only output one name per row, you could pipe the arrays to

Select-Object -Unique 

to only get unique entries.