Group column from CSV file and concatenate values of another column, and export back to CSV-CodePudding

This is a follow up to this: In PowerShell, how can I get the XML inner child to populate its parents and format it in a table?

My purpose is to grab my csv file and group by the "company" name, and I don't know where to start, I know that PS has a "Group-Object" CmdLet but it returns me an object that I don't know how to deal with.

So, using the example columns that I had before, Harvey from Specter would get from:

PName	Company	ORDERDATE	STREET1	City	ProjectName	Name	Color	OtherData
Charles	Contoso	2022-01-25	FakeStreet 123	San Francisco	Sacramento Expansion	ProductA	Lemon	OtherData
Harvey	Specter	2022-01-25	NotAFake 123	San Diego	North Dakota Expansion	ProductA	Red	OtherData
Harvey	Specter	2022-01-25	NotAFake 123	San Diego	North Dakota Expansion	ProductB	Blue	OtherData

To:

PName	Company	ORDERDATE	STREET1	City	ProjectName	Name	Color	OtherData
Charles	Contoso	2022-01-25	FakeStreet 123	San Francisco	Sacramento Expansion	ProductA	Lemon	OtherData
Harvey	Specter	2022-01-25	NotAFake 123	San Diego	North Dakota Expansion	ProductA, ProductB	Red, Blue	OtherData

Thanks and as always, appreciated

CodePudding user response：

Following your last question and assuming you already have $result stored, this is how you can update the object you already have with the help of Group-Object:

$result | Group-Object Company | ForEach-Object {
    # If the group is 1 object return it and skip next logic
    if($_.Count -eq 1) { return $_.Group }
    # Capture all objects of this group
    $thisGroup = $_.Group
    
    # We know if we are here that `$thisGroup` is an object[],
    # there is at least more than 1 object.
    # `$thisObject[0]` is the object we want to update, so,
    # `$thisObject[0].PropertyName` will enumerate ALL Values
    # of "PropertyName" and we can join them with `-join` and assign
    # the result to the 1st Object
    $thisGroup[0].Name  = $thisGroup.Name -join ', ' # Join these elements
    $thisGroup[0].Color = $thisGroup.Color -join ', '
    # Return ONLY the first object of this object[], this is the
    # object that was updated.
    $thisGroup[0]
} | Format-Table -AutoSize

Using the same XML and my previous answer to construct $result, the output from above code would be:

PName   Company ORDERDATE  STREET1        City          ProjectName            Name               Color     OtherData
-----   ------- ---------  -------        ----          -----------            ----               -----     ---------
Charles Contoso 2022-01-25 FakeStreet 123 San Francisco Sacramento Expansion   ProductA           Lemon     OtherData
Harvey  Specter 2022-01-25 NotAFake 123   San Diego     North Dakota Expansion ProductA, ProductB Red, Blue OtherData