Find Matching Column and concatenated into one cell while keeping other data

[tn647883]

Hi,

I have been trying to figure this issue out but unable to find the answer.

I have a csv file name for example with data

Category	Description	Number	Fresh
Fruit	Grape	3	Yes
Vegetable	Lettuce	3	Yes
Fruit	Grape	3	Yes
Fruit	Apple	3	Yes
Vegetable	Tomato	3	No

I want to group by Fruit and join the Description into one cell. While if cell Number and Fresh match to keep that row if not create new row. and if there is duplicate in Description field ignore.

I came up with this so far

$list = Import-Csv -Path grocery.csv
$Groups= $list | Group-Object -Property Category

$OutCSV = ForEach ($Group In $Groups) {
[PsCustomObject] @{
‘Category’ = $Group.Name
‘Description’ = $Group.Group.Description -join ‘, ‘

}
}

Expecting output

Category	Description	Number	Fresh
Fruit	Grape, Apple	3	Yes
Vegetable	Lettuce	3	Yes
Vegetable	Tomato	3	No

Please help.

• This topic was modified 1 week, 6 days ago by tn647883.

[AdminOfThings45 Participant Topics: 0 Replies: 81 Points: 362Rank: Contributor]

Please post an example of the final output you are expecting.

[ImAhNoBoDy]

PowerShell

$csv = @' Category,Description,Number,Fresh, Fruit,Grape,3,Yes, Vegetable,Lettuce,3,Yes, Fruit,Grape,3,Yes, Fruit,Apple,3,Yes, Vegetable,Tomato,3,No '@ | ConvertFrom-Csv

1 2 3 4 5 6 7 8

$csv = @' Category,Description,Number,Fresh, Fruit,Grape,3,Yes, Vegetable,Lettuce,3,Yes, Fruit,Grape,3,Yes, Fruit,Apple,3,Yes, Vegetable,Tomato,3,No '@ | ConvertFrom-Csv

PowerShell

$results=@() $csvSorted=$csv | Sort-Object -Property * -Unique $csvSorted | Select-Object -ExpandProperty Category | ForEach-Object { $csvCategory=$_ If ($csvCategoryMatch=$csvSorted | Where-Object -Property Category -eq $csvCategory) { $csvCategoryMatch | ForEach-Object { $csvCategoryCompare=$_ If ($CNFMatch=$results | Where-Object {$_.Category -eq $csvCategoryCompare.Category -and $_.Number -eq $csvCategoryCompare.Number -and $_.Fresh -eq $csvCategoryCompare.Fresh}) { If (-not($DescriptionMatch=$CNFMatch | Where-Object -Property Description -eq $csvCategoryCompare.Description)) { If (-not ($CNFMatch.Description.GetType().BaseType.Name -eq "Array")) { $CNFMatch.Description=@( $CNFMatch.Description.split() ) $CNFMatch.Description+=$csvCategoryCompare.Description } } } else { $results+=$csvCategoryCompare } } } } $results

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

$results=@() $csvSorted=$csv | Sort-Object -Property * -Unique $csvSorted | Select-Object -ExpandProperty Category | ForEach-Object { $csvCategory=$_ If ($csvCategoryMatch=$csvSorted | Where-Object -Property Category -eq $csvCategory) { $csvCategoryMatch | ForEach-Object { $csvCategoryCompare=$_ If ($CNFMatch=$results | Where-Object {$_.Category -eq $csvCategoryCompare.Category -and $_.Number -eq $csvCategoryCompare.Number -and $_.Fresh -eq $csvCategoryCompare.Fresh}) { If (-not($DescriptionMatch=$CNFMatch | Where-Object -Property Description -eq $csvCategoryCompare.Description)) { If (-not ($CNFMatch.Description.GetType().BaseType.Name -eq "Array")) { $CNFMatch.Description=@( $CNFMatch.Description.split() ) $CNFMatch.Description+=$csvCategoryCompare.Description } } } else { $results+=$csvCategoryCompare } } } } $results

