How to create new Active Directory Users with PowerShell and Excel

One of you asked me what if the domain has extended schema like Sharepoint. Ofc. You can export the extended schema with the correct LDAP attributes name, but before you can create a new user you have to extend the new domain’s schema as well. (Hint: at the down of this post you can find the XLSX file and you can download it)

So lets check our csv (already changed to xlsx), of what kind of information do we have.

We need to concatenate the fields in a correct way. With this I am going to show you an excel formula.

="New-ADUser -AccountPassword $sec_pass -Company "&""""&""&$I2&""&""""&" -Description "&""""&""&$J2&""&""""&" -EmailAddress "&""""&""&$N2&""&""""&" -DisplayName "&""""&""&$G2&""&""""&" -MobilePhone "&"""+"&""&$O2&""&""""&" -Surname "&""""&""&$H2&""&""""&" -Type User -SamAccountName "&""""&""&$M2&""&""""&" -GivenName "&""""&""&$K2&""&""""&" -Name "&""""&""&$L2&""&""""&" -Path "&$P2&""

With this easy formula we could concatenate the fields in the correct way. Don’t forget to create a new column (In my excel example this is the P one” If you want to specify the Path where the users should be created.

Just simply copy the Q columns out of from Excel and paste it to the PowerShell (run as admin)

So right now we got a Disabled Active Directory User with no Password, and without logon name:

The rest of the attributes are ok. We need to enable them and set up the logon name correctly. For this we need to create a new tab at excel and name it password. Go to the  and generate enough new random password.  For better recognization use the values from the first tab (called users).

Username: =users!M2 DisplayName: =users!L2 Password = copied from e-mail address =users!N2

Create a new tab called enable_password.

="Enable-ADAccount -identity "&users!M2&" ; Set-ADAccountPassword -identity "&users!M2&" -Reset -NewPassword (ConvertTo-SecureString -AsPlainText  "&password!$C2&" -Force)"

Simply paste the results to the powershell window.

Right now every user are enabled and got a new unique password (complex password), however the logon name still not the best

To handle this “error” run the following formula

Get-ADUser -Filter * -Properties userPrincipalName | foreach { Set-ADUser $_ -UserPrincipalName ("{0}@{1}" -f $_.sAMAccountName,"")}

If this is not brand new domain then maybe you need to specify the path. For this Use the following command:

Get-ADUser -Filter * -SearchBase "ou=Blog,ou=Engineer's,ou=IT,ou=An,ou=Just,dc=szeles,dc=me" -Properties userPrincipalName | foreach { Set-ADUser $_ -UserPrincipalName ("{0}@{1}" -f $_.sAMAccountName,"")}

So it does the trick. If you want to download the excel file feel free:

One thought on “How to create new Active Directory Users with PowerShell and Excel

Leave a Reply

Your email address will not be published. Required fields are marked *