CODEWITHSUNDEEP
listpowershellcsvgroup-by
francisco.l
francisco.l

Reputation: 336

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

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?

And it's related to this question:
Group column from CSV file and concatenate values of another column
and this question:
https://superuser.com/questions/453041/grouping-labels-and-concatenating-their-text-values-like-a-pivot-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

Upvotes: 1

Views: 152

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 59781

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

Upvotes: 3

Related Questions