How do you group unique values from imported csv in a foreach loop

Question

I've got a txt file with the following content:

#test.txt

'ALDHT21;MIMO;1111;BOK;Tree'
'ALDHT21;MIMO;1211;BOK;Tree'
'PRGHT21;AIMO;1351;STE;Water'
'PRGHT21;AIMO;8888;FRA;Stone'
'ABCDT22;DIDO;8888;STE;Stone'
'PRA2HT21;ADDO;8888;STE;Stone'
';ADDO;1317;STE;Stone'

To make it easier to explain, let's give the above content headers: ''Group;Code;ID;Signature;Type'

With the help of Powershell, I'm trying to create a foreach loop of each unique "Signature" to return two variables with unique data from rows where the "Signature" exists in and then mashed together with some delimiters.

Based on the file content, here are the expected results:

First loop:

$Signature = "BOK" 

$Groups = "Tree:ALDHT21"

$Codes = "Tree:MIMO"

Next loop:

$Signature = "FRA"

$Groups = "Stone:PRGHT21"

$Codes = "Stone:AIMO"

Last loop:

$Signature = "STE"

$Groups = "Stone:PRA2HT21,Stone:ABCDT22,Water:PRGHT21"

$Codes = "Stone:ADDO,Stone:DIDO,Water:AIMO"

Notice the last loop should skip the last entry in the file because it contains an empty Group.

My attempt didn't quite hit the mark and I'm struggling to find a good way to accomplish this:

$file = "C:\temp\test.txt"
$uniqueSigs = (gc $file) -replace "'$|^'" | ConvertFrom-Csv -Delimiter ';' -Header Group,Code,ID,Signature,Type | group Signature
foreach ($sigs in $uniqueSigs) {
    $Groups = ""
    foreach ($Group in $sigs.Group) {
        $Groups += "$($Group.Type):$($Group.Group),"
    }
    $Groups = $Groups -replace ",$" 
    [PSCustomObject] @{
        Signatur = $sigs.Name
        Groups = $Groups
    }
    $Codes = ""
    foreach ($Group in $sigs.Group) {
        $Codes += "$($Group.Type):$($Group.Code),"
    }
    $Codes = $Codes -replace ",$" 
    [PSCustomObject] @{
        Signatur = $sigs.Name
        Codes = $Codes
    }
    $Signature = $sigs.Name
    If ($Group.Group){
    write-host "$Signature "-" $Groups "-" $Codes "
}
}

Result from my bad attempt:

BOK  - Tree:ALDHT21,Tree:ALDHT21 - Tree:MIMO,Tree:MIMO 
FRA  - Stone:PRGHT21 - Stone:AIMO

Any help appreciated. :)

Why don't you import everything to a variable first? `$result = Import-Csv $File-Delimiter ';' -Header Group,Code,ID,Signature,Type` and afterwards you can sort and filter? — IT M, Nov 25 '21 at 15:24
Does the text file actually contain the literal `'` at the start and end of each line? — Mathias R. Jessen, Nov 25 '21 at 15:25
As for making each to a variable, how do you avoid duplicates in the Codes and Groups variable when you match them together? — aka_1337, Nov 25 '21 at 15:51

mklement0 · Accepted Answer · 2021-11-25T20:55:34.837

4

Your variables are somewhat confusingly named; the following streamlined solution uses fewer variables and perhaps produces the desired result:

$file = "test.txt"
(Get-Content $file) -replace "'$|^'" | ConvertFrom-Csv -Delimiter ';' -Header Group,Code,ID,Signature,Type |
  Group-Object Signature |
    ForEach-Object {
      # Create and output an object with group information.
      # Skip empty .Group properties among the group's member objects.
      # Get the concatenation of all .Group and .Code column
      # values each, skipping empty groups and eliminating duplicates.
      $groups = (
        $_.Group.ForEach({ if ($_.Group) { "$($_.Type):$($_.Group)" } }) |
          Select-Object -Unique
      ) -join ","
      $codes = (
        $_.Group.ForEach({ "$($_.Type):$($_.Code)" }) |
          Select-Object -Unique
      ) -join ","
      # Create and output an object comprising the signature
      # and the concatenated groups and codes.
      [PSCustomObject] @{
          Signature = $_.Name
          Groups = $groups
          Codes = $codes
      }
      # Note: This is just *for-display* output.
      # Don't use Write-Host to output *data*.
      Write-Host ($_.Name, $groups, $codes -join ' - ')
    }

