Import Active Directory Users from a CSV with PowerShell

Import Active Directory Users from a CSV Spreadsheet with PowerShell

Overview

In this tutorial I want to show you how you can write a PowerShell script to automatically create Active Directory user accounts based on a CSV or Excel spreadsheet.

This is what we will be learning in this tutorial:

New-ADUser PowerShell command
Import a file to PowerShell
Iterate over all lines and columns in a CSV file

Please launch the Windows Server 2016 AD lab from the IT playground so you can log into the IT labs and practice building these accounts in a test environment. Note that this requires a membership to ServerAcademy.com which you can get here. If you aren't one of our students you can still set up an IT lab in VirtualBox or Hyper-V.

If you want you can download the example "NewUsers.csv" file below by clicking the link.

Downloads

NewUsers.csv

NewUsers.ps1 (optional)

Become a System Admin

Without Leaving Your Home

Try For Free Today

Importing Required Modules and References

To get started we need to import the Active Directory module and load the Windows forms. We will use a open file dialog box to specify the path to the CSV file that contains the users. This is much easier for the end user versus typing the full path. You will notice we are using out-null, which just means we don't want to output the result of the command.

# Import Active Directory module
Import-Module ActiveDirectory

# Open file dialog
# Load Windows Forms
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null

Create the Open File dialog

Let's create the file open dialog box. You can use this snippet for other types of files too - you just need to change the filter on line 4 to include your desired file type.

# Create and show open file dialog
$dialog = New-Object System.Windows.Forms.OpenFileDialog
$dialog.InitialDirectory = "C:\"
$dialog.Filter = "CSV (*.csv)| *.csv" 
$dialog.ShowDialog() | Out-Null

# Get file path
$CSVFile = $dialog.FileName

Once we select a file in the file dialog box, it can be referenced by calling the $dialog.FileName variable which we will store in a variable called CSVFile. This is what the file open dialog box will look like:

Import the CSV file contents

We have found the file path, but we haven't actually imported the data yet. First we are going to make sure we have a valid file path. If the path is valid then we will import the CSV file to the $CSV variable with the Import-CSV command. If the file path is not valid, we will exit the script so it doesn't try to create any AD users.

# Import file into variable
# Lets make sure the file path was valid
# If the file path is not valid, then exit the script
if ([System.IO.File]::Exists($CSVFile)) {
    Write-Host "Importing CSV..."
    $CSV = Import-Csv -LiteralPath "$CSVFile"
} else {
    Write-Host "File path specified was not valid"
    Exit
}

Iterating over columns and lines

Now we are ready to begin iterating over each line of the CSV file. To do this we will use a ForEach loop, which will read the CSV as an array of objects that we use to access each column of each line.

In this for loop, we will define each line as "$user". For example, to access the column "Organizational Unit", we would access it by calling $user.'Organizational Unit'.

# Lets iterate over each line in the CSV file
foreach($user in $CSV) {
    # Lets do cool stuff in this for loop
}

When it comes to formatting multiple columns together, we can wrap variables inside of the parenthesis "$()" sub expression operator. For example, we could combine the first name and last name into a single variable like so:

$FirstAndLastName = "$($user.'First Name') $($user.'Last Name')"

We are going to use this logic to create a username in the first.last format based on the first and last name columns in our CSV:

# Set up variables
$UserName = "$($user.'First Name').$($user.'Last Name')"

Next lets address the posibility of having a name that contains a space. For example, "Dennis Smith JR" or "Joe Friday III". What we are going to do is create a variable to hold the first and last name then delete any spaces with the "replace" command:

$Username = $Username.Replace(" ", "")

Now let's generate a secure password BASED on the information that we have in the spreadsheet. For example, let's make one in the following format:

[First Name Initial] + [Last Name] + [Employee ID] + [Special Characters]

For the special characters, we can just define them as "!@#". We already have the other information stored in our spreadsheet, so let's generate this password! To create an AD user password, we need to use the command "ConvertTo-SecureString". Inside of this we are going to use sub expression operators to combine the different columns of the CSV file like so:

[First Name Initial] = $user.'First Name'[0]

[Last Name] = $user.'Last Name'

[Employee ID] = $user.'Employee ID'

[Special Characters] = !@#

Since we are going to be wrapping this into a quote, we will need to use a sub expression operator to ensure we parse the data correctly... So it will look like this:

# Password
$SecurePassword = ConvertTo-SecureString "$($user.'First Name'[0])$($user.'Last Name')$($user.'Employee ID')!@#" -AsPlainText -Force

Sometimes it can be a great idea to test when you are putting together a complicated string like that. We can test this code in the PowerShell console by substituting "$user" for "$CSV[0]" to reference the first row in our CSV as shown below:

Write-Host "$($CSV[0].'First Name'[0])$($CSV[0].'Last Name')$($CSV[0].'Employee ID')!@#"

Which outputs:

PHill558739!@#

This is exactly the format we want to generate the password so that is good to go.

Create the AD User

Now it's time to run the New-ADUser command and create the new user account. This can be a VERY long command, so we will use the backtick ( ` ) to word-wrap our command.