• This reply was modified 1 week, 6 days ago by ImAhNoBoDy.
• This reply was modified 1 week, 2 days ago by ImAhNoBoDy.
• This reply was modified 1 week, 2 days ago by ImAhNoBoDy.
• This reply was modified 1 week, 2 days ago by grokkit.

[Doug Maurer Participant Topics: 8 Replies: 568 Points: 2,170Rank: Community Hero]

Here is one way to achieve your desired result.

PowerShell

$csv = @' Category,Description,Number,Fresh, Fruit,Grape,3,Yes, Vegetable,Lettuce,3,Yes, Fruit,Grape,3,Yes, Fruit,Apple,3,Yes, Vegetable,Tomato,3,No '@ | ConvertFrom-Csv $csv | foreach -Begin{ $result = @() function Add-Line($newline){ [PSCustomObject]@{ 'Category' = $newline.category 'Description' = $newline.description 'Number' = $newline.number 'Fresh' = $newline.fresh } } } -process { if($_.category -in $result.category) { foreach ($line in $result | where category -eq $_.category) { if($line.number -eq $_.number -and $line.fresh -eq $_.fresh) { if($line.description -notmatch $_.description) { $line.description = $line.description,$_.description -join ',' } } else { $result += Add-Line $_ } } } else { $result += Add-Line $_ } } -End {$result}

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

$csv = @' Category,Description,Number,Fresh, Fruit,Grape,3,Yes, Vegetable,Lettuce,3,Yes, Fruit,Grape,3,Yes, Fruit,Apple,3,Yes, Vegetable,Tomato,3,No '@ | ConvertFrom-Csv   $csv | foreach -Begin{     $result = @()     function Add-Line($newline){         [PSCustomObject]@{             'Category' = $newline.category             'Description' = $newline.description             'Number' = $newline.number             'Fresh' = $newline.fresh         }     } } -process {     if($_.category -in $result.category)     {         foreach ($line in $result | where category -eq $_.category)         {             if($line.number -eq $_.number -and $line.fresh -eq $_.fresh)             {                 if($line.description -notmatch $_.description)                 {                     $line.description = $line.description,$_.description -join ','                 }             }                 else             {                 $result += Add-Line $_             }         }     }     else     {         $result += Add-Line $_     } } -End {$result}

Output

PowerShell

Category Description Number Fresh -------- ----------- ------ ----- Fruit Grape,Apple 3 Yes Vegetable Lettuce 3 Yes Vegetable Tomato 3 No

1 2 3 4 5

Category  Description Number Fresh --------  ----------- ------ ----- Fruit     Grape,Apple 3      Yes   Vegetable Lettuce     3      Yes   Vegetable Tomato      3      No

You can simply add a | Export-CSV $somefile -NoTypeInformation  after the -end closing bracket or even inside the -end block right after $result, either will work the same.

[Doug Maurer Participant Topics: 8 Replies: 568 Points: 2,170Rank: Community Hero]

Actually after further testing I found some logic errors that caused additional lines to be added to both the description of similar line and individually. Here is the updated code. I typically discourage using Foreach-Object  when using a foreach statement could help avoid having to stash current objects to variables. However, the need to export to CSV and using -begin/-end blocks I feel warrant making an exception to this rule of thumb. (Specifically referring to $currobj = $_ )

Here was the extended sample data I tested with.

PowerShell

$csv = @' Category,Description,Number,Fresh Fruit,Grape,3,Yes Vegetable,Lettuce,3,Yes Fruit,Grape,3,Yes Fruit,Apple,3,Yes Vegetable,Tomato,3,No Vegetable,Onion,3,No Fruit,Grape,3,No Fruit,Banana,3,No Fruit,Banana,3,Yes '@ | ConvertFrom-Csv

1 2 3 4 5 6 7 8 9 10 11 12

$csv = @' Category,Description,Number,Fresh Fruit,Grape,3,Yes Vegetable,Lettuce,3,Yes Fruit,Grape,3,Yes Fruit,Apple,3,Yes Vegetable,Tomato,3,No Vegetable,Onion,3,No Fruit,Grape,3,No Fruit,Banana,3,No Fruit,Banana,3,Yes '@ | ConvertFrom-Csv

