Update AD from CSV

Published October 3, 2008 Active Directory , AD , AD cmdlets , cmdlets , Examples , one-liner , oneliner , PowerShell 39 Comments

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 c $:\$ update.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 c $:\$ update.csv | ForEach-Object {
Set-QADUser $_.samAccountName -Title $_.Job `
-ObjectAttributes @{ExtensionAttribute1=($_.ExtensionAttribute1);
ExtensionAttribute2=($_.ExtensionAttribute2)}
}

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

39 Responses to “Update AD from CSV”

Feed for this Entry Trackback Address

1 Rich Harmer October 6, 2008 at 7:44 pm

Can’t this be slightly modified to create users too?

Reply
2 Dmitry Sotnikov October 7, 2008 at 11:28 am

Rich,

Yes, you can use a similar approach with the New-QADUser cmdlet:

https://dmitrysotnikov.wordpress.com/2008/01/21/ad-user-provisioning-from-csv-got-easier/

Dmitry

Reply
- 3 sra1 September 9, 2015 at 12:47 am
  
  hey, can you please explain me the terms in the command specifically. I have a csv file with fields age , color, height, weight, hair color. . Assume ” ABCD ” is the samaccountname. ABCD account do not have fields ” age and height “. and all other details exist in AD. I have to update only these two fields from csv file to AD. can you please put the whole command for better understanding.
  
  Reply
4 Anuar June 25, 2009 at 1:02 pm

How to deal with attribute that has dash ‘-‘ in it?
I got an error: “Missing ‘=’ operator after key in hash literal.”

Reply
5 Dmitry Sotnikov June 25, 2009 at 1:22 pm

Anuar,

Quotation marks should help. E.g.: @{ ‘attribute-with-dash’ = 25 }

If I got the question wrong or you have more issues there please post your questions to our AD PowerShell forum at: http://powergui.org/forum.jspa?forumID=173

Dmitry

Reply
6 Ozk4r February 3, 2011 at 1:24 am

How i run this update from a schedule task?

Thanks

Reply
- 7 Dmitry Sotnikov February 3, 2011 at 6:00 am
  
  Scheduling a script as task is easy:
  
  1. Make sure that the script has all the snapins/modules it needs. E.g. this one needs the QAD cmdlets so you might want to add this to the script:
  
  Add-PSSnapin Quest*
  
  2. Run PowerShell as administrator and set execution policy to allow local script execution:
  
  Set-ExecutionPolicy RemoteSigned
  
  3. Schedule powershell.exe running your script. E.g.:
  
  c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -Noninteractive -command “. c:\scripts\update-ad.ps1”
  
  (See this post for another example of script scheduling: https://dmitrysotnikov.wordpress.com/2008/05/29/monitor-web-site-availability/)
  
  Reply
  - 8 Dmitry Sotnikov February 3, 2011 at 9:14 pm
    
    Here’s a detailed post on scheduling PowerShell scripts: https://dmitrysotnikov.wordpress.com/2011/02/03/how-to-schedule-a-powershell-script/
9 Oscar February 9, 2011 at 12:23 am

Excelent, Thank you!

Reply
10 Kirby June 21, 2011 at 6:25 pm

If your cvs file just contains old phonenumbers and new phonenumbers can you use the old phonenumber for the identity and then replace it with the new phonenumber?

Reply
- 11 Dmitry Sotnikov June 21, 2011 at 9:58 pm
  
  Kirby,
  
  Yes, you can, just use Get-QADUser -PhoneNumber (http://wiki.powergui.org/index.php/Get-QADUser)
  
  Your script will probably look like:
  
  import-csv c:\numbers.csv | foreach-object {
  Get-QADUser -PhoneNumber $_.OldNumber | Set-QADUser -PhoneNumber $_.NewNumber
  }
  
  If you get stuck or have additional questions, ask those at our AD and PowerShell forum here: http://powergui.org/forumindex.jspa?categoryID=55
  
  Dmitry
  
  Reply
12 Kirby June 22, 2011 at 4:51 pm

Hi Dmitry,
Thanks for the help, it worked great. I added a small peice so I could run it agaist an targeted OU. Thanks again

$OuDomain = “OU=The,OU=One,OU=You,OU=Want,DC=mydomain,DC=com”
import-csv c:\scriptest\phonenumbers.csv | foreach-object { Get-QADUser -SearchRoot $OuDomain -PhoneNumber $_.OldNumber | Set-QADUser -PhoneNumber $_.NewNumber }

Reply
- 13 Annie February 22, 2012 at 6:48 pm
  
  Hello, Your script works well but it still searches all users in AD instead of the ones in that OU. Is there a way to be sure that it is targeting only one OU?
  
  Reply
  - 14 Dmitry Sotnikov February 22, 2012 at 8:30 pm
    
    Yes, just use -SearchRoot parameter. See http://wiki.powergui.org/index.php/Get-QADUser
  - 15 Annie February 23, 2012 at 2:04 pm
    
    I have used the searchroot command but it still searches the entire directory. Please see below.
    Get-QADUser -Searchroot “OU=Guests,OU=People,DC=XXX,DC=XXX” -id $_.user
  - 16 Dmitry Sotnikov February 23, 2012 at 5:31 pm
    
    This is very weird. Can you try posting the issue to the PowerShell and AD forum at http://powergui.org?
17 Vladimir Valchev September 11, 2011 at 10:21 pm

Thank you Dmitry, saved my day

Reply
- 18 Dmitry Sotnikov September 12, 2011 at 6:23 pm
  
  My pleasure! 🙂
  
  Reply
19 Michael November 14, 2011 at 3:03 pm

I have used the Powershell Addin. It ist very usefull for me.
But I have one problem. I have a csv file, but not every entry in the csv file have an user account. Everytime when the script not found the user, the script is stop the working.
Could help me to solve this problem?

Many thanks

Reply
- 20 Dmitry Sotnikov November 14, 2011 at 3:22 pm
  
  Michael,
  
  One workaround would be to use Test-QADObject as described here: https://dmitrysotnikov.wordpress.com/2010/03/22/test-if-ad-object-path-exists/
  
  Dmitry
  
  Reply
  - 21 Allan February 24, 2012 at 1:02 am
    
    Hi Dmitry,
    
    Am a novice Powershell user.
    
    Have tried to use your script and this does not appear to work for me.
    Not sure if it’s an environmental issue or something I am doing wrong.
    Also hard to see what’s happening as the script is not returning any errors – so it appears that this is working.
    
    Am trying to run the following script in a ps1 file: –
    
    Import-Csv “C:\Temp\update_users.csv” | Set-ADUser -server “inf003.org.local” -Identity { $_.samAccountName } 2>> “C:\Temp\User_Error_Log.txt”
    
    Script runs ok (as in no errors reported) however does not update the user in the CSV file.
    
    CSV file is as follows: –
    samAccountName,Title,EmployeeNumber
    user1,Testing123,567890
    
    So as you can see all CSV titles are correct and I think it should work?
    
    The changes I made to your original script at the top of this post was the server name (as reading other posts on the web I was coming up against a problem where I did not have sufficient rights to run the commands, even though I was logged in with DA privilages and running the console as Admin) and I am attempting to log any errors to a log file using 2>>
    
    Any thoughts?
    Help much appreciated!
  - 22 Dmitry Sotnikov February 24, 2012 at 3:20 am
    
    Allan,
    
    I don’t work at the team anymore so I would really recommend that you contact them via AD and PowerShell forum at http://powergui.org
    
    My guess is that direct pipeline from Import-CSV to Set-QADUser is broken and you should put foreach in-between:
    
    Import-Csv “C:\Temp\update_users.csv” | ForEach { Set-ADUser -Identity $_.samAccountName -Title $_.Title -ObjectAttributes @{EmployeeNumber=$_.EmployeeNumber} }
    
    Dmitry
    
    P.S. I love Alan Parson’s Project. 😉
23 Allan February 24, 2012 at 5:01 am

Thanks mate!
Have followed your advice and posted on the AD and PowerShell forum at http://powergui.org

Allan

Reply
- 24 Anonymous July 24, 2012 at 3:49 pm
  
  Hi Dmitry
  
  I tried your script Import-Csv c:\update.csv | Set-QADUser -Identity { $_.sAMAccountName } but it return argument invalid for Identity what is wrong with me
  
  Nathalie
  
  Reply
  - 25 Dmitry Sotnikov July 25, 2012 at 2:51 am
    
    Nathalie,
    
    I am no longer with the team, so I would recommend that you post the question as well as your sample CSV to the AD cmdlets forum here: http://communities.quest.com/community/activeroles?view=discussions
    
    You can always try to add ForEach and see if it helps, e.g.:
    
    Import-Csv c:\update.csv | ForEach-Object { Set-QADUser -Identity $_.sAMAccountName -Title $_.Title }
    
    Dmitry
26 Joe April 4, 2013 at 2:14 pm

Worked great, Thanks

Reply
27 shirish patil April 12, 2014 at 2:24 pm

hi,

I want to disable users from exported csv file. I don’t want to use any quest application to diable user.

Reply
- 28 Dmitry Sotnikov April 16, 2014 at 2:14 pm
  
  Microsoft’s own AD cmdlets now also have Disable-ADUser: http://technet.microsoft.com/en-us/library/ee617197.aspx
  
  Reply
29 James May 2, 2014 at 7:44 am

When using this I found that the attribute userPrincipalName was also changed if it does’nt match your samaccountname

Reply
30 Amin May 4, 2014 at 10:20 pm

third party tools available with more advanced options..we can try these from manageengine, quest,netwrix,adsysnet,lepide and more..

Reply
31 Ollie S May 28, 2014 at 6:29 pm

Going to rip my hair out, don’t support anyone can help me with why i’m getting the following error:

“Import-Csv : Cannot process argument because the value of argument “name” is invalid. Change the value of the “name” ar
gument and run the operation again.
At line:1 char:11″

For this script:

Import-Csv c:\update.csv | ForEach-Object { Set-QADUser -Identity $_.SamAccountName -ObjectAttributes @{department={$_.department} ;company={$_.company} ;physicalDeliveryOfficeName={$_.physicalDeliveryOfficeName} ;telephoneNumber={$_.telephoneNumber} ;wWWHomePage={$_.wWWHomePage} ;homePhone={$_.homePhone}; title={$_.title}}}

Any help would be grand, thanks.

Reply
- 32 Dmitry Sotnikov May 28, 2014 at 6:33 pm
  
  Hmm…. Try being more explicit with Import-CSV parameters. E.g.: Import-CSV -path “c:\update.csv”
  
  Reply
33 George July 29, 2014 at 9:36 pm

Hi Dmitry – Thank You for your post. I am trying to update titles, departments and descriptions of users in specific OU in AD from csv file using email as identifier. Here is my query i run but i am not getting any results or error messages. What could be wrong here? Your help is greatly appreciated.

$USERS = Import-CSV c:\temp\test.csv
$USERS|Foreach (user in $users){
Get-ADUser -Filter * -SearchBase “OU=test,DC=Testdomain,DC=org” |Set-ADUSer -Identity $_.mail}

Reply
34 Andrew Fitzgerald July 1, 2015 at 8:48 am

Great script, i will add this to my article about the top Active directory powershell scripts available http://www.networkangel.net/top-8-active-directory-powershell-scripts

Reply
35 Shakeel Shahid October 13, 2015 at 4:16 am

Dear Sir,

I am facing some issues in an on-going project.

I am importing users in Active Directory from CSV file and after this i need to replace values of some custom attributes of all imported users and the Values needs to be imported from CSV file automatically.

Please send me your contact details so that i can further discuss with you on this issue.

Regards,
Shakeel Shahid.

Reply
36 Eric January 9, 2017 at 8:53 am

I cannot seem to get this to work and I have no idea why.

Import-csv “massupdates.csv” | ForEach-Object {set-QADUser $_.samAccountName -ObjectAttributes @{‘User-CommissionClass’=($_.commissionclass)}}

In my CSV file I have two columns

samAccountName commissionclass
lsmith gen.0123
alincon scs.1212
….

I keep getting this error and am not sure why:

set-QADUser : Cannot validate argument on parameter ‘Identity’. The argument is null or empty.
is not null or empty and then try the command again.
At line:1 char:59.

If I add -identity before $_.samAccountName i get the same error.

the example about has a ` before -ObjectAttributes, I though perhaps that was it but then I just get a >> like it is looking for more input. I know I am missing something by I cannot see what.

Reply
37 Eric January 9, 2017 at 10:30 am

sorted it out:

Import-Csv -path H:\massupdates.csv | ForEach-Object { Set-QADUser -Identity $_.samaccount -ObjectAttributes @{“User-CommissionClass”=($_.commissionclass)}}

Name Type DN
—- —- —
Eric user CN=Eric ,OU=xxxxxx,OU=Employees,OU=Users,OU=Accounts,OU=Corp,DC=…

Reply