Updating Active Directory from a CSV

Scenario:

You’ve been asked to populate everyone’s Active Directory job title. The payroll system is correct, and they’re able to export you a list of usernames and correct job titles. All you need to do is get that into AD.

Solution:

You could do this manually of course, but that’s no fun and a waste of time. This is one of those scenarios where you’ll hopefully think ‘PowerShell can do this!’ and possibly wonder how. That’s what I did anyway, so set out to make it work.

Here’s a fake example of the data I was working with, in a file called fake.csv:

EMPLOYEENAME,JOBTITLE
AFOWLER,IT OPERATIONS MANAGER
RSOLE,JANITOR

Tip: If you open a csv file in Excel it is a bit easier to read.

From this data, we want to match the EMPLOYEENAME to the correct AD account, then update the Job Title field from the JOBTITLE entry of the csv file.

A script that will do this is:

Import-module ActiveDirectory
$data = import-csv -path c:\fake.csv
foreach ($user in $data){
Get-ADUser -Filter “SamAccountName -eq ‘$($user.employeename)'” | Set-ADUser -Replace @{title = “$($user.JobTitle)”}
}

So, what’s happening here? It can take a bit to get your head around especially if you’re not used to programming (like me), so I’ll try to explain it:

Import-module ActiveDirectory
Importing the ActiveDirectory module so the Get-ADUser command works. If you can’t load the module, install RSAT (Remote Server Administration Tools) which includes the AD module.

$data = import-csv -path c:\fake.csv
This is setting the $data variable to memory, which will contain all the contents of the fake.csv file.

foreach ($user in $data){
This is saying ‘for each line of information from the $data variable (which is the csv file), map that to $user and do the following”

Get-ADUser -Filter “SamAccountName -eq ‘$($user.employeename)'” | Set-ADUser -Replace @{title = “$($user.Job Title)”}
This is getting any AD User where their SamAccountName matches the employeename column of the $user variable (which is the current line of information from the csv at time of processing). Then with the pipe | it will use the result to then Set the AD User’s title field (where the job title goes) to the Job Title part of our $user variable. This command will run twice, because there are two lines for ‘foreach’ to process.

}
This is closing off the command which each ‘foreach’ command runs.

 

I hope that explains it enough so you’re able to manipulate the script to your own requirements.

 

6 thoughts on “Updating Active Directory from a CSV

  1. Please help, script run for a couple line then stop at Identiy:
    I have look in AD and only about half of the users have been updated.

    1. Sounds like the ‘jobtitle’ field in your CSV is just called ‘title’ which is one reason why it wouldn’t work, I’m sure this way works too though :)

  2. Thanks for this. Had to swap the single and double quotes in the filter to get it to work. Now it runs without error, but nothing happens. Here are my modifications:

    Import-module ActiveDirectory
    $data = import-csv -path “\ADMobileUpdateTest.csv”
    foreach ($user in $data){
    Get-ADUser -Filter ‘UserPrincipalName -eq “$($user.UPN)”‘ | Set-ADUser -Replace @{mobile = “$($user.Mobile)”}
    }

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.