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

Welcome Forums General PowerShell Q&A Find Matching Column and concatenated into one cell while keeping other data

Viewing 6 reply threads
  • Author
    Posts
    • #255953
      Participant
      Topics: 6
      Replies: 9
      Points: 63
      Rank: Member

      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.
    • #255980
      Participant
      Topics: 0
      Replies: 81
      Points: 362
      Helping Hand
      Rank: Contributor

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

    • #256013
      Participant
      Topics: 13
      Replies: 15
      Points: 85
      Rank: Member
      • 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.
    • #256064
      Participant
      Topics: 8
      Replies: 568
      Points: 2,170
      Helping Hand
      Rank: Community Hero

      Here is one way to achieve your desired result.

      Output

      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.

    • #256184
      Participant
      Topics: 8
      Replies: 568
      Points: 2,170
      Helping Hand
      Rank: 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.

      Updated script

      Output

      Just change ConvertTo-Csv  to Export-Csv

       

    • #256295
      Participant
      Topics: 8
      Replies: 568
      Points: 2,170
      Helping Hand
      Rank: Community Hero

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

      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.

    • #256397
      Participant
      Topics: 6
      Replies: 9
      Points: 63
      Rank: Member

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

      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
Viewing 6 reply threads
  • You must be logged in to reply to this topic.