# Concatenate field based on key value **URL:** \ **Category:** PowerShell Help\ **Created:** [February 27, 2020, 7:37am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877 "2020-02-27T07:37:52Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![chalexander](https://avatars.discourse-cdn.com/v4/letter/c/9dc877/32.png) [@chalexander](https://forums.powershell.org/u/chalexander)\ **Post date:** [February 27, 2020, 7:37am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/1 "2020-02-27T07:37:52Z")
Hello,   I have a .csv that looks like this; Field1 **Key** 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 I want the final result to look like this saved to a new .csv; Field1 **Key** 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   I’ve tried a few methods, but nothing that works.   Any idea how to accomplish this?   Thanks! ---
**Author:** ![kpatnayakuni](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/kpatnayakuni/32/19_2.png) [@kpatnayakuni](https://forums.powershell.org/u/kpatnayakuni)\ **Post date:** [February 27, 2020, 7:41am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/2 "2020-02-27T07:41:00Z")
Hi , Whatever you have tried so far, can you please put it down here, so that we can assist you further. Thank you. ---
**Author:** ![chalexander](https://avatars.discourse-cdn.com/v4/letter/c/9dc877/32.png) [@chalexander](https://forums.powershell.org/u/chalexander)\ **Post date:** [February 27, 2020, 7:47am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/3 "2020-02-27T07:47:00Z")
Hi Kiran, It’s been a few weeks since I’ve worked on this, and all my previous scripting attempts have been deleted… Thanks   ---
**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\ **Post date:** [February 27, 2020, 9:09am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/4 "2020-02-27T09:09:45Z")
You should read the help for the cmdlet [Group-Object](https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/group-object?view=powershell-7). Please read it completely including the examples to learn how to use it. Thanks. ---
**Author:** ![sam-boutros](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/sam-boutros/32/5_2.png) [@sam-boutros](https://forums.powershell.org/u/sam-boutros)\ **Post date:** [February 27, 2020, 10:40am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/5 "2020-02-27T10:40:16Z")
[quote quote=206478]Hello, I have a .csv that looks like this; Field1 **Key** 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 I want the final result to look like this; Field1 **Key** 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 I’ve tried a few methods, but nothing that works. Any idea how to accomplish this? Thanks![/quote] ``` # 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 ``` ---
**Author:** ![random-commandline](https://avatars.discourse-cdn.com/v4/letter/r/e56c9b/32.png) [@random-commandline](https://forums.powershell.org/u/random-commandline)\ **Post date:** [February 27, 2020, 10:50am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/6 "2020-02-27T10:50:46Z")
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 ``` ---
**Author:** ![chalexander](https://avatars.discourse-cdn.com/v4/letter/c/9dc877/32.png) [@chalexander](https://forums.powershell.org/u/chalexander)\ **Post date:** [March 2, 2020, 8:55am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/7 "2020-03-02T08:55:11Z")
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 ---
**Author:** ![random-commandline](https://avatars.discourse-cdn.com/v4/letter/r/e56c9b/32.png) [@random-commandline](https://forums.powershell.org/u/random-commandline)\ **Post date:** [March 2, 2020, 10:52am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/8 "2020-03-02T10:52:39Z")
I changed my previous post to export the results. ---
**Author:** ![chalexander](https://avatars.discourse-cdn.com/v4/letter/c/9dc877/32.png) [@chalexander](https://forums.powershell.org/u/chalexander)\ **Post date:** [March 5, 2020, 7:07am UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/9 "2020-03-05T07:07:52Z")
[quote quote=207237]I changed my previous post to export the results. [/quote] Thank you for this ! It works great however some fields have **System.Object** as values when using Get-Unique. Any way to solve this? ---
**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\ **Post date:** [May 16, 2024, 8:30pm UTC](https://forums.powershell.org/t/concatenate-field-based-on-key-value/13877/10 "2024-05-16T20:30:20Z")