You don’t always have access to a proper IdM system. At the moment I’m at an organisation which still uses the old, manual ways of updating AD. I was asked if I knew an easy way to update the mobile phone numbers of a list of users, the data having been sent to Helpdesk in a spreadsheet. I knew that a long list of departments were shortly to be changed as well, so I figured I’d make a general solution, where you could also specify the attribute name in the spreadsheet.
I actually ended up writing the script twice.
- First I used DNs as the search criteria, which makes the script quite simple, and also means it will work with any object type. You can see that script here.
- Then, after seeing that the source spreadsheet contained usernames rather than DNs, I modified the script to use the CN instead. The restriction is that now the script only works with user objects (though there’s actually no reason why you couldn’t make a column for the object type in the spreadsheet as well). That script is here.
It needs to be noted that these scripts should only be used for updating single-valued attributes. I have included no back-out – so if you have the wrong data in your spreadsheet I accept no responsibility for you putting rubbish in your AD.
You also need to make sure your spreadsheet is tidy, with even column lengths, and no blank row in the middle (the script stops at the first blank cell).
For the CN script your spreadsheet should look something like this:
2 Replies to “Updating AD attributes from an Excel spreadsheet”
Thanks for sharing a very useful script!
I noticed some problem if some fields in Excel are in number format instead of text, in that case it didn’t work. I made this little adjustment to solve this:
valueS = CStr(value)
objUser.Put attrib, valueS
Using “CStr” converts any variable to string format, looks like it’s working better.
Also found out that the script reported “successfully updated” even if a value not have been updated, for example because the above problem. Made this little adjustment:
If objUser.Get(attrib) = valueS Then
If IsNull(objUser.Get(attrib)) Then
wscript.echo CN & “, ” & attrib & ” NOT updated.”
wscript.echo CN & “, ” & attrib & ” successfully updated.”
This checks if objUser.Get(attrib) is not populated. Working really nice.
Micke from the sunny Ã…land Islands in Finland
Brilliant – thank you!
Comments are closed.