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

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

CodePudding user response：

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.