Wednesday, February 24, 2010

CSVDE command to import User Accounts in Active Directory

Preparation - The secrets of importing objects with CSVDE:

1) Practice with CSVDE Export. Remember that seven minutes of planning will save you an hour or rework. It may seem eccentric to start with an export when all you want to do is import, but trust me always start with CSVDE export. Here is my reasoning: gain experience of CSVDE switches in conditions where you can do no harm to Active Directory.

When you use CSVDE -i filename to import user accounts, you make changes that will be difficult to reverse. In export mode, you can do no harm to Active Directory, also starting with CSVDE -f filename.csv will help build up your understanding of the switches, or what Microsoft call the parameters.

2) Learn the precise spelling of LDAP fields, for example sAMAccountName is correct, sAMAaccountNames has two mistakes and would cause the import to fail. (This LDAP name is singular, and the double Aa is incorrect.)

3) Create a good .CSV file and learn as much as you can about the LDAP fields in the first row. When you export a user, you get a spreadsheet full of a LDAP attributes, for example, sn, phone, email and many more besides. My advice is to investigate which of the LDAP fields in the first row are compulsory, which are optional, which have strange numeric data, and which you can safely ignore when you switch CSVDE to import mode.

Example 1: A simple import with minimal LDAP fields

My theme is getting you started. Imagine the scenario: your manager wants 500 users added to his Windows 2003 domain. Fortunately, human resources have all the new joiners in a spreadsheet called Newport.csv. So, let us begin with a simple spreadsheet with only 3 LDAP columns: objectClass, sAMAccountName and DN.

LDAP Fields for Excel  DN sAMAccountName, objectClass

A) objectClass - User. Simple and easy we want to create a user and not a computer and not an OU.

B) sAMAccountName - This is the logon name, maximum of 11 characters. What the user should put in the Ctrl, Alt Delete logon box. Keep this name simple for now. Remember we just want to get the prototype import working and then we can add more LDAP fields.

C) DN - Distinguished name, for example, CN= Firstname Surname,OU=Newport,dc=domain,dc=com

DN is the hardest LDAP field to create. Let us break it down into 3 elements.

1) User name - CN= Firstname Surname. If it were me, the value would be CN=Guy Thomas. In this context think of CN= as meaning common name, or just plain name.

2) Organizational name - OU=Newport. All you have to worry about is have you created an OU called Newport in your domain? If not, then either create one, or change this value to OU=YourOU.

3) Domain name - dc=domain, dc=com. Is your domain called something like or is it plain mydom (no .com, .net or It is essential to find out what your domain is called, and only you know the answer.

CSVDE bulk importWhat would you say the Domain name is for this screen shot? cp,, cp.local? The answer is

So of this were your domain the third DN element would be, dc=cp,dc=com. Incidentally, dc stands for domain context not domain controller.

The Actual CSVDE -i import command

After all the hard work in preparing the spreadsheet, we are now ready for the import. Open the CMD prompt, navigate to the folder where you saved your .csv file.

Type this command: CSVDE -i -f Newport.csv

To check your new users, launch Active Directory Users and Computers and examine the Newport Organizational Unit. After each import, right click the OU and select Refresh from the short cut menu. Simply pressing F5 is not good enough.

Example of customized CSVDE command:

OU=Khadamat-FM users

c:\>csvde -f output.csv -d "OU=Khadamat-FM Users,DC=Local,DC=Khadamat-FM,DC=ae" -r ObjectClass=user -l "cn,Title,telephoneNumber,Mobile,mail"

this command will export the name,job title, tel no., mobile phone, and email address to output.csv file

No comments:

Post a Comment