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 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
Subscribe by email
Can’t this be slightly modified to create users too?
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
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.
How to deal with attribute that has dash ‘-‘ in it?
I got an error: “Missing ‘=’ operator after key in hash literal.”
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
How i run this update from a schedule task?
Thanks
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/)
Here’s a detailed post on scheduling PowerShell scripts: https://dmitrysotnikov.wordpress.com/2011/02/03/how-to-schedule-a-powershell-script/
Excelent, Thank you!
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?
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
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 }
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?
Yes, just use -SearchRoot parameter. See http://wiki.powergui.org/index.php/Get-QADUser
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
This is very weird. Can you try posting the issue to the PowerShell and AD forum at http://powergui.org?
Thank you Dmitry, saved my day
My pleasure! 🙂
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
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
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!
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. 😉
Thanks mate!
Have followed your advice and posted on the AD and PowerShell forum at http://powergui.org
Allan
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
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
Worked great, Thanks
hi,
I want to disable users from exported csv file. I don’t want to use any quest application to diable user.
Microsoft’s own AD cmdlets now also have Disable-ADUser: http://technet.microsoft.com/en-us/library/ee617197.aspx
When using this I found that the attribute userPrincipalName was also changed if it does’nt match your samaccountname
third party tools available with more advanced options..we can try these from manageengine, quest,netwrix,adsysnet,lepide and more..
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.
Hmm…. Try being more explicit with Import-CSV parameters. E.g.: Import-CSV -path “c:\update.csv”
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}
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
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.
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.
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=…