add users to security groups from excel spreadsheet

Hey,

If you're going for PowerShell, grab these first:

http://www.quest.com/activeroles-server/arms.aspx

And if you can drop it to a CSV file, because Excel is just unnecessary effort.

Could you tell us the format for "List of Security Groups"?

I'll use a semi-colon delimited list as an example. You could run...

ForEach ($Entry in (Import-CSV "TheFile.csv)) {
  ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }
}

Both columns would have to contain unique identifiers for each group / user. And the above is based on a CSV formatted like this:

ColumnA,ColumnB
username,group1;group2;group3

HTH

Chris

Hi Chris-Dent:

Thanks so much much for your response - This looks like exactly what I am looking for.
I am just install the quest tools so will be running the script on test shortly.

As for format of the list of groups, it will literally be:
ColumnA   | ColumnB
user1        | Systems Group, DBA_group, Finance etc
user2        | All_Users, WeB_group, invoices etc
user3        | Helpdesk_Group, MAIL_group, Finance etc

So I just place a comma after the user name I.e USER1,
The separate the groups with a semi-colon I.e GROUP1;Group2;Group3;etc ???

Is that correct?

Again, thanks so much for your time/code - very appreciated.

Cheers
Bry

Save it as .CSV in Excel. Once done, if you open up the file with Notepad you'll see the format I mean. PowerShell can natively read that where reading Excel is far more trouble than it's worth.

You'll need to give each column a name, otherwise PowerShell will use the first user / group combination as the column names.

And a comma delimited list for the groups is fine, we should find that Excel sticks quotes around them when you save it to CSV, making it:

user1,"Systems Group,DBS_Group,Finance"

Then we'd modify the code above to split on "," rather than ";":

ForEach ($Entry in (Import-CSV "TheFile.csv)) {
  ($Entry.ColumnB).Split(",") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }
}

If you want to see what PowerShell does when it reads the CSV just run:

Import-CSV "TheFile.csv"

That'll give you an Object which represents the contents of the file. It's that object we loop through to execute the commands.

Chris

Chris

that's brilliant..! - I'll give this a try and get back to you as soon as I can...
Thanks so much

Cheers
Bry

Hi

Getting this error when I run the script:

PS C:\> C:\Scripts\Test_Data\usr2grp.ps1
Encountered end of line while processing a string token.
At C:\Scripts\Test_Data\usr2grp.ps1:2 char:28
+   ($Entry.ColumnB).Split("," <<<< ) | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }

The import-csv comes back fine with the columns and the user and groups showing...

Do you happen to know what this may be?

Cheers
Bry

Hmm End of Line isn't expected there. Can you pop a sample of the CSV file you have at the moment? Can't quite see how it ended up with that particular error.

Chris

sure, no problem

BTW - I had to save it as .xls as EE didn't except .csv upload <8-|)
attached is the csv (.xls) file - If I run import-csv I get the relevant user and groups in the respective columns A & B...

Cheers
Bry
IMP1.xls

Ahh okay, back to the semi-colon delimited list. Just needs this bit changing:

($Entry.ColumnB).Split("," <<<< )

To this:

($Entry.ColumnB).Split(";")

Then it can loop through each of the items in ColumnB for us.

Chris

Hi Chris

Sorry - I am getting this error now:

PS C:\> C:\Scripts\Test_Data\usr2grp.ps1
Missing closing ')' in expression.
At C:\Scripts\Test_Data\usr2grp.ps1:5 char:27
+   ($Entry.ColumnB).Split("; <<<< ") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }


The .ps1 is as follows:
ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv)) {
  ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }
}


Cheers
Bry

Hi Chris

Apologies for delay in getting back to you, a production issue came up..!
I have now tested this again and brilliant, it works like a dream :-)

This is answered and I will accept your solution to the original question. I just wondered, how difficult it would be to reverse this so that in columnA I would have a group name and in columnB I would have users - so in affect, I would then run the script and have the group populated with the users..

I hope this makes sense and also, if I need to open a new question, I would be happy to do so.
as i say, I accept your fantastic solution.

thanks so much for your time.

Cheers
Bry

Aye, it does. Not much bother to change it:

ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv")) {
  ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember $Entry.ColumnA -Member $_ }
}

It's pretty flexible about the values you can use, so it should be fine with that.

Chris

Hi Chris

Thanks so much for this, I am extrememly grateful.

 I just ran the script and I get the following error:

PS C:\> C:\Scripts\Test_Data\grp2usr.ps1
Add-QADGroupMember : Cannot resolve DN for the given identity: 'it_systems_staff_global;org_I
At C:\Scripts\Test_Data\grp2usr.ps1:2 char:54
+   ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember  <<<< $Entry.ColumnA -Member $_ }

I am sure it is to do with the ";" - How do I use this split in columnA also? (I would like to keep the ";" split in ColumnB as well, so that I can add multiple users :-)

Thanks again
Bry

Ahh sorry, didn't realise both were now semicolon separated.

Are you sure you're not making it a bit complicated by having both fields with 2 dimensions? :)

Anyway, I wonder if this might do it. It's a bit less effort (for AD) than the other version (or at least we've moved the effort elsewhere).

Chris

ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv")) {
  ForEach ($Group in ($Entry.ColumnA.Split(";")) {
    Add-QADGroupMember $Group -Member $($Entry.ColumnB.Replace(";", ","))
  }
}

Select allOpen in new window

Hi Chris

Sorry - I was being a bit of a dufus..! It was exactly as you said and had provided the solution for - I just wanted the single group in 1 column and then the split of users in the other...
My apologies <*blush*>.
Tested and is working 100%.

I will use the other code snippit as well but for now, you have made my day..!

Thanks so much for your valuable time on helping me find a solution. Your a legend in my books..!

Cheers
Bry

Glad I could help :)

Chris

This is what I've been looking for however, my needs are much more simple.  I have a csv file with column A having the single security group and column B with the username.  I've tried fiddling with the script above and I just don't have the skills.  Can you help me out, Chris-Dent?

Sure, can you give me a sample of what you have?

Chris

Hello, I have the exact same requirements as the person that posted 2009-09-17. I also only have one security group that I would like to add my users to.
I'm completely new to powershell. I installed the activeroles tool on my domain controller.
Do I just open up powershell and paste in the command from above?

ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv")) {
  ForEach ($Group in ($Entry.ColumnA.Split(";")) {
    Add-QADGroupMember $Group -Member $($Entry.ColumnB.Replace(";", ","))
  }
}
 
Of course I modified it so that it would take the name and path of my csv file.