Updated script

PowerShell

$csv | foreach -Begin{ $result = @() function Add-Line($newline){ [PSCustomObject]@{ 'Category' = $newline.category 'Description' = $newline.description 'Number' = $newline.number 'Fresh' = $newline.fresh } } } -process { $currobj = $_ if($lines = $result | where category -eq $currobj.category) { if($matchinglines = $lines | where {$_.number -eq $currobj.number -and $_.fresh -eq $currobj.fresh}) { if($matchinglines.description -notmatch $_.description) { $matchinglines.description = $matchinglines.description,$_.description -join ',' } } else { $result += Add-Line $_ } } else { $result += Add-Line $_ } } -End {$result} | ConvertTo-Csv -NoTypeInformation

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

$csv | foreach -Begin{     $result = @()     function Add-Line($newline){         [PSCustomObject]@{             'Category' = $newline.category             'Description' = $newline.description             'Number' = $newline.number             'Fresh' = $newline.fresh         }     } } -process {     $currobj = $_     if($lines = $result | where category -eq $currobj.category)     {         if($matchinglines = $lines | where {$_.number -eq $currobj.number -and $_.fresh -eq $currobj.fresh})         {             if($matchinglines.description -notmatch $_.description)             {                 $matchinglines.description = $matchinglines.description,$_.description -join ','             }         }         else         {             $result += Add-Line $_         }     }     else     {         $result += Add-Line $_     } } -End {$result} | ConvertTo-Csv -NoTypeInformation

Output

PowerShell

"Category","Description","Number","Fresh" "Fruit","Grape,Apple,Banana","3","Yes" "Vegetable","Lettuce","3","Yes" "Vegetable","Tomato,Onion","3","No" "Fruit","Grape,Banana","3","No"

1 2 3 4 5

"Category","Description","Number","Fresh" "Fruit","Grape,Apple,Banana","3","Yes" "Vegetable","Lettuce","3","Yes" "Vegetable","Tomato,Onion","3","No" "Fruit","Grape,Banana","3","No"

Just change ConvertTo-Csv  to Export-Csv

 

[Doug Maurer Participant Topics: 8 Replies: 568 Points: 2,170Rank: Community Hero]

As I was driving 5 hours home from vacation, it dawned on me this could be simplified.

PowerShell

$csv = @' Category,Description,Number,Fresh Fruit,Grape,3,Yes Vegetable,Lettuce,3,Yes Fruit,Grape,3,Yes Fruit,Apple,3,Yes Vegetable,Tomato,3,No Vegetable,Onion,3,No Fruit,Grape,3,No Fruit,Banana,3,No Fruit,Banana,3,Yes '@ | ConvertFrom-Csv

1 2 3 4 5 6 7 8 9 10 11 12

$csv = @' Category,Description,Number,Fresh Fruit,Grape,3,Yes Vegetable,Lettuce,3,Yes Fruit,Grape,3,Yes Fruit,Apple,3,Yes Vegetable,Tomato,3,No Vegetable,Onion,3,No Fruit,Grape,3,No Fruit,Banana,3,No Fruit,Banana,3,Yes '@ | ConvertFrom-Csv

PowerShell

$result = [System.Collections.Generic.List[object]]::new() foreach($line in $csv) { if($match = $result | where {$_.category -eq $line.category -and $_.number -eq $line.number -and $_.fresh -eq $line.fresh}) { if($match.description -notmatch $line.description) { $match.description = $match.description,$line.description -join ',' } } else { $result.add($line) } } $result

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

$result = [System.Collections.Generic.List[object]]::new()   foreach($line in $csv) {     if($match = $result | where {$_.category -eq $line.category -and $_.number -eq $line.number -and $_.fresh -eq $line.fresh})     {         if($match.description -notmatch $line.description)         {             $match.description = $match.description,$line.description -join ','         }     }     else     {         $result.add($line)     } }   $result

PowerShell

