Update AD from CSV

Suppose you have a CSV file (a text file with columns separated by commas) with the properties for AD user accounts you want to update. How do you do this in PowerShell?

Turns out, that we talked a lot about creating new accounts from CSV files before, but not about updating existing ones. Let’s fix this right away.

I will be using AD user accounts in my examples, but it is fairly easy to adapt them to other AD objects: groups, computers, OUs, DNS records, and so on.

The command actually depends on the CSV you get. The easiest case is when the column names are exactly the same as Set-QADUser parameters. For example, let’s say you have a CSV file in which you have a samAccountName column which you want to use to locate the accounts to update and Title and Department columns with the new values to set:

samAccountName,Title,Department
test1,Manager,Accounting
test2,Developer,RD
test3,SC,Pre-Sales

The onliner to apply this file to your AD is as simple as:

Import-Csv cupdate.csv | Set-QADUser -Identity { $_.samAccountName }

You basically pipe import into Set-QADUser and specify which column to use as the identity anchor.

Easy!

Now, suppose that life is not so easy and either you do not control the column labels or you need to update attributes which either do not match the parameter names or have no matching parameters at all. Like:

samAccountName,Job,ExtensionAttribute1,ExtensionAttribute2
test1,Manager,M,Yes
test2,Developer,S,No
test3,SC,XXL,Maybe

The automated column matching will not work here but we can use ForEach-Object loop and match the parameters manually + use ObjectAttributes for attributes with no parameters:

Import-Csv cupdate.csv | ForEach-Object {
Set-QADUser $_.samAccountName -Title $_.Job `
-ObjectAttributes @{ExtensionAttribute1=($_.ExtensionAttribute1);
ExtensionAttribute2=($_.ExtensionAttribute2)}
}

Now we can update from CSV any account properties we want!

Tags: AD, AD cmdlets, Active Directory, Examples, PowerShell, cmdlets, one-liner, oneliner
Add to: | Technorati | Digg | del.icio.us | Yahoo | BlinkList | Spurl | reddit | Furl |