Bulk Creating User accounts from csv and adding them to groups

I have an open question regarding this for using ActiveRoles Management Shell but would entertain any method for accomplishing this task.  Any good scripts for also adding the users to specified groups?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi, here's a VBScript that would do that, but from an Excel file instead of CSV.  It requires that Excel be installed on the system running the script.
The excel format is
Column A: First Name
Column B: Last Name
Column C: Username
Column D: Password

It will also set the account password to never expire.


If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
	strPath = Wscript.ScriptFullName
	strCommand = "%comspec% /k cscript  """ & strPath & """"
	Set objShell = CreateObject("Wscript.Shell")
	objShell.Run(strCommand), 1, True
End If

' Bind to Active Directory.
Set objRootLDAP = GetObject("LDAP://rootDSE")

' Path to Excel file
strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Users.xls"
' Path to the OU to create the users in
strOUPath = "OU=New Users,OU=Main Office,OU=Sites," & objRootLDAP.Get("defaultNamingContext")
strNewGroup = "CN=NewUsersGroups,OU=Main Office,OU=Sites," & objRootLDAP.Get("defaultNamingContext")

Const xlUp = -4162
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open strExcelFile

Set objNetwork = CreateObject("WScript.Network")
strDomainName = objNetwork.UserDomain

WScript.Echo "There are " & objExcel.ActiveSheet.Cells(65536, "A").End(xlUp).Row & " rows in the worksheet."

For intRow = 2 To objExcel.ActiveSheet.Cells(65536, "A").End(xlUp).Row

	strFirstName = Trim(objExcel.ActiveSheet.Cells(intRow, "A").Value)
	strLastName = Trim(objExcel.ActiveSheet.Cells(intRow, "B").Value)
	strUserName = LCase(Trim(objExcel.ActiveSheet.Cells(intRow, "C").Value))
	strPassword = LCase(Trim(objExcel.ActiveSheet.Cells(intRow, "D").Value))

	If strFirstName <> "" And strLastName <> "" And strUserName <> "" Then
		' This will add the user to eg. Domain.Local\Users
		Set objContainer = GetObject("LDAP://" & strOUPath) 
		' Check if the user already exists
		strDNCheck = ""
		strLoginCheck = ""
		strDNCheck = Get_LDAP_User_Properties("user", "distinguishedName", "cn=" & strFirstName & " " & strLastName & "," & strOUPath, "adsPath")
		strLoginCheck = Get_LDAP_User_Properties("user", "samAccountName", strUsername, "adsPath")
		If InStr(strDNCheck, "LDAP://") > 0 Or InStr(strLoginCheck, "LDAP://") > 0 Then
			If InStr(strDNCheck, "LDAP://") > 0 Then
				WScript.Echo "User already exists: " & "cn=" & strFirstName & " " & strLastName & "," & strOUPath
				WScript.Echo "Login name already exists: " & strUsername & vbCrLf & "for " & strLoginCheck
			End If
			' Build the actual User.
			' Attributes listed here: http://support.microsoft.com/kb/555638
			Set objNewUser = objContainer.Create("User", "cn= " & strFirstName & " " & strLastName)
			If InStr(strUserName, "@") > 0 Then
				arrDomUserName = Split(strUserName, "@")
				strUserName = arrDomUserName(0)
				strSuffix = arrDomUserName(1)
				strUserName = strUserName
				strSuffix = Replace(Replace(objRootLDAP.Get("defaultNamingContext"), ",", "."), "DC=", "")
			End If
			objNewUser.Put "userPrincipalName", strUserName & "@" & strSuffix
			objNewUser.Put "sAMAccountName", strUserName
			objNewUser.Put "givenName", strFirstName
			objNewUser.Put "sn", strLastName
			objNewUser.Put "displayName", strFirstName & " " & strLastName
			objNewUser.SetPassword strPassword
			objNewUser.AccountDisabled = False

			intUserAccountControl = objNewUser.Get("userAccountControl") 
			If Not objNewUser.userAccountControl And ADS_UF_DONT_EXPIRE_PASSWD Then
				objNewUser.Put "userAccountControl", objNewUser.userAccountControl Xor ADS_UF_DONT_EXPIRE_PASSWD
			End If
			Set objNewGroup = GetObject("LDAP://" & strNewGroup)

			WScript.Echo strFirstName & " " & strLastName & " created with login name " & strUserName
		End If
	End If

WScript.Echo "Done"
objExcel.ActiveWorkbook.Close False
Set objExcel = Nothing

Function Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
      If InStr(strObjectToGet, "\") > 0 Then
            arrGroupBits = Split(strObjectToGet, "\")
            strDC = arrGroupBits(0)
            strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
            strObjectToGet = arrGroupBits(1)
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDNSDomain = objRootDSE.Get("defaultNamingContext")
      End If
      strDetails = ""
      strBase = "<LDAP://" & strDNSDomain & ">"
      ' Setup ADO objects.
      Set adoCommand = CreateObject("ADODB.Command")
      Set ADOConnection = CreateObject("ADODB.Connection")
      ADOConnection.Provider = "ADsDSOObject"
      ADOConnection.Open "Active Directory Provider"
      adoCommand.ActiveConnection = ADOConnection
      ' Filter on user objects.
      'strFilter = "(&(objectCategory=person)(objectClass=user))"
      strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"
      ' Comma delimited list of attribute values to retrieve.
      strAttributes = strCommaDelimProps
      arrProperties = Split(strCommaDelimProps, ",")
      ' Construct the LDAP syntax query.
      strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      adoCommand.CommandText = strQuery
      adoCommand.Properties("Page Size") = 100
      adoCommand.Properties("Timeout") = 30
      adoCommand.Properties("Cache Results") = False
      ' Run the query.
      Set adoRecordset = adoCommand.Execute
      ' Enumerate the resulting recordset.
      Do Until adoRecordset.EOF
          ' Retrieve values and display.
          For intCount = LBound(arrProperties) To UBound(arrProperties)
                If strDetails = "" Then
                      strDetails = adoRecordset.Fields(intCount).Value
                      strDetails = strDetails & vbCrLf & adoRecordset.Fields(intCount).Value
                End If
          ' Move to the next record in the recordset.
      ' Clean up.
      Get_LDAP_User_Properties = strDetails
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlakeISSAuthor Commented:
Hi Rob,
This is great but I would also need it to be able to add the users to groups as well.
Are all users going to the same groups or different groups for each user?
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

BlakeISSAuthor Commented:
Sorry, I should have indicated that.  The users would be going into the same groups.

It looks like Rob's script does include adding the users to a group.  Line 17 is the group DN and Lines 83-85 actually add the new user to the group.

If you need to add to more groups, you could duplicate line 17 - using a new variable name and duplicate lines 83-85 changing to the new variable name.

Let me know if you have any questions.
Sorry, I didn't mention that, but it is in the code as was indicated.  This is the DN of the new group to add users to:
strNewGroup = "CN=NewUsersGroups,OU=Main Office,OU=Sites," & objRootLDAP.Get("defaultNamingContext")


BlakeISSAuthor Commented:
Do you have an example of what the group would look like?

Assume your group is named New Users Group, and is in the following OU in ADUC:
domain.com\Sites\Main Office\

You then reverse the OU structure for the LDAP query, and have strNewGroup like so:
strNewGroup = "CN=New Users Group,OU=Main Office,OU=Sites," & objRootLDAP.Get("defaultNamingContext")

The defaultNamingContext part takes care of the domain.com specifics, but that translates to DC=domain,DC=com

One note, if the group if you're adding to is located in the *default* Users container in ADUC, then it would be:
strNewGroup = "CN=NewUsersGroups,CN=Users," & objRootLDAP.Get("defaultNamingContext")

because for some reason, the default Users container is a "container" rather than an "organization unit", hence the CN= instead of the OU=


BlakeISSAuthor Commented:
In putting together the script with all input, this solution worked.  I am going to tweak it in hopes of being able to add in multiple groups.  
Thanks Much!
Great. Thanks for the grade.

To add more groups, change this:
strNewGroup = "CN=NewUsersGroups,OU=Main Office,OU=Sites," & objRootLDAP.Get("defaultNamingContext")

Open in new window

to this:
arrNewGroups = Array( _
   "CN=NewUsersGroup1,OU=Main Office,OU=Sites," & objRootLDAP.Get("defaultNamingContext"), _
   "CN=NewUsersGroup2,OU=Main Office,OU=Sites," & objRootLDAP.Get("defaultNamingContext"), _
   "CN=NewUsersGroup3,OU=Main Office,OU=Sites," & objRootLDAP.Get("defaultNamingContext") _

Open in new window

and then also change this:
			Set objNewGroup = GetObject("LDAP://" & strNewGroup)

Open in new window

to this:
			For Each strNewGroup In arrNewGroups
				Set objNewGroup = GetObject("LDAP://" & strNewGroup)

Open in new window

and that should work fine.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.