Welcome › Forums › General PowerShell Q&A › Find Matching Column and concatenated into one cell while keeping other data
- This topic has 7 replies, 4 voices, and was last updated 4 months ago by
Participant.
-
AuthorPosts
-
-
September 11, 2020 at 3:31 pm #255953
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 4 months, 1 week ago by
tn647883.
-
This topic was modified 4 months, 1 week ago by
-
September 11, 2020 at 3:54 pm #255980
Please post an example of the final output you are expecting.
-
September 11, 2020 at 8:37 pm #256013PowerShell12345678$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-CsvPowerShell123456789101112131415161718192021222324$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 4 months, 1 week ago by
ImAhNoBoDy.
-
This reply was modified 4 months ago by
ImAhNoBoDy.
-
This reply was modified 4 months ago by
ImAhNoBoDy.
-
This reply was modified 4 months ago by
grokkit.
-
This reply was modified 4 months, 1 week ago by
-
September 12, 2020 at 12:31 am #256064
Here is one way to achieve your desired result.
PowerShell123456789101112131415161718192021222324252627282930313233343536373839404142$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
PowerShell12345Category Description Number Fresh-------- ----------- ------ -----Fruit Grape,Apple 3 YesVegetable Lettuce 3 YesVegetable Tomato 3 NoYou 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.
-
September 13, 2020 at 1:33 am #256184
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.
PowerShell123456789101112$csv = @'Category,Description,Number,FreshFruit,Grape,3,YesVegetable,Lettuce,3,YesFruit,Grape,3,YesFruit,Apple,3,YesVegetable,Tomato,3,NoVegetable,Onion,3,NoFruit,Grape,3,NoFruit,Banana,3,NoFruit,Banana,3,Yes'@ | ConvertFrom-CsvUpdated script
PowerShell12345678910111213141516171819202122232425262728293031$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 -NoTypeInformationOutput
PowerShell12345"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
-
September 13, 2020 at 6:14 pm #256295
As I was driving 5 hours home from vacation, it dawned on me this could be simplified.
PowerShell123456789101112$csv = @'Category,Description,Number,FreshFruit,Grape,3,YesVegetable,Lettuce,3,YesFruit,Grape,3,YesFruit,Apple,3,YesVegetable,Tomato,3,NoVegetable,Onion,3,NoFruit,Grape,3,NoFruit,Banana,3,NoFruit,Banana,3,Yes'@ | ConvertFrom-CsvPowerShell123456789101112131415161718$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)}}$resultPowerShell123456Category Description Number Fresh-------- ----------- ------ -----Fruit Grape,Apple,Banana 3 YesVegetable Lettuce 3 YesVegetable Tomato,Onion 3 NoFruit Grape,Banana 3 NoSimply 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.
PowerShell1234567891011121314151617181920$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 -
September 14, 2020 at 4:07 pm #256397
As I was driving 5 hours home from vacation, it dawned on me this could be simplified.
PowerShell12345678910111213141516171819202122232425262728293031323334353637$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)}}$resultCategory Description Number Fresh——– ———– —— —–Fruit Grape,Apple,Banana 3 YesVegetable Lettuce 3 YesVegetable Tomato,Onion 3 NoFruit 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 -NoTypeInformationThank you i tested it with some of my changes and it work beautifully. Appreciate all the help.
-
This reply was modified 4 months ago by
kvprasoon. Reason: code formatting
-
This reply was modified 4 months ago by
-
-
AuthorPosts
- The topic ‘Find Matching Column and concatenated into one cell while keeping other data’ is closed to new replies.