Concatenate field based on key value

Welcome Forums General PowerShell Q&A Concatenate field based on key value

Viewing 8 reply threads
  • Author
    Posts
    • #206478
      Participant
      Topics: 1
      Replies: 3
      Points: 0
      Rank: Member

      Hello,

       

      I have a .csv that looks like this;

      Field1         KeyField2 Field3 Field4 Field5 Field6 Field7   Field8
      Pr1              3378           John    M         44        Y          Green   1112223333
      Pr2             4235           Steve    T          21         N         Red       2223331111
      Pr4             3378           John    M         44         Y         Green   1112223333
      Pr7             2346           Chris    Y          32         Y         Yellow  2221113333

      I want the final result to look like this saved to a new .csv;

      Field1         KeyField2 Field3 Field4 Field5 Field6 Field7   Field8
      Pr1, Pr4     3378           John    M         44        Y          Green   1112223333
      Pr2             4235           Steve    T          21         N         Red       2223331111
      Pr7             2346           Chris    Y          32         Y         Yellow  2221113333

       

      I’ve tried a few methods, but nothing that works.

       

      Any idea how to accomplish this?

       

      Thanks!

      • This topic was modified 2 months, 4 weeks ago by chalexander45.
    • #206484
      Moderator
      Topics: 3
      Replies: 216
      Points: 1,176
      Helping Hand
      Rank: Community Hero

      Hi ,

      Whatever you have tried so far, can you please put it down here, so that we can assist you further.

      Thank you.

    • #206487
      Participant
      Topics: 1
      Replies: 3
      Points: 0
      Rank: Member

      Hi Kiran,

      It’s been a few weeks since I’ve worked on this, and all my previous scripting attempts have been deleted…….

      Thanks

       

    • #206511
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      You should read the help for the cmdlet Group-Object. Please read it completely including the examples to learn how to use it. Thanks.

    • #206553
      Participant
      Topics: 12
      Replies: 519
      Points: 1,194
      Helping Hand
      Rank: Community Hero

      Hello,

      I have a .csv that looks like this;

      Field1 KeyField2 Field3 Field4 Field5 Field6 Field7 Field8

      Pr1 3378 John M 44 Y Green 1112223333

      Pr2 4235 Steve T 21 N Red 2223331111

      Pr4 3378 John M 44 Y Green 1112223333

      Pr7 2346 Chris Y 32 Y Yellow 2221113333

      I want the final result to look like this;

      Field1 KeyField2 Field3 Field4 Field5 Field6 Field7 Field8

      Pr1, Pr4 3378 John M 44 Y Green 1112223333

      Pr2 4235 Steve T 21 N Red 2223331111

      Pr7 2346 Chris Y 32 Y Yellow 2221113333

      I’ve tried a few methods, but nothing that works.

      Any idea how to accomplish this?

      Thanks!

      # https://powershell.org/forums/topic/concatenate-field-based-on-key-value/
      # Script to 'combine' identical records
      # Criteria: Identical value in $Keyfield
      # Combining method: Add multiple values in $CombinedField
      # Sam Boutros - 27 Feb 2020 
      
      #region Input
      $InputFile      = '.\test1.csv'
      $KeyField       = 'Field2'
      $CombinedField  = 'Field1'
      #endregion
      
      #region Prep - create input CSV sample
      '"Field1","Field2","Field3","Field4","Field5","Field6","Field7","Field8"' | Out-File $InputFile
      '"Pr1","3378","John","M","44","Y","Green","1112223333"' | Out-File $InputFile -Append
      '"Pr2","4235","Steve","T","21","N","Red","2223331111"' | Out-File $InputFile -Append
      '"Pr4","3378","John","M","44","Y","Green","1112223333"' | Out-File $InputFile -Append
      '"Pr7","2346","Chris","Y","32","Y","Yellow","2221113333"' | Out-File $InputFile -Append
      #endregion
      
      #region Process
      $myInput = Import-Csv $InputFile
      
      $MyOutput = foreach ($Group in ($myInput | group $KeyField)) {
          if ($Group.Count -eq 1) {
              $Group.Group 
          } else {
              $CombinedRecord = ($Group.Group[0]).psobject.copy()
              $CombinedRecord.$CombinedField = foreach ($Record in $Group.Group) { $Record.$CombinedField }
              $CombinedRecord
          }
      }
      #endregion
      
      #region Output
      "Here's the sample CSV:"
      $myInput | FT -a 
      "and here's the output:"
      $MyOutput | FT -a 
      #endregion
      
      Here's the sample CSV:
      
      Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8    
      ------ ------ ------ ------ ------ ------ ------ ------    
      Pr1    3378   John   M      44     Y      Green  1112223333
      Pr2    4235   Steve  T      21     N      Red    2223331111
      Pr4    3378   John   M      44     Y      Green  1112223333
      Pr7    2346   Chris  Y      32     Y      Yellow 2221113333
      
      
      and here's the output:
      
      Field1     Field2 Field3 Field4 Field5 Field6 Field7 Field8    
      ------     ------ ------ ------ ------ ------ ------ ------    
      {Pr1, Pr4} 3378   John   M      44     Y      Green  1112223333
      Pr2        4235   Steve  T      21     N      Red    2223331111
      Pr7        2346   Chris  Y      32     Y      Yellow 2221113333
      
      • This reply was modified 2 months, 4 weeks ago by Sam Boutros.
      • This reply was modified 2 months, 4 weeks ago by Sam Boutros.
    • #206568
      Participant
      Topics: 5
      Replies: 321
      Points: 436
      Helping Hand
      Rank: Contributor

      There must be a cleaner way of doing this, but this may work for you.

      # Group objects based on header 'Field2'
      $group = Import-Csv data.csv | Group-Object -Property Field2
      
      $group | ForEach-Object {
          [PSCustomObject]@{
              Field1 = $_.group.Field1 -join ','
              Field2 = $_.group.Field2 | Get-Unique
              Field3 = $_.group.Field3 | Get-Unique
              Field4 = $_.group.Field4 | Get-Unique
              Field5 = $_.group.Field5 | Get-Unique
              Field6 = $_.group.Field6 | Get-Unique
              Field7 = $_.group.Field7 | Get-Unique
              Field8 = $_.group.Field8 | Get-Unique
          }
      } | Export-Csv -NoTypeInformation -Path .\group.csv
      
    • #207168
      Participant
      Topics: 1
      Replies: 3
      Points: 0
      Rank: Member

      This works great……. any idea how to run this with Import and export files?

       

      Import file – Imp_1.csv

      Export file – Exp_1.csv

       

      Thanks

    • #207237
      Participant
      Topics: 5
      Replies: 321
      Points: 436
      Helping Hand
      Rank: Contributor

      I changed my previous post to export the results.

    • #208314
      Participant
      Topics: 1
      Replies: 3
      Points: 0
      Rank: Member

      I changed my previous post to export the results.

      Thank you for this ! It works great however some fields have System.Object[] as values when using Get-Unique. Any way to solve this?

Viewing 8 reply threads
  • You must be logged in to reply to this topic.