Updating AD attributes from an Excel spreadsheet

Posted on by Carol

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.

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

jbloggs department IT Helpdesk
jbloggs mobile 0123456789
hzhen description Administrative Assistant

I've been doing IT for 30 years, and IdM for 15. I live in Australia and build IdM solutions based on Microsoft Identity Manager. I also play the violin, but that doesn't help much with the IdM solutions.

2 Replies to “Updating AD attributes from an Excel spreadsheet”

  1. Hi,

    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.”
    Else
    wscript.echo CN & “, ” & attrib & ” successfully updated.”
    End If

    End If

    This checks if objUser.Get(attrib) is not populated. Working really nice.

    Thanks!

    Micke from the sunny Ã…land Islands in Finland

Comments are closed.