AD / Import CSV Update Attributes Script

by ws2000 at 2012-12-07 17:47:21


I am attempting to modify some standard AD account fields with the script below. Sorry… I am a Powershell newbie. Please help me fill in the missing pieces to get it working correctly. Do I need to use a -Replace for previously populated fields? Is it better to use a –Clear parameter and start fresh? If my CVS file contains more attributes fields than I currently need to update in AD is it ok to just pick those fields with the script?

# Import AD Module

Import-Module ActiveDirectory

# Import CSV into variable $userscsv

$userscsv = import-csv D:\areile\Desktop\adtest.csv

# Loop through CSV and update users if they exist in CVS file

foreach ($user in $userscsv) {

#Search in specified OU and Update existing attributes
Get-ADUser -Filter "samaccountname -eq ‘$($user.samaccountname)’" -SearchBase "DC=subdomain,DC=company,DC=com" %
% { Set-ADUser $_ -Replace @{Division = $($.Division); Office = $($."Office"); City = $($.City)}

CSV File Example
SamAccountName Division Office City
GRogers Accounting Main Office New York
SRogers Admin Building #2 New Jersy

Thank you in advance
by DonJ at 2012-12-08 08:49:56
Using -Replace is fine - it’ll internally clear the attribute and put in the new value for you. And it’s certainly fine to just pick and choose what you want to update.
by ws2000 at 2012-12-08 11:31:26
Any ideas why the script doesn’t work for me? When I ran it I didn’t get any errors but none of the test users where updated either.
by DonJ at 2012-12-08 11:52:37
It’s actually doing exactly what you told it to ;). It definitely updated them - it just set them to their existing values, so there was no change.

Firs, the following line should end in a pipe, not a %…

Get-ADUser -Filter "samaccountname -eq ‘$($user.samaccountname)’" -SearchBase "DC=subdomain,DC=company,DC=com" %

That was probably just a typo here int he forums, I’m guessing.

In the ForEach-Object block below, $
represents an ADUser object. That’s what Get-ADUser produces, and that’s what you’re piping in. So you’re basically setting the Division property to whatever the Division property already is (and Office, and City, etc). Instead of $.Division, I think you may have meant to use $user.Division, which would represent the current line from your CSV file. $ only represents what was piped into ForEach-Object; it won’t contain a reference to your CSV file, but instead represents the original ADUser values.

% { Set-ADUser $_ -Replace @{Division = $user.Division); Office = $user."Office"); City = $user.City)}

That said, there’s no real reason to use a ForEach block at all. The way you’ve written Get-ADUser, it can only return one user, because samAccountName is guaranteed unique in a domain:

Get-ADUser -Filter "samaccountname -eq ‘$($user.samaccountname)’" -SearchBase "DC=subdomain,DC=company,DC=com" |
Set-ADUser -City $ -Division $user.division -Office $

You can’t use $_ in that syntax because it’s meaningless, but Set-ADUser can accept the ADUser object output by Get-ADUser. Also notice that I used the attribute-specific parameters instead of -Replace; you only need -Replace when you need to mess with an attribute for which there isn’t a dedicated parameter. Using ForEach as you did doesn’t hurt anything, but it overcomplicates your code and makes it a bit harder to follow what’s happening. Since Get-ADUser, as you’re using it, will never produce a collection, there’s no need to enumerate through them.

Speaking of overcomplicating… you don’t actually need Get-ADUser. You’re specifying the samAccountName as your search criteria; that’s guaranteed unique across the domain.

foreach ($user in $userscsv) {
Set-ADUser -Identity $user.samaccountname -City $ -Division $user.division -Office $

Set-ADUser already knows how to search by samAccountName, since that’s guaranteed to return only one object.

If you were hell-bent on making this a one-liner:

Import-CSV D:\areile\Desktop\adtest.csv |
ForEach-Object {
Set-ADUser -Identity $.samaccountname -City $.city -Division $.division -Office $.office

And a couple of notes…

You don’t need to use subexpressions as you were: $.Division is fine instead of $($.Division). Although as noted, I think you want $user.division. The only time you need the subexpression syntax is when you’re inside double quotes.


You don’t need the double quotes when the property doesn’t contain a space (few do). $
."The Property" would need the quotes, for example.
by RichardSiddaway at 2012-12-09 02:31:19
The other issue is that -replace uses the LDAP attribute name rather than the GUI name so city becomes l and office becomes physicaldeliveryofficename
by ws2000 at 2012-12-10 15:40:07
DonJ and RichardSiddaway

Thank you both for the responses. They were very helpful in educating me and arriving at a actual working script. The code example @ RichardSiddaway Blog = "Bulk modifications using Set-AdUser" worked perfectly for me.