Matching a range of cells between 2 .CSVs

Tagged: 

This topic contains 2 replies, has 3 voices, and was last updated by Profile photo of Al Al 3 months ago.

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

    Hi Folks,

    I have a csv containing usernames and individual permissions that I need to compare to another CSV that defines permissions into roles. Like so:

    role_key.csv

    Title,A,B,C,D,E
    CSR_1,0,0,0,1,3
    CSR_2,1,0,0,1,3
    CSR_3,0,2,0,1,3
    CSR_4,1,0,1,1,3

    User_Access.csv

    Users,A,B,C,D,E
    User_1,1,0,0,1,3
    User_2,1,0,5,1,3
    User_3,0,0,0,1,3
    User_4,1,0,1,1,3

    The output I am looking for is:

    User_1,CSR_2
    User_2,No Match
    User_3,CSR_1
    User_4,CSR_4

    The actual CSVs are kinda big (118 individual permissions, 540 roles, and 5000 users). Is there a way I can do this without defining each role in the script?

    Thanks!

  • #60514
    Profile photo of Ron
    Ron
    Participant

    I would combine those permissions into 1 string and use that as a hash key, store the title. Then you can lookup the title as you loop through the users.

  • #60520
    Profile photo of Al
    Al
    Participant
    gc '.\role.csv'  |  select -skip 1  |  %{ $h=@{} }{
        $v,$k=$_  -split ',',2
        $h.$k=$v
    }
    
    gc '.\users.csv'  |  select -skip 1  |  %{
        $u,$x=$_  -split ',',2
        if( $h.$x ){ "$u;$($h.$x)" }else{ "$u;No Match" }
    }
    

You must be logged in to reply to this topic.