Adding names from a spreadsheet into AD groups - solved

[enjay]

Sharing this in case any of you ever get asked to do the same thing:

 

I'd been given a load of spreadsheets containing student names and the different AD groups they need to go in - normally, we sync these from SIMS but these particular groups don't exist in SIMS. Since it was every student in the school, I wasn't about to start entering them manually (a task which I reckon would have taken at least 4 hours), so I got creative. Here's what I did:

 

1 - add an extra column to the spreadsheet which uses text-to-columns and/or =concatenate formula to get the students' names into the same format as their AD display names, let's call this column G for this example

2 - add another column next to this (H for this example) which contains the formula =G1&";" in the first row, cell H1 in this example

3 - in cell H2, enter the formula =H1&G2&";"

4 - copy the formula from H2 down into the rest of the column

5 - cell H50, or whatever the last row is, will read Name 1;Name 2;...Name49;Name50

6 - copy that text and paste it into the "add members" search box in the AD group

 

Using this method, I added all 850 students to one of 30 groups in around 1.5 hours, a task I reckon would have taken at least 4 hours to do if entering each student in turn.

[Domino]

For reference, once the AD names and Groups were in a csv you could have done a quick powershell to run them all in.

 

EG, in groups.csv with columns labeled UserName and GroupName:

 

Import-Csv groups.Csv | Foreach-Object{

Add-ADGroupMember -identity $_.GroupName -user $_.UserName

}

Edited February 14, 2018 by Domino

[enjay]

For reference, once the AD names and Groups were in a csv you could have done a quick powershell to run them all in.

 

EG, in groups.csv with columns labeled UserName and GroupName:

 

I didn't have their usernames though, only their real names.

 

Powershell is one of those things I've never spent the time to learn. I don't do much manglement in AD anyway (especially since buying Salamander), so it has never been a priority for me.

[Domino]

I was sharing that for reference in case anyone else needed it...

[enjay]

I was sharing that for reference in case anyone else needed it...

 

Definitely worth it. It would be even quicker than the method I adopted.

[JRA]

I use Wisesoft's Bulk AD Users for stuff like this. Defo worth a gander, but for goodness' sake un-tick the force password change and generate password tickboxes before you do ANYTHING in it:

 

Bulk AD Users

 

And with ANUC back in the day (we used to use this, and the above to mass-change groups etc) we'd import our new starters:

 

https://mediarealm.com.au/articles/bulk-import-users-to-active-directory-with-powershell-script-anuc-ps1/

Edited February 21, 2018 by JRA

[Alis_Klar]

I love the Wisesoft tools but they don't seem to have been updated since 2009 and don't think they work with anything newer than Excel 2010. Am I right?

[3s-gtech]

Work fine with Office 2016 IME.