# Create new user
New-ADUser -Name "$($user.'First Name') $($user.'Last Name')" `
            -GivenName $user.'First Name' `
            -Surname $user.'Last Name' `
            -UserPrincipalName $Username `
            -SamAccountName $Username `
            -EmailAddress $user.'Email Address' `
            -Description $user.Description `
            -OfficePhone $user.'Office Phone' `
            -Path "$($user.'Organizational Unit')" `
            -ChangePasswordAtLogon $true `
            -AccountPassword $SecurePassword `
            -Enabled $([System.Convert]::ToBoolean($user.Enabled))
            
# Write to host that we created a new user
Write-Host "Created $Username / $($user.'Email Address')"

You will notice for -Enabled we used a System.Convert to convert that column to a boolean. This is because the New-ADUser command connot accept a string for -Enabled and it must be a boolean instead. We use the $([System.Convert]::ToBoolean() command to convert the value of our column from a string to a boolean.

Now we just need to add logic to add the user to each group that we specified in the spreadsheet. We can do that by splitting the group column and then iterating with another foreach loop over each split value:

# If groups is not null... then iterate over groups (if any were specified) and add user to groups
if ($User.'Add Groups (csv)' -ne "") {
    $User.'Add Groups (csv)'.Split(",") | ForEach {
        Add-ADGroupMember -Identity $_ -Members "$Username"
        Write-Host "Added $Username to $_ group" # Log to console
    }
}

At this point we just need to close the foreach loop, add a prompt to pause the script before closing the window (for troubleshooting purposes) and we are all done:

}

Read-Host -Prompt "Script complete... Press enter to exit."

I am going to save the script as NewUsers.ps1 to my C: drive where I have the CSV file. We can use the script by right-clicking the script and choosing "Run with PowerShell" as shown below:

The script will start and display the open file dialog. Once the script is complete - it will pause and you can take this moment to look for errors... I don't have any errors so I will just close the script.

And now I can see the users that were created in AD in the correct OUs and with the correct group memberships according to my CSV file and script!

And that's it! Hope you enjoyed this one :D... In case you wanted it here is the full syntax of the code:

# Import Active Directory module
Import-Module ActiveDirectory

# Open file dialog
# Load Windows Forms
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null

# Create and show open file dialog
$dialog = New-Object System.Windows.Forms.OpenFileDialog
$dialog.InitialDirectory = $StartDir
$dialog.Filter = "CSV (*.csv)| *.csv" 
$dialog.ShowDialog() | Out-Null

# Get file path
$CSVFile = $dialog.FileName

# Import file into variable
# Lets make sure the file path was valid
# If the file path is not valid, then exit the script
if ([System.IO.File]::Exists($CSVFile)) {
    Write-Host "Importing CSV..."
    $CSV = Import-Csv -LiteralPath "$CSVFile"
} else {
    Write-Host "File path specified was not valid"
    Exit
}

# Lets iterate over each line in the CSV file
foreach($user in $CSV) {

    # Password
    $SecurePassword = ConvertTo-SecureString "$($user.'First Name'[0])$($user.'Last Name')$($user.'Employee ID')!@#" -AsPlainText -Force

    # Format their username
    $Username = "$($user.'First Name').$($user.'Last Name')"
    $Username = $Username.Replace(" ", "")

    # Create new user
    New-ADUser -Name "$($user.'First Name') $($user.'Last Name')" `
                -GivenName $user.'First Name' `
                -Surname $user.'Last Name' `
                -UserPrincipalName $Username `
                -SamAccountName $Username `
                -EmailAddress $user.'Email Address' `
                -Description $user.Description `
                -OfficePhone $user.'Office Phone' `
                -Path "$($user.'Organizational Unit')" `
                -ChangePasswordAtLogon $true `
                -AccountPassword $SecurePassword `
                -Enabled $([System.Convert]::ToBoolean($user.Enabled))

    # Write to host that we created a new user
    Write-Host "Created $Username / $($user.'Email Address')"

    # If groups is not null... then iterate over groups (if any were specified) and add user to groups
    if ($User.'Add Groups (csv)' -ne "") {
        $User.'Add Groups (csv)'.Split(",") | ForEach {
            Add-ADGroupMember -Identity $_ -Members "$($user.'First Name').$($user.'Last Name')"
            WriteHost "Added $Username to $_ group" # Log to console
        }
    }

    # Write to host that we created the user
    Write-Host "Created user $Username with groups $($User.'Add Groups (csv)')"
}

Read-Host -Prompt "Script complete... Press enter to exit."

[wpdevart_facebook_comment width="100%"]

Import Active Directory Users from a CSV Spreadsheet with PowerShell

Overview

Downloads

Become a System Admin

Without Leaving Your Home

Importing Required Modules and References

Create the Open File dialog

Import the CSV file contents

Iterating over columns and lines

Create the AD User

Training

Sign Up Free

Our Curriculum

IT Labs

Lab Challenges

Blog

Members

Login

Community

Discord

About Us

Contact

Free Active Directory Course!

Active Directory Fundamentals

Import Active Directory Users from a CSV Spreadsheet with PowerShell

Overview

Downloads

Become a System Admin

Without Leaving Your Home

Importing Required Modules and References

Create the Open File dialog

Import the CSV file contents

Iterating over columns and lines

Create the AD User

Training

Members

About Us