Multivalued Array – concatenate double values

[jhn01 Participant Topics: 1 Replies: 2 Points: 16Rank: Member]

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.

 

[Matt Bloomfield Participant Topics: 4 Replies: 422 Points: 734Rank: Major Contributor]

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).

 

PowerShell

$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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

$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

[random commandline Participant Topics: 5 Replies: 349 Points: 632Rank: Major Contributor]

PowerShell

$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

1 2 3 4 5 6 7 8

$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

[jhn01]

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:

PowerShell

$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 }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

$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!

• This reply was modified 1 month ago by jhn01.

[jhn01 Participant Topics: 1 Replies: 2 Points: 16Rank: Member]

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:

PowerShell

$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 }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

$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.

[Author]

Posts