Friday, December 14, 2012

Bulk Import to ActiveDirectory using CSVDE

Preparation - Importing Objects with CSVDE:

Learn the precise spelling of LDAP fields, like sAMAccountName , etc and would cause the import to fail.
Example 1: A Simple Import with Minimal LDAP Fields

lets 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.

Instructions to Create a Simple CSVDE Import Spreadsheet

1) Copy the below example below and paste into an Excel spreadsheet at precisely cell A1.

user,Petergr," CN=Peter Graham,OU=Newport,DC=cp,dc=com"
user,Janiebo," CN=Janie Bourne,OU=Newport,DC=cp,dc=com"
user,Edgardu," CN=Edgar Dunn,OU=Newport,DC=cp,dc=com"
user,Belindaha," CN=Belinda Hart,OU=Newport,DC=cp,dc=com"
user,Mayja," CN=May Jamieson,OU=Newport,DC=cp,dc=com"
user,Leroyot," CN=Leroy Ota,OU=Newport,DC=cp,dc=com"

2) In Excel, select the Data Menu and then Text to Columns.  Naturally, choose the comma delimiter. Save the file as .csv for example, Newort.csv
3) Make sure that the 3 LDAP fields are in the first row.  (ObjectClass, sAMAccountName, and DN.)
4) Once you have opened the file in Excel, it is easier to manipulate the values.  For example, you may wish to find and replace dc=cp, dc=com with the name of your domain as we discussed earlier.
Excel Save as type CSV Comma delimited5) When you have finished preparing the spreadsheet to your liking, then Save As and make sure you select Save as type CSV (Comma delimiter).  Since the next step is the command prompt, save the file into an easily accessible folder. E.g. C:\csv.

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.

No comments :