Update AD Users in Bulk from Excel Spreadsheet

A PowerShell V1 script to update users (or any class of AD objects) in bulk from the information in a Microsoft Excel spreadsheet. Only single-valued string attributes are supported.

UpdateUsers.ps1
 
 
 
 
 
4 Star
(18)
9,862 times
Add to favorites
Active Directory
9/17/2011
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Sample Excel Document
    1 Posts | Last post March 07, 2017
    • Hi Richard,
      
      I am fascinated with your PS script.  Do you happen to have a sample document with examples of multiple users to have their info updated? 
  • Error
    2 Posts | Last post January 22, 2017
    • Hi am getting this in the log.
      ------------------------------------------------
      UpdateUsers.ps1 Version 1.0 (September 12, 2011)
      Started: 17-01-2017 13:38:02
      Spreadsheet: c:\Scripts\UpdateUsers.xls
      Log file: c:\Scripts\PSUpdateUsers.log
      NetBIOS name of domain: COMPANY\
      ## Attribute description has a syntax that is not supported
      ## Attribute Status not found in schema
      ## Attribute ADsPath not found in schema
      ## Attribute Password not found in schema
      Program aborted: 17-01-2017 13:38:03
      
      Anyone that knows about this error.
    • This program only supports single-valued AD attributes. Unfortunately, the description attribute is multi-valued. I know it never has more than one value, but in the schema it is actually multi-valued. As for the others, there are no attributes with names Status, ADsPath, or Password. ADsPath is actually an ADSI method (function). The closest equivalent is distinguishedName, but it cannot be modified directly (nor can ADsPath). Instead the object must be moved. A method is required to assign passwords: you cannot directly assign a value to any password attribute. And by Status you may be referring to the flag settings in the userAccountControl attribute. Different bits of this integer attribute specify settings like Enabled, Password Not Required, Smartcard Logon Required, etc. All of these settings would require considerably more code to invoke the methods necessary to assign values.
  • The directory service cannot perform the requested operation on the RDN attribute of an object
    1 Posts | Last post July 01, 2014
    • I have modify as mention all needed lines in order to modify the CN attribute but Im getting the following error after running the script:
      The directory service cannot perform the requested operation on the RDN attribute of an object
      
      Please help !!!
      
  • Update SamAccountName
    1 Posts | Last post May 14, 2014
    • I have a CSV File with 2 columns. One with the current SamAccountName and one with the desired SamAccountName.
      Is it possible to update the SamAccountName with a Quest powershell script?
  • CSV
    1 Posts | Last post February 21, 2013
    • What is the specific format for spreadsheet, or do you have an example one?  I've tried creating one with username and manager and i get "Attribute manager has a syntax that is not supported"
      
      spreadsheet is setup with 2 columns sAMAccountName and manager (tried Manager_sAMAccountName) as well.
      
      Just trying to update the manager of a couple hundred users.
  • What Version of excel
    2 Posts | Last post February 20, 2013
    • Hi Richard, im trying to use your script but when a run de script the log said:
      
      UpdateUsers.ps1 Version 1.0 (September 12, 2011)
      Started: 10/01/2013 07:01:04 p. m.
      Spreadsheet: C:\Users\alvaro.ceballos\Desktop\TestAD\CargaTest01.xlsx
      Log file: C:\Users\alvaro.ceballos\Desktop\TestAD\log\UpdateUsers.log
      ## Excel spreadsheet not found: C:\Users\alvaro.ceballos\Desktop\TestAD\CargaTest01.xlsx
         Exception has been thrown by the target of an invocation.
      Program aborted: 10/01/2013 07:01:05 p. m.
      
      Can you help me please i´m new using powershell with AD
      
      regards
    • Hi Alvaro, I solved the same error doing the following:
      
      1) In the script include this function:
      
      Function Invoke([object]​$m, [string]$method​, $parameters)
      {
      	$m.PSBase.GetTy​pe().InvokeMemb​er($method, [Reflection.Bin​dingFlags]::Inv​okeMethod, $null, $m, $parameters,$ci​US)	
      }
      
      2) And add and modify the following code lines as you see next:
      
      # US culture info
      $ciUS = [System.Globali​zation.CultureI​nfo]'en-US'
      
      # Open specified Excel spreadsheet.
      $Excel = New-Object -ComObject "Excel.Applicat​ion"
      #$Workbooks = $Excel.Workbook​s
      $Step = "4"
      #$Workbook = $Workbooks.Open​($ExcelPath)
      $Workbook = Invoke $Excel.Workbook​s Open $ExcelPath
      
      3) Also you probably will have to apply this workaround because of different languages between Excel and OS locale: http://blogs.ms​dn.com/b/eric_c​arter/archive/2​005/06/15/42951​5.aspx
      
      
      I hope it will be helpful.
      
      Regards,
      
      Manuel
  • SID instead of Sam or DN
    3 Posts | Last post February 20, 2013
    • are you able to modify this to use the users SID as it is unique to each user instead of SAM or DN?
      
      btw i love the look of the Script are you still working on Ver 2 to allow all update properties? such as manager.
    • I made good progress on a version to handle more attributes, but set it aside. A lot of things needed to be invented, and a lot of testing is required. I may post what I have so far, but the script will get fairly long. It actually would be easy to identify users by SID, if you mean the "friendly" form similar to S-1-5-21-xxxxxxxx-xxxxxxxxx-xxxxxxxxx-xxxx. I could add that feature.
    • Hi Alvaro, I solved the same error doing the following:
      
      1) In the script include this function:
      
      Function Invoke([object]$m, [string]$method, $parameters)
      {
      	$m.PSBase.GetType().InvokeMember($method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters,$ciUS)	
      }
      
      2) And add and modify the following code lines as you see next:
      
      # US culture info
      $ciUS = [System.Globalization.CultureInfo]'en-US'
      
      # Open specified Excel spreadsheet.
      $Excel = New-Object -ComObject "Excel.Application"
      #$Workbooks = $Excel.Workbooks
      $Step = "4"
      #$Workbook = $Workbooks.Open($ExcelPath)
      $Workbook = Invoke $Excel.Workbooks Open $ExcelPath
      
      3) Also you probably will have to apply this workaround because of different languages between Excel and OS locale: http://blogs.msdn.com/b/eric_carter/archive/2005/06/15/429515.aspx
      
      
      I hope it will be helpfull.
      
      Regards,
      
      Manuel
  • I know this is old but could use some help
    4 Posts | Last post September 20, 2012
    • Anyway to include the abillity to rename an object?  This script works perfectly for what I need, I just need to be able to rename the "Name" attribute based on the excel spread sheet.  Any help you can give me would be very much appreciated.  I've figured out that you can't just "update" this attribute, you have to rename it.
      
      thanks!
    • Hi there...
      Here is the piece of code I'm using:
      
      (...)
      # Create the User
      New-ADUser -Name $strUserName `
                 -SamAccountName $strUserName `
                 -UserPrincipalName $strUPName `
                 -Surname $strLastName `
                 -GivenName $strFirstName `
                 -AccountPassword (ConvertTo-SecureString -AsPlainText $strPwd -Force) `
                 -Enabled $true
      # Find the Identity of the User you just created
      $newdn = (Get-ADUser $strUserName).DistinguishedName 
      # Rename the User you just created
      Rename-ADObject -Identity $newdn -NewName $strFullName
      (...)
      
      Francis
    • The attribute name in the spreadsheet (to rename users) should be "cn", not "name". First, you need to remove the following lines of code:
      =====
          # This script cannot be used to rename users.
          If ($value.ToLower() -eq "cn")
          {
              Add-Content -Path $LogFile -Value "## This script cannot be used to rename users"
              Add-Content -Path $LogFile -Value "   Do not specify the cn attribute in the spreadsheet"
              Add-Content -Path $LogFile -Value $("Program aborted: " + (Get-Date).ToString())
              CleanUp
              Return "Program aborted: cn attribute found in spreadsheet" `
                  + "`nSee log file: $LogFile"
          }
      =====
      Next, change this line of code:
      =====
                                      If ($AttrName.ToLower -eq "samaccountname")
      =====
      to this instead:
      =====
                                      If (($AttrName.ToLower -eq "samaccountname") Or ($AttrName.ToLower -eq "cn"))
      =====
      Finally, replace these lines of code:
      =====
                                      # Assign the new value to the attribute.
                                      $User.Put($AttrName, $Value)
                                      $Changed = $True
      =====
      with the following:
      =====
                                      # Check for rename.
                                      If ($AttrName.ToLower -eq "cn")
                                      {
                                          $User.psbase.rename("cn=$Value")
                                          $Changed = $True
                                      }
                                      Else
                                      {
                                          # Assign the new value to the attribute.
                                          $User.Put($AttrName, $Value)
                                          $Changed = $True
                                      }
      
      =====
    • Sorry, in the code I just posted, "Or" should be "-Or" in this line:
      =====
      If (($AttrName.ToLower -eq "samaccountname") -Or ($AttrName.ToLower -eq "cn"))
      =====
  • Problem changing Attributes
    4 Posts | Last post March 07, 2012
    • Hi,
      
      The atributes accountExpires, manager, description, etc. cant modify using this script, how i can bulk Update this atributes??
      
      ty
    • I am working on another version of this program that can handle those attributes, but it is a big undertaking. The accountExpires attribute is Integer8, which is a large 64-bit integer representing dates. I believe the AccountExpirationDate method must be used to modify this, but I haven't figured out how to invoke it in PowerShell yet. The manager attribute is DN. The description attribute is multi-valued (even though there can be only one value).
    • Until I finish the PowerShell version of the program that handles those attributes, I have a VBScript version on my web site that you can use. It is called UpdateUsers2.vbs and is linked on this page: http://www.rlmueller.net/UpdateUsers.htm
      
    • This is a nice Script "Richard"
      keep it up :)