Comparing multiple columns across 2 .csv files

PowerShell Help
1

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
            }
        }
    }
2

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

3

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"
4

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.

5

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.

6

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.

7

Fixing the header later feels like cheating… :slight_smile: 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)}

}
8

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