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.

Next Steps

Building an efficient and effective IT team requires a centralized solution that acts as your core service deliver tool. NinjaOne enables IT teams to monitor, manage, secure, and support all their devices, wherever they are, without the need for complex on-premises infrastructure.

Learn more about NinjaOne Remote Script Deployment, check out a live tour, or start your free trial of the NinjaOne platform.

Categories:

You might also like

Watch Demo×
×

See NinjaOne in action!

By submitting this form, I accept NinjaOne's privacy policy.

NinjaOne Terms & Conditions

By clicking the “I Accept” button below, you indicate your acceptance of the following legal terms as well as our Terms of Use:

  • Ownership Rights: NinjaOne owns and will continue to own all right, title, and interest in and to the script (including the copyright). NinjaOne is giving you a limited license to use the script in accordance with these legal terms.
  • Use Limitation: You may only use the script for your legitimate personal or internal business purposes, and you may not share the script with another party.
  • Republication Prohibition: Under no circumstances are you permitted to re-publish the script in any script library belonging to or under the control of any other software provider.
  • Warranty Disclaimer: The script is provided “as is” and “as available”, without warranty of any kind. NinjaOne makes no promise or guarantee that the script will be free from defects or that it will meet your specific needs or expectations.
  • Assumption of Risk: Your use of the script is at your own risk. You acknowledge that there are certain inherent risks in using the script, and you understand and assume each of those risks.
  • Waiver and Release: You will not hold NinjaOne responsible for any adverse or unintended consequences resulting from your use of the script, and you waive any legal or equitable rights or remedies you may have against NinjaOne relating to your use of the script.
  • EULA: If you are a NinjaOne customer, your use of the script is subject to the End User License Agreement applicable to you (EULA).