Importing and exporting to and from Active Directory

As you probably know, setting up user accounts through the Active Directory Users And Computers console can be very tedious. Sure, going through the basic wizard for adding users isn’t too bad, but when you have to start entering addresses, phone numbers, and that sort of thing, the process can become really time consuming. In fact, I just spent the last half hour creating nine user accounts in this way.

There is a better way though. Suppose for a moment that you have 150 new employees starting on Monday and they all need user accounts. You could either spend the entire weekend filling in the information, or if you can talk the HR department into giving you the information in electronic format, you can import the information directly.

About CSVDE
For your purposes, CSVDE is the tool of choice because the data that it exports will be in CSV format. This means that the data can be imported into Microsoft Access or Microsoft Excel. Once you have the data imported into one of these tools, it is easy to modify it or add additional data and then re-import it into the Active Directory.

CSVDE isn’t the only tool at your disposal though. Another tool called LDIFDE also exists in the %SYSTEMROOT%\SYSTEM32 folder. LDIFDE is also capable of importing and exporting Active Directory information. The difference between the two tools (aside from the syntax) is that the import and export does not use the CSV file format and is therefore incompatible with Microsoft Access and Excel.

Instead, the format used by LDIFDE conforms to a standard called LDIF. LDIF stands for LDAP Data Interchange Format. LDIF is basically a standard that can be used to import, export, or modify large batches of data through an LDAP query. This tool can be used to import and export data into any LDAP-based directory that conforms to the LDIF standard. You aren’t just limited to the Active Directory.

Exporting user data
The easiest way to export user data through either of these tools is to create an OU and then move the users that you want to export into that OU (at least temporarily) You can then tell the tool to export all of the Active Directory objects in the entire OU. For example, on my test network, I have created nine user accounts in an OU called sample.

Since this article is more about the CSVDE tool, I really don’t want to get into an involved discussion on LDIFDE, but I do want to at least show you the difference between LDIFDE and CSVDE. To export the user objects using LDIFDE, I used the following command: LDIFDE –F users.ldf –S bart –D ”OU=sample,CN=test,CN=com”

In the above command, the –F switch designates the export filename. In this case, the file name is USERS.LDF. The –S switch tells the LDIFDE command which domain controller to extract the information from. In this case, the domain controller’s name is BART. You can see the entire process shown in Figure A.

The file that you create will be in LDF format. You can download a copy of USERS.LDF so that you may use it as a sample though.

There’s really no way that you could ever hope to import an LDF file into Microsoft Access. Now, let’s look at the same process, but performed with the CSVDE tool. This time the syntax is as follows: CSVDE –F users.csv –S bart –D “OU=sample,DC=test,DC=com”

At first glance, this command syntax appears to be identical to that used with LDIFDE, but there is a difference. Notice that LDIFDE required you to use CN to designate your domain name. CSVDE on the other hand requires the use of DC instead. As you can see in Figure B., the export process looks a lot like that used with LDIFDE, but Figure C shows that the output is much different. I have included the CSV file with this article so that you may experiment with it on your own.

Modifying the CSV File
The technique that you will use to modify the CSV file really depends on whether you prefer to work in Microsoft Excel or in Microsoft Access. If you like to work in Excel, just double-click on the file and it will be imported into Excel. Notice in Figure D that the top row of the spreadsheet lists what each column contains. You might have noticed in Figures A and C that ten objects were exported when my sample OU only contained nine. This tenth object was the schema information listed on this top row.

Using Excel, you can modify existing data or add new users. When you’re done, just use the File | Save As command and save the data in CSV format.

Importing the data into Microsoft Access is a bit more involved. To do so, open Access and create a new blank database. Now, choose the Get External Data | Import commands from Access’s File menu. You will now be prompted for a file to import. Specify that you want to import a text file (which includes CSV) and then select the CSV file that you wish to import and click OK.

At this point, Access will launch the Import Text Wizard. The first screen asks if the data is delimited or of a fixed width. Select Delimited and click Next. On the next screen, select Comma as the delimiter and select the First Row Contains Field Names check box, as shown in Figure E.

Depending on what types of data you have imported, you may receive a message stating that the top row has some columns that don’t use valid field names. In this case, Access will create its own field names. An example of such a field name might be a blank field name that was changed by Access to Field59. If you have such fields, it’s usually best to either delete them (which may result in data loss) or to simply work in Excel instead.

Click Next and Access will ask you if you want to import the data into a new table or into an existing table. Since there are no tables, use the New Table option. Click Next to skip the next screen and choose No Primary Key on the following screen and click Next. The wizard’s final screen will set the new table’s name to Users. Click Finish to acknowledge this and import the data. When you are done, the data will appear in Access. You can download a sample Access database here.

The process for exporting the data back out of Microsoft Access is almost identical to importing the data. Simply select the table containing your data and then select the Export command from the file menu. Give the exported file a CSV file name and select Text Files from the Save As Type drop-down list. When you do, Access will open the Export Text Wizard. Just answer the questions in the same way that you did earlier and the data will be exported to a CSV file.

Importing Your CSV File
Importing the CSV file that you created is much easier than exporting it. That’s because you don’t have to specify a location within the Active Directory because the location is already documented within the import file. Instead, just use the –I switch to specify an import, the –F switch to specify the file name, and the –S switch to specify the server. You can see an example of the syntax below. If you receive errors, you can use the –K switch to ignore the errors and process valid data. CSVDE –I –F users.csv –S bart –K

TechRepublic originally published this article on 7 January 2004.