Category Description Number Fresh -------- ----------- ------ ----- Fruit Grape,Apple,Banana 3 Yes Vegetable Lettuce 3 Yes Vegetable Tomato,Onion 3 No Fruit Grape,Banana 3 No

1 2 3 4 5 6

Category  Description        Number Fresh --------  -----------        ------ ----- Fruit     Grape,Apple,Banana 3      Yes   Vegetable Lettuce            3      Yes   Vegetable Tomato,Onion       3      No   Fruit     Grape,Banana       3      No

Simply add | Export-Csv $outputfile -NoTypeInformation after $result to export to CSV. Instead of importing the CSV to a variable you could also write the code like this.

PowerShell

$inputfile = 'path\to\input.csv' $result = [System.Collections.Generic.List[object]]::new() foreach($line in Import-Csv $inputfile) { if($match = $result | where {$_.category -eq $line.category -and $_.number -eq $line.number -and $_.fresh -eq $line.fresh}) { if($match.description -notmatch $line.description) { $match.description = $match.description,$line.description -join ',' } } else { $result.add($line) } } $result | Export-Csv $outputfile -NoTypeInformation

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

$inputfile = 'path\to\input.csv'   $result = [System.Collections.Generic.List[object]]::new()   foreach($line in Import-Csv $inputfile) {     if($match = $result | where {$_.category -eq $line.category -and $_.number -eq $line.number -and $_.fresh -eq $line.fresh})     {         if($match.description -notmatch $line.description)         {             $match.description = $match.description,$line.description -join ','         }     }     else     {         $result.add($line)     } }   $result | Export-Csv $outputfile -NoTypeInformation

[kvprasoon]

Doug Maurer wrote:

As I was driving 5 hours home from vacation, it dawned on me this could be simplified.

PowerShell

$result = [System.Collections.Generic.List[object]]::new() foreach ($line in $csv) { if ($match = $result | where { $_.category -eq $line.category -and $_.number -eq $line.number -and $_.fresh -eq $line.fresh }) { if ($match.description -notmatch $line.description) { $match.description = $match.description, $line.description -join ‘,’ } } else { $result.add($line) } } $result Category Description Number Fresh ——– ———– —— —– Fruit Grape,Apple,Banana 3 Yes Vegetable Lettuce 3 Yes Vegetable Tomato,Onion 3 No Fruit Grape,Banana 3 No $inputfile = ‘path\to\input.csv’ $result = [System.Collections.Generic.List[object]]::new() foreach ($line in Import-Csv $inputfile) { if ($match = $result | where { $_.category -eq $line.category -and $_.number -eq $line.number -and $_.fresh -eq $line.fresh }) { if ($match.description -notmatch $line.description) { $match.description = $match.description, $line.description -join ‘,’ } } else { $result.add($line) } } $result | Export-Csv $outputfile -NoTypeInformation

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

$result = [System.Collections.Generic.List[object]]::new() foreach ($line in $csv) {     if ($match = $result | where { $_.category -eq $line.category -and $_.number -eq $line.number -and $_.fresh -eq $line.fresh }) {         if ($match.description -notmatch $line.description) {             $match.description = $match.description, $line.description -join ‘,’         }     }     else {         $result.add($line)     } }   $result   Category Description Number Fresh ——– ———– —— —– Fruit Grape,Apple,Banana 3 Yes Vegetable Lettuce 3 Yes Vegetable Tomato,Onion 3 No Fruit Grape,Banana 3 No     $inputfile = ‘path\to\input.csv’ $result = [System.Collections.Generic.List[object]]::new()   foreach ($line in Import-Csv $inputfile) {     if ($match = $result | where { $_.category -eq $line.category -and $_.number -eq $line.number -and $_.fresh -eq $line.fresh }) {         if ($match.description -notmatch $line.description) {             $match.description = $match.description, $line.description -join ‘,’         }     }     else {         $result.add($line)     } }   $result | Export-Csv $outputfile -NoTypeInformation

Thank you i tested it with some of my changes and it work beautifully. Appreciate all the help.

• This reply was modified 1 week, 3 days ago by kvprasoon. Reason: code formatting

[Author]

Posts