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

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.

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

$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
$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

Here is one way to achieve your desired result.

$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

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.

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.

$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

$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

"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

 

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

$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
$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   

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.

$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

[quote quote=256295]As I was driving 5 hours home from vacation, it dawned on me this could be simplified.

$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.