PowerShell Bulk AD user import – Windows Server 2012 R2

Every now & then you might be up for a task where you have to import a bulk amount of users in your Active Directory. As an Infrastructure engineer, this can be a time consuming & hard task if you don’t know how to automate it. As a consultant I often get asked about this process and although there are loads of how to available in the internet, I am yet to find a good set of instructions that follows through A-Z on how to do this task. So I decided to write up a how to for others that is out there looking for a simple PowerShell solution.

To get started you need a list of users in an Excel Spreadsheet.
1) Excel Spreadsheet
2) Populate the Excel Spreadsheet with Data that you use in your AD attributes
3) Convert the Excel Spreadsheet to a excel CSV file
4) Log in to your domain controller
5) Install powershell module for Active Directory in the Domain Controller VM
6) Adjust your powershell script from the script below, as in, add or remove fields as necessary.

Below are the fields I use in my test company, pay attention to how I have written the below items without spaces, this is how an AD Attribute is written
GivenName (first name)
SurName (last name)
DisplayName (Full name)

Below is a screenshot to show the fields I have populated in my AD


Customizing the Excel Spreadsheet
To get the excel spreadsheet filled with exact information can be a hard task but if you know a little of excel then this task can be easy.

Username:  in the username column I ran below calculation =concatenate(left(B2,1), D2) then press entre. This grabs the first initial of first name and full lastname and joins them together.

UserPrincipalName: under the userprincipalname column I ran below excel command,
=A2&”@domain.local”. This grabs the A2 column which is the username & joins the @domain.local to the end.

Password: Under the password column, I just filled the whole column with plain txt, ie: password123

Path: this is the OU where the new users will be created, you can check the attributes of the OU container to grab the path or you can grab it from ADSI Edit.

TelephoneNumber: I wanted to put the telephone number as format +61 3 9164 5421 but excel was changing the format. So to get around the issue, I formatted the telephone column from number to plain text.
Country: AD doesn’t recognise the whole country name. so in the country column instead of putting country name “America” you have to put “US” or instead of “Australia” you have to put “AU”

HomeDirectory: to get the home directory, I had to create another column and fill that our with UNC path. Then under the HomeDirectory column I run excel command =CONCATENATE(T2,A2). This joins the unc path & username together to get a home directory of \\fileserver\username

To get the full details about how AD attributes are named please see below link

PowerShell Script
Now comes the main part. The script has 2 part,
Line 1 – import the csv file & for each row of the CSV do the next line.
Line2 – Match the AD fields to the column of the CSV, -FIELDNAME is the AD object and $_.COLUMNNAME is the item from the csv file.

Place both the script & csv file in a temp folder. Open Powershell, disable restricted mode in powershell. Then navigate to the folder where you saved your csvfile & script. Then run the script.

My Script looks like below

Import-Csv .\userImport1.csv | foreach-object {New-ADUser -Name $_.DisplayName -UserPrincipalName $_.UserPrincipalName -HomePage $_.HomePage -Company $_.Company -SamAccountName $_.Username -GivenName $_.GivenName -DisplayName $_.DisplayName -SurName $_.Surname -Description $_.Description -Department $_.Department -StreetAddress $_.StreetAddress -City $_.City -State $_.State -Country $_.Country -PostalCode $_.ZipCode -OfficePhone $_.TelephoneNumber -Title $_.Occupation -Office $_.Office -Path $_.Path -AccountPassword (ConvertTo-SecureString $_.Password -AsPlainText -force) -Enabled $True -HomeDirectory $_.HomeDirectory -HomeDrive $_.HomeDrive -ChangePasswordAtLogon $true -PassThru }

Further information could found from below link

Leave a Reply

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