Concatenate field based on key value

PowerShell Help
1

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!

2

Hi ,

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

Thank you.

3

Hi Kiran,

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

Thanks

 

4

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

5

[quote quote=206478]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![/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
6

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
7

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

8

I changed my previous post to export the results.

9

[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?