Output:

BOK - Tree:ALDHT21 - Tree:MIMO
FRA - Stone:PRGHT21 - Stone:AIMO
STE - Water:PRGHT21,Stone:ABCDT22,Stone:PRA2HT21 - Water:AIMO,Stone:DIDO,Stone:ADDO
Signature Groups                                     Codes
--------- ------                                     -----
BOK       Tree:ALDHT21                               Tree:MIMO
FRA       Stone:PRGHT21                              Stone:AIMO
STE       Water:PRGHT21,Stone:ABCDT22,Stone:PRA2HT21 Water:AIMO,Stone:DIDO,Stone:ADDO

Note that the for-display Write-Host surprisingly precedes the the default output formatting for the [pscustomobject] instances, which is due to the asynchronous behavior of the implicitly applied Format-Table formatting explained in this answer.

edited Nov 25 '21 at 20:55

answered Nov 25 '21 at 16:10

mklement0

382,024
64
607
775

That does indeed look cleaner. However the result is somewhat alike mine where you're not getting unique rows. Your result gives: BOK - Tree:ALDHT21,Tree:ALDHT21 - Tree:MIMO,Tree:MIMO STE - Water:PRGHT21,Stone:ABCDT22,Stone:PRA2HT21,Stone: - Water:AIMO,Stone:DIDO,Stone:ADDO,Stone:ADDO FRA - Stone:PRGHT21 - Stone:AIMO And I'm trying to achieve unique values for $groups and $codes like this: BOK - Tree:ALDHT21 - Tree:MIMO STE - Water:PRGHT21,Stone:ABCDT22,Stone:PRA2HT21,Stone: - Water:AIMO,Stone:DIDO,Stone:ADDO FRA - Stone:PRGHT21 - Stone:AIMO – aka_1337 Nov 25 '21 at 19:15
Couldn't edit my previous comment. Meant to add that your example also does not "skip" groups that are empty. So pretty much the same as my attempt but much much fancier :) – aka_1337 Nov 25 '21 at 19:36
@aka_1337, I've added code to filter out empty group values. If you want to avoid duplicates, one option is to just output _one_ `[pscustomobject]` instance per group, and give it both a `.Groups` and a `.Codes` property. Please see my update, which also shows the code's output. If that doesn't help, please clarify your requirements. – mklement0 Nov 25 '21 at 20:08
Writing from my mobile, excuse any typos. The update looks almost like what my end goal is. The only thing left, and what has been my biggest problem to solve yet is the duplicates in the output. I only want to show unique values in $groups and $code. For example “tree:ALDHT21,Tree:ALDHT21“ should just be “tree:ALDHT21” as they would otherwise be the same twice. Hope that makes sense. Appreciate all your help and patience so far! – aka_1337 Nov 25 '21 at 20:38
If you want to avoid duplicates, you could do things like `$codes = ( $_.Group | ForEach-Object { "$($_.Type):$($_.Code)" } | Select -Unique ) -join ","` – carrvo Nov 25 '21 at 20:49
Also, but I guess some asynchronous issue from the original post, I might have suggested using `| Select-Object @{Name='Signature';Expression={$_.Name}},@{Name='Groups';Expression={...}},@{Name='Codes';Expression={...}}` instead of `| ForEach-Object {...}`. Might be more complex but also might clean up the `[PSCustomObject]` cast... – carrvo Nov 25 '21 at 20:53
1

Good point about `Select-Object -Unique`, @carrvo - answer updated. Calculated properties with `Select-Object` are a great option in general, but I don't think it's the right choice here, given the complexity of the expressions involved. I'm not sure if that's what you were trying to say, but to be clear: you'll get the output-timing problem irrespective o whether you use `Select-Object` or a `[pscustomobject]` cast. – mklement0 Nov 25 '21 at 20:59
Can't thank you enough @mklement0 ! Works perfectly, and thanks for the explanations they've definitely helped! – aka_1337 Nov 26 '21 at 07:27
Glad to hear it, @aka_1337; my pleasure. – mklement0 Nov 26 '21 at 13:11

How do you group unique values from imported csv in a foreach loop

1 Answers1