Comparing multiple columns across 2 .csv files

This topic contains 7 replies, has 3 voices, and was last updated by Profile photo of Colter Colter 5 months, 1 week ago.

  • Author
    Posts
  • #64197
    Profile photo of Colter
    Colter
    Participant

    Hi Folks,

    So I have a user access report and a key file (.CSVs) that I'm trying to compare. Here is an example of the data:

    users.csv
    Username,jobtitle,permission1,permission2,permission3,permission4,permission5
    bill,wizard,0,0,0,1,3
    jack,archer,1,0,0,2,4
    barb,healer,0,1,1,0,0

    key.csv
    jobtitle,permission1,permission2,permission3,permission4,permission5
    wizard,0,0,0,0,3
    archer,1,0,0,2,4
    healer,1,1,1,1,1

    The output needs to look like the following. I'm trying to get a simplified report that associates a user's permissions to their title while also showing "wildcard" permissions:

    Username,jobtitle,permission1,permission2,permission3,permission4,permission5,titlematch
    bill,wizard,,,,1,,wizard
    jack,archer,,,,,,archer
    barb,healer,0,,,0,0,healer

    In the actual data there are 20,000 users, 500 titles, and 120 permissions

    Here is what I have so far. It only outputs exact matches which isn't really helpful. I've been wondering if I'm even close on this.

    Any help would be greatly appreciated. Cheers,

     
    $Data = import-csv "C:\users.csv"
    $Key = import-csv "C:\key.csv" 
    
    $permissions    = permission1,permission2,permission3,permission4,permission5
    
    Foreach ($D in $Data){
        foreach ($K in $Key){
            if($D."JobTitle" -eq $K."JobTitle"){
                Compare-Object -ReferenceObject $D -DifferenceObject $K -Property $permissions -IncludeEqual -ExcludeDifferent -PassThru |
                Select-Object * -ExcludeProperty SideIndicator | 
                Export-Csv "C:\results.csv" -Append -notypeinformation
                }
            }
        }
  • #64212
    Profile photo of random commandline
    random commandline
    Participant

    What do you mean by 'wildcard' permissions? Do you need output like this?
    You asked a similar question sometime ago. https://powershell.org/forums/topic/match-multiple-csv-columns/
    bill,wizard,0,0,0,1,3,wizard,0,0,0,0,3
    jack,archer,1,0,0,2,4,archer,1,0,0,2,4
    barb,healer,0,1,1,0,0,healer,1,1,1,1,1

  • #64255
    Profile photo of Ron
    Ron
    Participant

    This should do it, and adapt to the actual data.

    $u=@'
    Username,jobtitle,permission1,permission2,permission3,permission4,permission5
    bill,wizard,0,0,0,1,3
    jack,archer,1,0,0,2,4
    barb,healer,0,1,1,0,0
    '@
    
    $k=@'
    jobtitle,permission1,permission2,permission3,permission4,permission5
    wizard,0,0,0,0,3
    archer,1,0,0,2,4
    healer,1,1,1,1,1
    '@
    
    $users=$u|convertfrom-csv
    $keys=$k|convertfrom-csv
    
    function Perm_ary {
      param ($obj)
    
      $obj | Get-Member | ?{$_.name -like 'perm*'} | %{$obj[0].($_.name)}
    
    }
    
    $keyhash = @{}
    
    $keys | %{$keyhash.($_.jobtitle) = Perm_ary($_)}
    
    $out = ForEach ($user in $users) {
    
      if ($keyhash.($user.jobtitle)) {
        $obj = New-Object –TypeName PSObject
        $obj | Add-Member –MemberType NoteProperty –Name Username   -value $user.Username
        $obj | Add-Member –MemberType NoteProperty –Name jobtitle   -value $user.jobtitle
    
        $ary = Perm_ary($user)
        for ($i=0;$i -lt $ary.Count;$i++) {
          $p = $null
          if ($ary[$i] -ne $keyhash.($user.jobtitle)[$i]) {
            $p = $ary[$i]
          }
        $obj | Add-Member –MemberType NoteProperty –Name $('Permission' + ($i + 1))   -value $p
        }
    
        $obj | Add-Member –MemberType NoteProperty –Name jobmatch   -value $user.jobtitle
    
        $obj
      }
    }
    
    $out|convertto-csv -NoTypeInformation
    
    "Username","jobtitle","Permission1","Permission2","Permission3","Permission4","Permission5","jobmatch"
    "bill","wizard",,,,"1",,"wizard"
    "jack","archer",,,,,,"archer"
    "barb","healer","0",,,"0","0","healer"
    
    • #64266
      Profile photo of Colter
      Colter
      Participant

      Thank you Ron,

      I should have mentioned that the permission names are more like "FEE","CEC","EWQ","VRR","YUI" but I should be able to figure out how to adapt your script.

    • #64270
      Profile photo of Ron
      Ron
      Participant

      That will make it a bit more complex. Assuming they are the same and in the same order in both files, you can store the permission names in an array and use that to select/compare.

    • #64281
      Profile photo of Colter
      Colter
      Participant

      They are and I'll give the array a try. I guess another way I could do it is to just rename the headers when I import the file then change them back later.

  • #64284
    Profile photo of Ron
    Ron
    Participant

    Fixing the header later feels like cheating... 🙂 But this small change would make it work. Same output as before. You'd only need to change the headers after.

    $notperm='Username','jobtitle'
    function Perm_ary {
      param ($obj)
    
      $obj | Get-Member -MemberType NoteProperty | ?{$notperm -notcontains $_.name} | %{$obj[0].($_.name)}
    
    }
    
    • #64300
      Profile photo of Colter
      Colter
      Participant

      Thanks! Everything is working. I just need to include an else statement to catch users with job titles that are not in the key.

You must be logged in to reply to this topic.