How to map column to another column value from csv file?

Question

Suppose i have a csv file as follows:

role    member
role1   memberA
role1   memberB
role2   memberC
role3   memberX
role3   memberY
role3   memberC

I want to add to a database all members to the roles on the database

$csvR = (Import-Csv $csvFile).role

        foreach ($Role in $csvR)
        {
            foreach ($Member in $Role) 
            {   
                & ".\AddMembers.ps1" -AddtoDatabaseOnOneServer "$Role" "$Member" "$DatabaseName" "$ServerInput"
            } 
        }

Will this loop recognize for example to add memberA to role1, memberB to role1, memberC to role2, etc.? in other words, is this inner loop correct: foreach ($Member in $Role)

Have you tried it? The objects output by Import-Csv represent each row of the input CSV with a String property for each column. It's not going to create any collections or do any further interpretation/association unless you instruct it to. Further, by setting $csvR to .role you're picking off the Role property and discarding the rest of the object. — Lance U. Matthews
– Lance U. Matthews, Commented May 22, 2019 at 22:08
take a look at the Group-Object cmdlet help. it can group the objects from your Import-CSV by the .Role property and then you can iterate thru the resulting groups to assign the .Member to the .Role. — Lee_Dailey
– Lee_Dailey, Commented May 22, 2019 at 22:12
@Lee_Dailey suppose i do this: Import-Csv "E:\foo.csv"| Group-Object Role,Member. how do i assign the values? and after assigning the values, how do i use it in the loop? — Cataster
– Cataster, Commented May 22, 2019 at 22:25
@Cataster - take a look at what you have after you save the grouped stuff to a $Var. it'll be an array of objects & each will have a .Group that has all the matching objects from the CSV import in it. you can iterate thru that ... [grin] ///// if you replace that nasty, icky, useless picture of your sample data with several lines from your source, i could do a demo of the idea. i won't bother if you won't bother to post usable sample data, tho. [frown] — Lee_Dailey
– Lee_Dailey, Commented May 22, 2019 at 22:30
You could just do one loop and access the roles and members as properties: $csvR = Import-Csv $csvFile; foreach ($row in $csvR) { $row.role; $row.member }. If your AddMembers.ps1 script only takes a single role and single member as a parameter, you will be doing some sort of iteration. — AdminOfThings
– AdminOfThings, Commented May 22, 2019 at 22:33

Lee_Dailey · Accepted Answer · 2019-05-22 23:00:30Z

1

here's a demo of how to use the Group-Object cmdlet to iterate thru the items in an imported CSV file by a grouped property. [grin]

at the give person line, you can either add your call to your script for each person OR gather the names of all the persons for that .Role and add them all at once.

# fake reading in a CSV file
#    in real life, use Import-CSV
$InStuff = @'
role, member
role1, memberA
role1, memberB
role2, memberC
role3, memberX
role3, memberY
role3, memberC
'@ | ConvertFrom-Csv

$GroupedIS = $InStuff |
    Group-Object -Property Role

foreach ($GIS_Item in $GroupedIS)
    {
    foreach ($GG_Item in $GIS_Item.Group)
        {
        'give person [ {0} ] the role [ {1} ]' -f $GG_Item.Member, $GG_Item.Role
        }
    }

output ...

give person [ memberA ] the role [ role1 ]
give person [ memberB ] the role [ role1 ]
give person [ memberC ] the role [ role2 ]
give person [ memberX ] the role [ role3 ]
give person [ memberY ] the role [ role3 ]
give person [ memberC ] the role [ role3 ]

answered May 22, 2019 at 23:00

Lee_Dailey

7,5292 gold badges25 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cataster Over a year ago

nice, im trying it out!

Lee_Dailey Over a year ago

@Cataster - it is a really nifty cmdlet ... and you can group by multiple properties ... and you use calculated properties, too. [grin]

Lee_Dailey Over a year ago

@Cataster - you are most welcome! glad to have helped ... [grin]

Collectives™ on Stack Overflow

How to map column to another column value from csv file?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related