Importing Data from AD

In Part 2 we  discussed how to export data from a working Active Directory installation. That was the easy part, now the real fun begins as we discuss how to import data to your Active Directory installation. It is strongly suggested to try this out on a test scenario, and try this out as much as need until you are certain that you know what the different switches do, as this will affect your installation of AD.

There are a few things that need to be done before you can import data to your Active Directory installation. First of all you have to create a CSV file with all the details of your users, which requires some basic knowledge of LDAP properties, manipulation of strings in Excel and some basic knowledge of scripting (which will later allow you to enable all the accounts you have created with a simple double click!).

First things first, what are LDAP Properties? Very simple, LDAP, or more fully Lightweight Directory Access Protocol, is an application protocol for querying and modifying directory services running over TCP/IP. A directory is a set of objects with attributes organized in a logical and hierarchical manner, or in plain English the properties of a user when you right click in Active Directory Users and Computers. There are countless options you can add, but for the sake of clarity we will stick to the 8 of the most basic ones you need to work with as described below:

Now that we know what the fields we need are, we can start creating a CSV file. The easiest way to do this is by using a spreadsheet application, in this case Microsoft Excel, which has a built-in function that will create a CSV file by separating the columns with commas. Let us start creating our template in Excel. We will just create the first row and fill in the rest by clicking and dragging

It is very important that before you start you make sure that you have created the Organizational unit you will add the users to, as CSVDE is unable to create Organizational Units. For this example the OU that has been created is named Test.

Below you have an example, simply fill in the first row in your sheet (the ones in bold). Next fill in only the givenName and SN columns.

Now the real fun starts, as the rest of the columns will be filled using string manipulation formulas that are built-in in excel. First of all we must know what naming convention we will adhere to; in this case it will be the first letter of the first name and the entire surname.

Let us start off easy, by concatenating the contents of two different cells so as to create the name and CN fields:

=B2&” “&C2       

This will concatenate cells B2 and C3 with a space in the middle.

The sAMAccountName for our user will be jdoe, and will be created as follows:

=LOWER(LEFT(B2,1) & (C2))        

This will take the first letter on the left of cell B2  ‘LEFT(B2,1)’ and concatenate to the whole contents  of cell C2 using the ampersand (&) and make sure it is all in lowercase (LOWER).

The process is similar for the userPrincipalName, but using the result of a previous formula:


This will concatenate the contents of cell D2 and append the name of the domain to it.

Finally the most complicated part, creating the Distinguished Name, which will use a combination of all the previous formulas to create a valid DN:

=”CN=”& B2 & ” ” & C2 & “,” & “OU=Test,dc=testdom,dc=com” This will create a field containing the complete distinguished name by concatenating a series of text entries to different other cells.

Now fill in the rest of the names and surnames and simply click and drag the other cells to completely fill the table for all your users.

To create a CSV file simply press F12 (Save As…) and select CSV (Comma Separated) as file type, click save and click ok on the prompt that pops up, which warns you that all formatting will be lost and that it won’t let you save multiple books.

The one CSVDE switch we will use to import is the -i switch. This will load the data from any CSV file that has been specified in the command, import the data into Active Directory, and you can also output a report log to a specified file.

The command to import is:

CSVDE -i -k -f test.csv

Let us break down the different switches:

-i                             The import switch which tells CSVDE that it has to import not the standard export.

-k                            Tells CSVDE to ignore all errors and continue importing data.

f <filename>    Specifies the file name of the CSV file to be imported.

Now launch Active Directory Users and Computers so as to verify the users have been created.

Tip: Before running the CSVDE command, browse to the location of the CSV file so at to avoid typing long UPN path names which make for needless errors. eg. If the CSV file is stored in the Administrator’s documents make sure you have browsed to C:\Documents and Settings\Administrator\ before running the CSVDE command.

However, CSVDE has a limitation; it does not offer the functionality to enable the accounts that have just been created. Do not worry though, you will not have to enable the hundreds of accounts you have added one by one. There are various scripts available online that, with little editing, can help you enable all these accounts, along with the various flavours of the LDAP Property UserAccountControl, this gives you some flexibility on how and what exactly you can do. A good set of scripts and explanations of how they work and how to edit them can be found at the Computer Performance website.