Sunday, September 21, 2014

Active Directory - Update AD users's field data starting with number ZERO from a csv file

Last Friday, when I was trying to update Active Directory users data from a csv file, I accidentally changed value of a very important attribute (let's call it the X field).

X was a string of numbers which has a number ZERO at the beginning. And my company uses X to identify users in another system which requires ids in the form of 0xyz, and 1xyz. I exported all the users AD data, modified value of some of the fields, than updated back to Active Directory. I realized that X value of all users had been changed (without ZERO at the beginning).

I totally forgot that csv/excel will ignore any number ZERO if it stays at the beginning of the number (e.g. 061898989 will be translated to 61898989). So, to change the value back to what It was, I did:

1. Export X value data of all the users to a csv file.

2. Add ZERO to the beginning of attribute X's data of all users (using a text editor, find & replace)

3. Make all field were quotes ("")

4. Update the modified data back to Active Directory

P/S: to bulk update AD data, you can use a free tool names Bulk AD Users