Match multiple .csv columns

Welcome Forums General PowerShell Q&A Match multiple .csv columns

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

 
Participant
1 year, 10 months ago.

  • Author
    Posts
  • #62058

    Participant
    Points: 0
    Rank: Member

    Hi Folks,

    I have 2 .csv files that I am trying to compare multiple fields in. One is the users with their application permissions and the other is the application roles with what permissions each role should contain. What I need to do is check the user's title against the role names and if there is a match then compare all the permissions for that role against what the user actually has, if that matches then export that user to a new report.

    I've tried a few different scripts like this one, but I can't seem to get the output I'm looking for.

    $Users = Import-Csv C:\users.csv
    $Roles = Import-Csv C:\roles.csv
    
    foreach ($Row in $Users){
        foreach ($item in $roles){
            If (($row.JobTitle -eq $item.RoleName) -and
               ($row.Permission1 -eq $item.permission1) -and
               ($row.Permission2 -eq $item.permission2) -and
               ($row.Permission3 -eq $item.permission3) -and
               ($row.Permission4 -eq $item.permission4) -and
               ($row.Permission5 -eq $item.permission5)) {
                    $Report = [PSCustomObject] @{
    'FirstName'          = $row.'FirstName'
    'LastName'           = $row.'LastName'
    'UserID'             = $row.'UserID'
    'JobTitle'           = $row.'JobTitle'
    'DOMAIN'             = $row.'DOMAIN'
                }
            }
        }
    $Report | Export-Csv C:\report.csv -Delimiter "," -NoTypeInformation -append
    }
    

    Here is some example data. I know I can use where-object to compare the roles one at a time, but there are over 600 roles so I'd like to avoid that if possible.

    Users.csv

    FirstName,LastName,UserID,JobTitle,DOMAIN,Permission1,Permission2,Permission3,Permission4,Permission5
    Jon,Doe,User1,Buyer,AA,0,2,1,1,3
    Jan,Doe,User2,Administrator,AA,0,2,1,1,1
    William,Doe,User3,Vice President,AA,0,0,0,0,1
    Deana,Doe,User4,Quality Analyst,AA,0,1,1,1,3
    Diana,Doe,User5,Planning Lead,AA,1,2,1,1,3
    Erika,Doe,User6,Supply Planner,AA,0,0,0,0,3
    Gregory,Doe,User7,Inventory Manager,AA,0,2,1,1,1
    John,Doe,User8,Delivery Specialist,AA,0,0,0,0,0
    Jeffrey,Doe,User9,Project Manager,AA,1,2,1,1,3
    Jeff,Doe,User10,Operations Manager,AA,0,2,1,1,3
    Jennifer,Doe,User11,Planning Lead,AA,0,2,1,1,1
    Jefrey,Doe,User12,Branch Manager,AA,1,0,0,0,1

    Roles.csv

    RoleName,Permission1,Permission2,Permission3,Permission4,Permission5
    Buyer,0,2,1,1,3
    Administrator,0,2,1,1,1
    Vice President,0,0,0,0,1
    Quality Analyst,0,2,1,1,3
    Planning Lead,1,2,1,1,3
    Supply Planner,0,0,0,0,3
    Inventory Manager,0,2,1,1,1
    Delivery Specialist,0,0,0,0,0
    Project Manager,1,2,1,1,3
    Operations Manager,0,2,1,1,3
    Planning Lead,0,2,1,1,1
    Branch Manager,1,0,0,0,1

    If done correctly Deana Doe should be excluded from the report.

  • #62085

    Participant
    Points: 10
    Rank: Member

    During my test, it took about 2 seconds to compare 700 objects, (1) role and (1) user, Deanna Doe, was excluded from results.

    # Make sure each object has same headers
    $Users = Import-Csv C:\users.csv
    $Roles = Import-Csv C:\roles.csv | Select-Object *,@{n='JobTitle';exp={$_.RoleName}} -ExcludeProperty Rolename
    $headers = 'JobTitle','Permission1','Permission2','Permission3','Permission4','Permission5'
    
    # Compare jobtitle and permissions
    Compare-Object -ReferenceObject $Users -DifferenceObject $Roles -Property $headers -IncludeEqual -ExcludeDifferent -PassThru | Select-Object * -ExcludeProperty SideIndicator | Export-Csv C:\user_results.csv -NoTypeInformation
    
  • #63267

    Participant
    Points: 0
    Rank: Member

    Thanks!

The topic ‘Match multiple .csv columns’ is closed to new replies.