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 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!

Tags: , , , , , , ,

37 Responses to “Update AD from CSV”


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

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

  2. 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

    • 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.

  3. 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.”

  4. 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

  5. 6 Ozk4r February 3, 2011 at 1:24 am

    How i run this update from a schedule task?

    Thanks

  6. 9 Oscar February 9, 2011 at 12:23 am

    Excelent, Thank you!

  7. 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?

  8. 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 }

  9. 17 Vladimir Valchev September 11, 2011 at 10:21 pm

    Thank you Dmitry, saved my day

  10. 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

    • 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

      • 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.😉

  11. 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

    • 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

  12. 26 Joe April 4, 2013 at 2:14 pm

    Worked great, Thanks

  13. 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.

  14. 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

  15. 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..

  16. 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.

    • 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”

  17. 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}

  18. 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

  19. 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.


  1. 1 Skip empty CSV fields « Dmitry’s PowerBlog: PowerShell and beyond Trackback on October 7, 2008 at 8:00 am
  2. 2 Blogs for Technological Boniks - Update the information of domain users by CLI or Powershell Trackback on July 12, 2012 at 6:47 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




My Recent Tweets

Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

October 2008
M T W T F S S
« Sep   Nov »
 12345
6789101112
13141516171819
20212223242526
2728293031  

%d bloggers like this: