Multivalued Array - concatenate double values

PowerShell Help
1

Hello, been bugging me all day and I cannot seem to find a decent solution … I hope the powershell community can help me.

I have the following array:

col1 Col2
ab data1
cd data2
ef data1
ab data2
cd data3
The array is created from a csv which is imported through 'Import-CSV' with -Delimiter "," and -Header "Col1","Col2". The goal is to rework this Array to concatenate the value from 'Col2' for the double entries in 'Col1', preferably into a new array. The output would then look like
Col1 Col2
ab data1 | data2
cd data2 | data3
ef data1
I've been fuzzing around with a lot of code snippets but nothing that gives me the desired output.

 

2

If it’s only two columns, you could import the CSV into a hashtable and see if the key (column 1) already exists, if it does, add it to existing key (hashtables can’t contain duplicate keys).

 

$data = @"
col1,col2
ab,data1
cd,data2
ef,data1
ab,data2
cd,data3
"@

$csv = ConvertFrom-Csv $data

$HashTable = @{}

foreach($row in $csv) {

    if ($row.col1 -in $hashTable.Keys) {
    
        $hashTable[$row.col1] = $hashTable[$row.col1] + " | $($row.col2)"
    
    }

    else {

        $hashTable[$row.col1] = $row.col2

    }
    
}

$hashTable



Output:

Name                           Value
----                           -----
cd                             data2 | data3
ef                             data1
ab                             data1 | data2
3 
$group = Import-Csv -Path input.csv -Delimiter ',' -Header 'Col1','Col2' | Group-Object -Property Col1
$result = foreach ($g in $group){
    [PSCustomObject]@{
        Col1 = $g.Name
        Col2 = $g.Group.Col2 | Out-String
    }
}
$result | Export-Csv -Path result.csv -NoTypeInformation
4

Matt, thank you for your reply. I was also looking at that solution, but that didn’t do the trick.

I must say I forgot to mention there are more then 2 columns.

Finally, the code from random commandline did the trick.

A user, identified by his login, can be multiple times in the received csv file. Each time with a different group.
Goal was to create a new Array with unique logins (no doubles) with all the groups for the user grouped in one Array element for that login
Finally, it looks like this:

$csv = Import-CSV "received.csv" -Header "Login","Mail","FirstName","LastName","ADGroupId" -Delimiter ";" | Group-Object -Property Login
$result = foreach ($line in $csv)
{
$myvar = $line.Group.ADGroupId | Out-String
$myvar = $myvar -Replace('[\n]', "|")

$NewLine = [PSCustomObject]@{
Login = $line.Name
Mail = $($line.Group.Mail |Select-Object -Last 1)
FirstName = $($line.Group.FirstName |Select-Object -Last 1)
LastName = $($line.Group.LastName |Select-Object -Last 1)
ADGroupId = $myvar.TrimEnd("|")
}
$FlattenArray += $NewLine
}

The Out-String comes with a “new line”, hence “-Replace(‘[\n]’, “|”)”
“TrimEnd(”|“)” to remove the final ‘|’

Thank you!

5

thank you for your input.
Matt, i was already trying this piece of code, but it didn’t do the trick. I forgot to mention in my original post that there are more then 2 columns.
Finally, the code from Random Commandline did the trick.

Input is a csv with several columns, one containing group info.
The csv can contain multiple lines for the same user, each representing a different group.
Goal was to create a final array, only with all the unique users with a ‘group’ field per user containing all the groups (if there are multiple).

The final code looks like this:

$csv = Import-CSV "received.csv" -Header "Login","Mail","FirstName","LastName","ADGroupId" -Delimiter ";" | Group-Object -Property Login
$result = foreach ($line in $csv)
{
$myvar = $line.Group.ADGroupId | Out-String
$myvar = $myvar -Replace('[\n]', "|")

$NewLine = [PSCustomObject]@{
Login = $line.Name
Mail = $($line.Group.Mail |Select-Object -Last 1)
FirstName = $($line.Group.FirstName |Select-Object -Last 1)
LastName = $($line.Group.LastName |Select-Object -Last 1)
ADGroupId = $myvar.TrimEnd("|")
}
$FlattenArray += $NewLine
}

-Replace(‘[\n]’, “|”) –> the outstring comes with a new line for each value
.TrimEnd(“|”) –> to remove the final “|”

Thank you all for your help.