PowerShell How-To

How To Compare a CSV File with Active Directory Users

Using PowerShell, Adam shows you one way to compare a .CSV file full of employee accounts with Active Directory users.

• By Adam Bertram
• 08/20/2019

So you've been given a .CSV file from your HR department that contains a list of employees that you need create user accounts for in Active Directory.

Perhaps it was because of a recent company merger or a new department was formed. Either way, you've got a lot of user accounts to create -- and there's no way you're going to manually create each one of them.

Let's see how PowerShell can cut down the time to create all of these user accounts.

Reading the CSV File
The first step you're going to do is read the .CSV file. For this article, assume that your .CSV file has four columns: first name, last name, department and employee ID.

You'll read the .CSV file using the Import-Csv cmdlet in PowerShell. This cmdlet inherently knows the structure of a .CSV file and returns one object per .CSV row with property values from the .CSV, as shown below:

## Assuming C:\McpMag is where your employees.csv file is
PS C:\> Import-Csv -Path C:\McpMag\employees.csv

FirstName LastName Department EmployeeID
--------- -------- ---------- ----------
Adam      Bertram  IT         1111
Joe       Jones    HR         1223
Bobby     Simmons  Accounting 23444

Now you can see that you've pulled out all of the employee rows from the .CSV. You'll be comparing the employees in this .CSV file in a little bit. Go ahead and assign all of these employees to a variable to reference later.

$csvEmployees = Import-Csv -Path C:\McpMag\employees.csv

Enumerating Active Directory Users
The next step is to find all of current Active Directory users. You'll need this information to tell whether or not that employee may already have a user account. Be sure you have the ActiveDirectory PowerShell module installed. Once you have the module installed, assuming you have appropriate rights in Active Directory, you can use the Get-AdUser cmdlet to pull all enabled user accounts.

Use the Properties parameter to ensure all of the same properties that are in the .CSV file are returned here.

$adUsers = Get-AdUser -LdapFilter '"(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))"' -Properties givenName,surName,department,employeeid

Comparing CSV Employees and Active Directory Users
Finally, once you have both data sources in memory and available, you can now compare the two.

First, you'll need a unique ID to compare. In this example, you have an EmployeeID property that should be unique to each .CSV row and Active Directory user. Using this property, you can build some code that will check each employee indicated by .CSV row against all of the Active Directory user accounts you previously saved in the $adUsers variable.

You can see below that I'm using the foreach() method on the $csvEmployees array. Although the ForEach-Object cmdlet could have been used, the foreach() method is typically faster. If your script is processing thousands of accounts, you'll want the fastest way possible.

$csvEmployeesNotInAd = $csvEmployees.foreach({
    $employee = @{
        'CSVEmployeeID' = $_.EmployeeID
        'InAD' = $false
    }
    if ($_.EmployeeID -in $adUsers.employeeId }) {
        $employee.InAD = $true
    }
    [pscustomobject]$employee
})

Once the above code snippet is complete, it will return all of the employees that are not in Active Directory looking like the below output:

CSVEmployeeID   InAd
-------------   ----
1111            True
1223            False
23444           False

I've now shown you one way to compare a .CSV file full of employee accounts with Active Directory users. How else could you do this? With PowerShell, you have many ways to perform the same action. If you'd like to see a more advanced example of this topic, I encourage you to check out my blog post, "Get Active Directory Users From Text File."