How to Import Users into Active Directory from CSV File using PowerShell

One of the most common applications of PowerShell that I see IT professionals use is syncing information with Active Directory (AD). It seems like everyone has some external data source that correlates to some employees that we need to get into AD. This is to be expected because AD typically is the most used IT service in an organization and provides an excellent feature not just as authentication but as a repository of employee information.

Whether you’ve got data stored in a custom SQL database or some fancy HR system, it sucks to have to check two spots if you need information like employee department, groups that employee should be a member of, and so on. When you do begin to sync that data, the source system may change its schema, may change APIs, servers, and so on which means a code rework. To ensure the data source you’re syncing from always stays the same and can easily be created from any number of systems, it’s a good idea to get that data into a CSV file.

A simple CSV file can be exported from just about any software or database and when in this format, gives the administrator a copy that can be tweaked as well as necessary foregoing the requests from another team to change up a database somewhere.

Let’s go with a simple script you can use today to see how we can sync an attribute from a CSV to an AD account.

To set the stage, let’s say I’ve got a CSV called C:Employees.csv. This CSV has three columns: FirstName, LastName, and Department. You’d like a script that can find the AD account that’s associated with that first/last name and then change the department to whatever is in the CSV.

This script, like any sync script, will primarily consist of three “phases.” Those “phases” are reading the source data (CSV in this example), finding the unique ID of the destination source data that matches the source data (AD samAccountName in this example), and finally writing the associated attributes tied to that account.

The first task is reading the CSV file.

$csvUsers = Import-Csv -Path 'C:Employees.csv'

Done. The next part is finding the unique ID to match each row of the CSV file on. Since we don’t have the unique ID inside of the CSV, we have to build one on the fly. To come up with that unique ID, we’re going to assume that the first initial/last name of an employee will always be an AD samAccountName. We’ll need to create this search samAccountName and then perform an AD query to see if an account exists.
Note: Before we get too far, be sure you have downloaded and installed the Remote Server Administration Tools for your operating system. This will give you the Active Directory PowerShell module which contains the Get-AdUser and Set-AdUser cmdlets we will be using.

foreach ($csvUser in $csvUsers) {
    $userName = '{0}{1}' -f $csvUser.FirstName.SubString(0,1),$csvUser.LastName
    if ($adUser = Get-AdUser -Filter "samAccountName -eq $userName" -Properties $attributesToSync) {
        ## Our "unique ID" has been found. Proceed to read the CSV data and write attributes
    } else {
        Write-Warning -Message 'User match not found'
    }
}

Once we have an AD account to work with, we can then read each attribute associated with that employee name in the CSV file and write that information to the AD account found. Notice below that I’m defining the $attributesToSync variable and then reading that later. I’ve done this so that I can add additional attributes that may be in the CSV to sync those as well in the future. The below example is first checking to see if the attribute is the same. If not, it’s using Set-AdUser to change it.

$attributesToSync = 'Department'
foreach ($csvUser in $csvUsers) {
    $userName = '{0}{1}' -f $csvUser.FirstName.SubString(0,1),$csvUser.LastName
    if ($adUser = Get-AdUser -Filter "samAccountName -eq $userName" -Properties $attributesToSync) {
        foreach ($attr in $attributesToSync) {
            if ($csvUser.$_ -ne $adUser.$_) {
                $setParams = @{$_ = $csvUser.$_}
                $adUser | Set-AdUser @setParams
            }
        }
    } else {
        Write-Warning -Message 'User match not found'
    }
}

This example will only work with attributes that are string values inside AD. Watch out for those attributes like the manager, account expiration date, and so on. These attributes require an object of the correct type to be built before being written to AD. It would take another series of articles to go over the intricacies of AD syncing and working with different attributes, but I’ve created a PowerShell called PSADSync that assists you with this. It can be found by pulling it from the PowerShell Gallery with Install-Module PSADSync.

Adam Bertram is a Microsoft Windows Cloud and Datacenter Management MVP and has authored various training courses, is a regular contributor to numerous print and online publications and presents at various user groups and conferences. You can find Adam at adamtheautomator.com or on Twitter at @adbertram.