compare two multip column araays

Welcome Forums General PowerShell Q&A compare two multip column araays

This topic contains 10 replies, has 5 voices, and was last updated by

 
Participant
2 years, 8 months ago.

  • Author
    Posts
  • #39315

    Participant
    Points: 0
    Rank: Member

    I've two arrays; 1. $A
    PackageId Versionid
    ——— ———
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761

    and $B;

    PackageId Versionid
    ——— ———
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    AF553CD0-F24F-44F1 679F7BDF-788D-4593
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C

    I want to get the line from Array $B which does not exist in $A,

    i first tried it with a $B | where {$A -notcontains $_}, but this didn't work well. Could you help me out?

  • #39316

    Participant
    Points: 58
    Rank: Member

    I have two options below.

    $array1 = @"
    PackageId Versionid
    ——— ———
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    "@ -split "`n"
    
    $array2 = @"
    PackageId Versionid
    ——— ———
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    AF553CD0-F24F-44F1 679F7BDF-788D-4593
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    "@ -split "`n"
    
    foreach ($item in $array2){
        If ($array1 -notcontains $item){$item}}
    
    Compare-Object -ReferenceObject $array2 -DifferenceObject $array1
    # Results:
    # InputObject                                       SideIndicator
    # -----------                                       -------------
    # AF553CD0-F24F-44F1 679F7BDF-788D-4593             <=
    
    • #43548

      Participant
      Points: 0
      Rank: Member

      thx! sorry for the delay. this indeed works . however. how can I go back to a array table?
      when the result is like this;

      @{PACKAGEID=C3EB6BB1-4294-4634-BAAE-EE974212EADF; VERSIONID=C3EB6BB1-4294-4634-BAAE-EE974212EADE}
      @{PACKAGEID=C3EB6BB1-4294-4634-BAAE-EE974212EADF; VERSIONID=C3EB6BB1-4294-4634-BAAE-EE974212EADF}
      
  • #39318

    Participant
    Points: 21
    Rank: Member

    That's a neat way to make arrays 🙂

  • #44639

    Participant
    Points: 0
    Rank: Member

    Anyone? The split works fine to be able to compare. But I need to revert it back to an array, to work with the different values, or remove or add columns, values to the array

  • #44673

    Participant
    Points: 638
    Helping Hand
    Rank: Major Contributor

    You have 2 arrays:

    $array1 = @()
    $array1 += [pscustomobject]@{PackageID="58E4DB62-67D3-40CA";VersionId="122600F0-34E0-4F3C"}
    $array1 += [pscustomobject]@{PackageID="AF553CD0-F24F-44F1";VersionId="E69AE4CF-C3E1-410C"}
    $array1 += [pscustomobject]@{PackageID="AE67BEFF-ECB8-42C7";VersionId="E77EC859-E340-4761"}
    
    $array2 = @()
    $array2 += [pscustomobject]@{PackageID="58E4DB62-67D3-40CA";VersionId="122600F0-34E0-4F3C"}
    $array2 += [pscustomobject]@{PackageID="AE67BEFF-ECB8-42C7";VersionId="E77EC859-E340-4761"}
    $array2 += [pscustomobject]@{PackageID="AF553CD0-F24F-44F1";VersionId="679F7BDF-788D-4593"}
    $array2 += [pscustomobject]@{PackageID="AF553CD0-F24F-44F1";VersionId="E69AE4CF-C3E1-410C"}
    

    I'm not sure what the end result is that you are looking for. Here are some options to work with the data. If you are trying to create a final object, you can do something like this:

    #Join the arrays and get unique VersionID
    $array3 = $array1 + $array2
    $array3 | Sort-Object VersionId -Unique
    
    
    PackageID          VersionId         
    ---------          ---------         
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AF553CD0-F24F-44F1 679F7BDF-788D-4593
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    

    If you simply want the difference, you can do something like this:

    #Get the difference item and add it to a new object
    $array4 = Compare-Object -ReferenceObject $array1 -DifferenceObject $array2 -PassThru | foreach {
        $_ | Select PackageID, VersionID
    }
    
    PS C:\Users\Rob> $array4
    
    PackageID          VersionId         
    ---------          ---------         
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    

    If you want to add it to an existing array, such as array 1...

    PS C:\Users\Rob> #Join the difference to the first array
    $array1 + $array4
    
    
    PackageID          VersionId         
    ---------          ---------         
    58E4DB62-67D3-40CA 122600F0-34E0-4F3C
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    AE67BEFF-ECB8-42C7 E77EC859-E340-4761
    AF553CD0-F24F-44F1 E69AE4CF-C3E1-410C
    
  • #44676

    Participant
    Points: 0
    Rank: Member
    PS C:\Users\n002323b.PPGEUR.000> $ActivePackagesOnDiskArray
    
    PackageId                                                                           Versionid                                                                         
    ---------                                                                           ---------                                                                         
    58E4DB62-67D3-40CA-9051-31FD54A0AB1A                                                58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                              
    58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                                122600F0-34E0-4F3C-AA30-3BE7A0733EBA                                              
    58E4DB62-67D3-40CA-9051-31FD54A0AB1E                                                58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                              
    AE67BEFF-ECB8-42C7-AB01-C14698F415D9                                                D3629331-A90E-4E87-92BC-32D7F48BF37E                                              
    C3EB6BB1-4294-4634-BAAE-EE974212EADE                                                CD3EA732-7078-4B29-97A4-277C830AFE94                                              
    E0F1D187-93A6-42D1-96BF-72735E442B65                                                625EED06-E466-4C50-B264-C9D6FE6B8314                                              
    
    
    
    PS C:\Users\n002323b.PPGEUR.000> $ActivePackagesArray
    
    PackageId                                                                           Versionid                                                                         
    ---------                                                                           ---------                                                                         
    58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                                122600F0-34E0-4F3C-AA30-3BE7A0733EBA                                              
    E0F1D187-93A6-42D1-96BF-72735E442B65                                                625EED06-E466-4C50-B264-C9D6FE6B8314                                              
    C3EB6BB1-4294-4634-BAAE-EE974212EADE                                                CD3EA732-7078-4B29-97A4-277C830AFE94                                              
    AE67BEFF-ECB8-42C7-AB01-C14698F415D9                                                D3629331-A90E-4E87-92BC-32D7F48BF37E                                              
    
    
    
    PS C:\Users\n002323b.PPGEUR.000> Compare-Object -ReferenceObject $ActivePackagesArray -DifferenceObject $ActivePackagesOnDiskArray -PassThru | foreach {
        $_ | Select PackageID, VersionID
    }
    
    PackageId                                                                           Versionid                                                                         
    ---------                                                                           ---------                                                                         
    C3EB6BB1-4294-4634-BAAE-EE974212EADE                                                CD3EA732-7078-4B29-97A4-277C830AFE94                                              
    E0F1D187-93A6-42D1-96BF-72735E442B65                                                625EED06-E466-4C50-B264-C9D6FE6B8314                                              
    
    

    The issue with directly comparing a multi columns array is that It give me the wrong lines back, as shown above. For that, the suggestion to split it using '`n' worked. It give me the correct lines back. But then I've formatted output like;

    @{PACKAGEID=C3EB6BB1-4294-4634-BAAE-EE974212EADF; VERSIONID=C3EB6BB1-4294-4634-BAAE-EE974212EADE}
    @{PACKAGEID=C3EB6BB1-4294-4634-BAAE-EE974212EADF; VERSIONID=C3EB6BB1-4294-4634-BAAE-EE974212EADF}
    

    I need that to revert that back to an array table. to better work with that.

  • #44747

    Participant
    Points: 638
    Helping Hand
    Rank: Major Contributor

    To answer your question, those are hash tables. A Powershell Object (PSObject) is an array of hash tables. If you look at the previous example, when I created the mock objects, I used a typename of [pscustomobject], this a simple way to convert a hashtable to a PSObject. Either method below would convert the hash table to a PSObject.

    $props = @{PACKAGEID="C3EB6BB1-4294-4634-BAAE-EE974212EADF"; VERSIONID="C3EB6BB1-4294-4634-BAAE-EE974212EADE"}
    
    New-Object -TypeName PSObject -Property $props
    
    #or
    
    [pscustomobject]$props
    

    Using the `n is basically appears to make the entire line a compare string. It's a common way in SQL to compare multiple rows as a basic unique identifier. The `n seems a bit kludgy to me. Possibly try this method which uses a calculated expression to accomplish the same thing (using my array examples previously):

    $array1 = $array1 | Select PackageID, VersionID, @{Name="CompareString";Expression={"{0}{1}" -f $_.PackageID, $_.VersionID}}
    $array2 = $array2 | Select PackageID, VersionID, @{Name="CompareString";Expression={"{0}{1}" -f $_.PackageID, $_.VersionID}}
    
    Compare-Object -ReferenceObject $array1 -DifferenceObject $array2 -Property CompareString -PassThru |
    Select PackageID, VersionID
    
  • #44931

    Participant
    Points: 0
    Rank: Member

    The calculated expression works beautifully. a nice creative solution. thanks for that!

  • #45022

    Participant
    Points: 0
    Rank: Member

    One open issue still,

    I have an array

    PS G:\> $NotMatchingArrayItems
    
    PackageID                                                                 Versionid                                                                 CompareString                                                           
    ---------                                                                 ---------                                                                 -------------                                                           
    29E49811-1214-4BBF-84CD-8AE9C5A4809F                                      E8E0CD14-469F-4FEC-AB07-A6393FC478F5                                      29E49811-1214-4BBF-84CD-8AE9C5A4809F-E8E0CD14-469F-4FEC-AB07-A6393FC4...
    4F30650F-3F12-4D8E-9333-40F581CA431E                                      64F2D58F-8349-4A46-B1E0-220621F07B2F                                      4F30650F-3F12-4D8E-9333-40F581CA431E-64F2D58F-8349-4A46-B1E0-220621F0...
    58E4DB62-67D3-40CA-9051                                                   58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                      58E4DB62-67D3-40CA-9051-58E4DB62-67D3-40CA-9051-31FD54A0AB1D            
    E0F1D187-93A6-42D1-96BF-72735E442B65                                      625EED06-E466-4C50-B264-C9D6FE6B8314                                      E0F1D187-93A6-42D1-96BF-72735E442B65-625EED06-E466-4C50-B264-C9D6FE6B...
    EF905FCD-E406-4FC3-9393-C421936419BE                                      7DFA2667-1DBC-453D-AD64-29C17A8E7081                                      EF905FCD-E406-4FC3-9393-C421936419BE-7DFA2667-1DBC-453D-AD64-29C17A8E...
    

    and another one '$AppvServerPackages';

    PackageGuid                                                                         Name                                                                              
    -----------                                                                         ----                                                                              
    29e49811-1214-4bbf-84cd-8ae9c5a4809f                                                CDM_Light_1.4.7                                                                   
    ef905fcd-e406-4fc3-9393-c421936419be                                                DWG_TrueView_2016                                                                 
    e0f1d187-93a6-42d1-96bf-72735e442b65                                                Gemba_OEE_Client_UK                                                               
    

    And now I want the $NotMatchingArrayItems array to be supplemented with an extra column 'Name' where I add the corresponding Name from the array $AppvServerPackages, when then PackageId and Packageguid match. I used the following, which didn't work...

    PS G:\> $NotMatchingArrayItems | select PackageID, VersionID, @{Name="Name";Expression={"{0}" -f $(%{$AppvServerPackages | where {$NotMatchingArrayItems.Packageid -like $_.Packageguid}}).Name}}
    
    
    PackageID                                                                 Versionid                                                                 Name                                                                    
    ---------                                                                 ---------                                                                 ----                                                                    
    29E49811-1214-4BBF-84CD-8AE9C5A4809F                                      E8E0CD14-469F-4FEC-AB07-A6393FC478F5                                      CDM_Light_1.4.7                                                         
    4F30650F-3F12-4D8E-9333-40F581CA431E                                      64F2D58F-8349-4A46-B1E0-220621F07B2F                                      CDM_Light_1.4.7                                                         
    58E4DB62-67D3-40CA-9051                                                   58E4DB62-67D3-40CA-9051-31FD54A0AB1D                                      CDM_Light_1.4.7                                                         
    E0F1D187-93A6-42D1-96BF-72735E442B65                                      625EED06-E466-4C50-B264-C9D6FE6B8314                                      CDM_Light_1.4.7                                                         
    EF905FCD-E406-4FC3-9393-C421936419BE                                      7DFA2667-1DBC-453D-AD64-29C17A8E7081                                      CDM_Light_1.4.7                                                         
    

    Suggestions?

  • #45113

    Participant
    Points: 638
    Helping Hand
    Rank: Major Contributor

    There are numerous ways to do it. I'll start with a "it depends". If you're dealing with a couple of records, you can do something simple, again with calculated expressions:

    $array1 = @()
    $array1 += [pscustomobject]@{PackageID="29E49811-1214-4BBF-84CD-8AE9C5A4809F";VersionId="122600F0-34E0-4F3C"}
    $array1 += [pscustomobject]@{PackageID="4F30650F-3F12-4D8E-9333-40F581CA431E";VersionId="E69AE4CF-C3E1-410C"}
    $array1 += [pscustomobject]@{PackageID="E0F1D187-93A6-42D1-96BF-72735E442B65";VersionId="E77EC859-E340-4761"}
    $array1 += [pscustomobject]@{PackageID="EF905FCD-E406-4FC3-9393-C421936419BE";VersionId="E77EC859-E340-4761"}
    
    $array2 = @()
    $array2 += [pscustomobject]@{PackageGUID="29e49811-1214-4bbf-84cd-8ae9c5a4809f";Name="CDM_Light_1.4.7"}
    $array2 += [pscustomobject]@{PackageGUID="ef905fcd-e406-4fc3-9393-c421936419be";Name="DWG_TrueView_2016 "}
    $array2 += [pscustomobject]@{PackageGUID="e0f1d187-93a6-42d1-96bf-72735e442b65";Name="Gemba_OEE_Client_UK"}
    
    $final = $array1 |
             Select PackageID, VersionID, @{Name="Name";Expression={$pkgID = $_.PackageID;$array2 | Where {$_.PackageGUID -eq $pkgID} | Select -ExpandProperty Name}}
    
    $final
    

    Your results would look something like this:

    PackageID                            VersionId          Name               
    ---------                            ---------          ----               
    29E49811-1214-4BBF-84CD-8AE9C5A4809F 122600F0-34E0-4F3C CDM_Light_1.4.7    
    4F30650F-3F12-4D8E-9333-40F581CA431E E69AE4CF-C3E1-410C                    
    E0F1D187-93A6-42D1-96BF-72735E442B65 E77EC859-E340-4761 Gemba_OEE_Client_UK
    EF905FCD-E406-4FC3-9393-C421936419BE E77EC859-E340-4761 DWG_TrueView_2016  
    

    Note if the lookup fails, you simply get a null value. If you are working with large data tables, you should leverage Join-Object, which is a function that several folks have written:

    Cookie Monster
    Powershell Team

    I've used Join-Object for 20-30k records. If you're dealing with extremely large datasets, then you should leverage SQL to do complex joins.

The topic ‘compare two multip column araays’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort