• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

How can I use Excel or CSV files to modify an Exchange Custom Attribute in Active Directory?

I need to use Exchange Custom Attribute 15 to allow for display of our internal 7-digit dialing (xxx-xxxx)... I have edited the template within Exchange 2007 so that it points to, and displays, the data contained within this attribute. I also have a spreadsheet containing the user account name, email address, etc. and have added a column that matches the users to reflect the data I wish to populate within the attribute...
I can manually edit the attribute within ADUC but would like to use a script or another method since I have some 2800 users to update...
Any help is appreciated!
0
gdozier
Asked:
gdozier
  • 4
  • 2
1 Solution
 
aces4all2008Commented:
Something similar to the following cmd file should work for you.  You'll need to run it from a server 2003 machine (something with ldifde.exe anyway) and modify it to match your environment.  The script is set up to use csv file with only 2 fields: username and attribute value.  You should test this before using it on all 2800 users.
Set CsvFile=InputFile.csv
Set LdfFile=CustomAttr.ldf
Set AttrName=Name
Set LogFIle=ImportLog.txt
 
 
for /f %%a in (%CsvFile%) do call :MakeLdf %%a %%b
Call :WriteAttributes
 
Goto :End
 
:MakeLdf
Set UserID=%1
Set AttrValue=%2
 
for /f "delims=|" %%i in ('dsquery user -samid %UserID%') do echo dn: %%i>>%LdfFile%
echo changetype: modify>>%LdfFile%
echo replace: %AttrName%>>%LdfFile%
echo %AttrName%: %AttrValue%>>%LdfFile%
echo ->>%LdfFile%
echo.>>%LdfFile%
 
Goto :End
 
:WriteAttributes
 
ldifde -i -f %LdfFile% 
 
:End

Open in new window

0
 
gdozierAuthor Commented:
so to be sure I understand; this script takes a csv file with the two fields (username; attribute) and makes the ldf file which is then used to populate the attribute?
If I am missing something, please elaborate... I have a test domain on which I can test with using any number of users... If it works for 10 should work for all, correct?
0
 
gdozierAuthor Commented:
On another note, when the script is copy/pasted; in what type of format should the file be saved in order to utilize? And do I call the file using ldifde?
I know I appear ignorant and perhaps for good reason!  :)
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
gdozierAuthor Commented:
Works perfectly with one exception: When the LDF file is created it places the DN data within quotes which causes a syntax error... Do a "find/replace" on the LDF file to remove the quotes and then manually run ldifde against the edited LDF file and all is good to go... I simply couldn't ascertain where the quotes were being inserted; if you can correct this in the above script then it will work without any edits other than those required for each environment...
0
 
aces4all2008Commented:


"...when the script is copy/pasted; in what type of format should the file be saved in order to utilize?"

Save it as a cmd file using notepad

"...this script takes a csv file with the two fields (username; attribute) and makes the ldf file which is then used to populate the attribute?"

Yes

"...If it works for 10 should work for all, correct?"

Yes

"...Works perfectly with one exception: When the LDF file is created it places the DN data within quotes which causes a syntax error... "

See code snippet below


Good luck to you





Set CsvFile=InputFile.csv
Set LdfFile=CustomAttr.ldf
Set AttrName=Name
Set LogFIle=ImportLog.txt
 
 
for /f %%a in (%CsvFile%) do call :MakeLdf %%a %%b
Call :WriteAttributes
 
Goto :End
 
:MakeLdf
Rem Generates ldf file for use with ldifde application from csv fil
 
Set UserID=%1
Set UserDN=
Set AttrValue=%2
 
for /f "delims=|" %%i in ('dsquery user -samid %UserID%') do Call :FormatDN %%i
echo dn: %UserDN>>%LdfFile%
echo changetype: modify>>%LdfFile%
echo replace: %AttrName%>>%LdfFile%
echo %AttrName%: %AttrValue%>>%LdfFile%
echo ->>%LdfFile%
echo.>>%LdfFile%
 
Goto :End
 
:WriteAttributes
Rem Writes attributes from ldf file to active directory
 
ldifde -i -f %LdfFile% 
 
Goto :End
 
:FormatDN
Rem Removes quotes (first and last characters) from around distinguished name
 
Set UserDN=%1
Set UserDN=%UserDN:~1,-1%
 
Goto :End
 
:End

Open in new window

0
 
gdozierAuthor Commented:
Perfect!! Thanks for the code addition in addition to your previous assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now