Custom Search

---

---

Guy recommends:
Free network monitor

Solarwinds' free monitor makes it easy to check the performance and health of a router or firewall.

Download your free Network Device Monitor

---

CSVDE Import

Introduction to CSVDE -i Bulk Import

The purpose of this page is to show you how to bulk import user accounts into Active Directory using CSVDE.  Here are scenarios where CSVDE will save you repetitive work:

• To create hundreds of new users in a Windows Server 2003 or 2000 domain.
• To import thousands of NT 4.0 users into a brand new Active Directory domain.
• To migrate directory services from Exchange 5.5 into Active Directory.

Topics for CSVDE Import

• Preparation - The secrets of importing objects with CSVDE:
• Example 1: A simple import with minimal LDAP fields
• The Actual CSVDE import command
• Troubleshooting CSVDE import
• Limitations of CSVDE - Password Problem
• CSVDE Summary

 ♦

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.

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 mydom.com?  or is it plain mydom (no .com, .net or .co.uk).  It is essential to find out what your domain is called, and only you know the answer.

What would you say the Domain name is for this screen shot?  cp,  cp.com, cp.local?  The answer is cp.com. 

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.

Guy Recommends:  Solarwinds' Free Bulk Import Tool

Import users from a spreadsheet.  Just provide a list of the users with their fields in the top row, and save as .csv file.  Then launch this FREE utility and match your fields with AD's attributes, click and import the users.  Optionally, you can provide the name of the OU where the new accounts will be born.

There are also two bonus tools in the free download, and all 3 have been approved by Microsoft:

1. Bulk-import new users into Active Directory.
2. Seek and zap unwanted user accounts.
3. Find inactive computers.

Download your FREE bulk import tool.

Instructions to Create a Simple CSVDE Import Spreadsheet

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

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.

5) 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.

Next step - Try an advanced import.  More fields, more spreadsheet functions.

Guy Recommends: Permissions Analyzer - Free Active Directory Tool

I like the Permissions Monitor because it enables me to see quickly WHO has permissions to do WHAT.  When you launch this tool it analyzes a users effective NTFS permissions for a specific file or folder, takes into account network share access, then displays the results in a nifty desktop dashboard!

Think of all the frustration that this free utility saves when you are troubleshooting authorization problems for users access to a resource.

Download Permissions Analyser - Free Active Directory Tool

Limitation of CSVDE - Password problem

CSVDE is unable to add passwords. 

Your solution is to change the domain policy to allow blank passwords.  You can revert to the previous security setting once you successfully import the accounts.  Also see this VBScript.

4) The key disadvantage of CSVDE is that you cannot set passwords with this program.  So, use a separate VBScript to set the passwords and enable accounts.  Sadly once created, you cannot you alter the accounts in anyway with CSVDE.  See here for a VBScript to reset passwords.

The point is use the right tool for the right job.  For a quick import of hundreds of user accounts, you cannot beat CSVDE.  However if you need to alter accounts or add passwords then turn to VBScript.  Both CSVDE and VBScript 'feed' off spreadsheets, to me, this is their killer advantage over LDIFDE.  I find that it is so useful to have all the accounts and their values set out in my Excel spreadsheet.

Troubleshooting CSVDE Import

• Check that you understand the: DC=domain, DC=COM (LDAP attributes).
• If your ADSI Edit says: DC=mydom, then change, dc=cp,dc=com to dc=mydom.
• Check you created an OU called Newport.
• Check out the Error Messages.
• Experiment with different syntax. Try a different output filename.
• Open and close the CMD command prompt.

Import CSVDE Bulk Import Summary

CSVDE is an ideal program to bulk import users into Active Directory.  The executable is built-in to Windows Server 2003 and 2008.  The key advantage of CSVDE is the way than it interacts with spreadsheets to import or export LDAP data.

See Also

• CSVDE Export Examples
• CSVDE Import Advanced
• CSVDE Import User Accounts
• Troubleshooting CSVDE Errors
• CSV Import Active Directory - Free Tool
• LDAP Fields
• LDIFDE Export
• LDIFDE Import

 *

Custom Search

Guy Recommends: WMI Monitor and It's Free! Windows Management Instrumentation (WMI) is one of the hidden treasures of Microsoft operating systems. Fortunately, Solarwinds have created the Free WMI Monitor so that you can actually see and understand these gems of performance information.  Take the guess work out of which WMI counters to use for applications like Microsoft Active Directory, SQL or Exchange Server. Download your free copy of WMI Monitor

Home Copyright © 1999-2012 Computer Performance LTD All rights reserved Please report a broken link, or an error.