AD bulk user import - using csvde
From Jonsdocswiki
CSVDE is a command line application for importing large numbers of users into Active Directory based on information contained within a .csv (Comma Separated Value) file. It's important to note there are limitations to CSVDE but these limitations can be mitigated with scripts and other techniques.
Contents |
Limitations
- You cannot set passwords using CSVDE
- You cannot specify AD group memberships in the CSV file.
- If the memberOf attribute (column) is present in the CSV file then the CSVDE import will fail.
- Home directories won't be created by CSVDE although you can populate this field.
- The homeDirectory attribute is used.
Building a suitable CSV file
For ease I start by working in a spreadsheet program that supports concatenation and cross-sheet referencing (Excel or OpenOffice.org Calc). As a number of the fields contain the same values, or can be built out of data in other areas of the sheet, the concatenation function will become a trusted friend.
For added ease, you can download my blank workbook (File:ExampleCSVDEExcel.zip) that contains the following attributes:
Required attributes
- DN - Distinguished Name.
- Gives the full location of the object in AD.
- objectClass
- The type of object this line refers to.
- cn - Common Name
- The object's name. For a user this could be their full name e.g. Jonathan Haddock
- sAMAccountName
- The Windows 2000 username
- Username
- The username (like the userPrincipalName but without the @domain component)
- userPrincipalName
- The Windows XP and above username (complete with @domain component).
- If you're using Desknow with AD integration, a userPrincipalName must be present or the user will be unable to logon.
- objectCategory
- The category as defined in the schema.
- Don't ever mess in the AD Schema unless you're 100% sure you know what you're doing!
Useful (optional) attributes
- sn - Surname
- givenName
- For example, the user's first name (Jonathan).
- description
- Brief additional information about the user (e.g. users that joined the school in 2011)
- displayName
- Name to show when various GUI elements would display your name (e.g. top of the start menu)
- name
- More name data
- profilePath
- if not specified a local profile is used.
- Backslashes must be escaped, for example \\server\profile becomes \\\\server\\profile
- homeDirectory
- Remember, CSVDE won't create home directories / set permissions on the server.
Whether you use my blank workbook (File:ExampleCSVDEExcel.zip) or choose to create your own, you'll need (although some of the Required above are only strong recommendations) the fields from the Required section. These should be on row 1 and from memory are case sensitive.
If using my blank file
Fill in the Setup sheet
If you're using my blank workbook (File:ExampleCSVDEExcel.zip) then you'll find the Setup tab incredibly useful. As I mentioned earlier, a small amount of concatenation saves a lot of work when creating a CSVDE file. On the Setup tab you'll find a set of labels in column A and their corresponding attribute in column B:
- Cell B1: distinguishedName stem
- the distinguishedName stem is used to build up the exact path to the object. Without a DN field CSVDE can't place the object anywhere in the Active Directory and the import will fail.
- Cell B2: userPrincipalName stem
- Cell B3: mail stem
- Cell B4: homeDirectory stem
- Cell B5: Description
- Cell B6: profilePath stem
- Cell B7: Username Prefix (any characters you'd like to prefix the username)
- Cell B8: objectCategory
- Cell B9: objectClass
In my workbook, data is taken from these and, using the concatenate function, populates various cells saving time :) Simply populate the B column with appropriate values; examples:
- Cell B1: OU=CSVDETutorial,OU=User,DC=JONSDOCS,DC=ORG,DC=UK
- Cell B2: @jonsdocs.org.uk
- Cell B3: @jonsdocs.org.uk
- Cell B4: \\\\homesvr\\homes\\
- Note that backslashes (\) must be escaped, hence the UNC path having 4 backslashes at the beginning. To escape a backslash, proceed it with a backslash.
- Remember, CSVDE won't create home directories on the server or set the permissions.
- Cell B5: CSVDE Tutorial User
- Cell B6: \\\\profilesvr\\profile
- Cell B7: 10-
- Cell B8: CN=Person,CN=Schema,CN=Configuration,DC=BCGS,DC=LOCAL
- Unless you have a very good reason, don't change this value!
- Cell B9: user
- Unless you have a very good reason, don't change this value!
Populate values in the FormulaVersion sheet
A number of the columns in this sheet will self-populate based on data from the Setup sheet, data you enter and the result of the CONCATENATE function. You will need to populate:
- Column D (sn - surname)
- Column F (givenName - user's first name)
Assumptions made in the FormulaVersion sheet
Based on this data, the following assumptions are made by the sheet to populate the other cells:
- That you want usernames to be of the form prefix (From the setup sheet) then first initial (from givenName) then surname (from sn)
- e.g. If the Username Prefix is 10-, the givenName Jonathan and the sn is Haddock the username will be 10-jhaddock.
- The username is calculated in the U column of FormulaVesion and has a yellow background)
- That you want email addresses to be username@mail stem (Username is calculated based on the above assumption, mail stem is entered on the setup sheet)
Duplicate checks
The V column in FormulaVersion contains a duplicate check. Put simply it checks if the value for the username is the same as the value on the row beneath. In the event of a duplicate column V will show True, otherwise you'll see false. In the event of AD encountering a duplicate samAccountName the import will fail so it's worth checking you have no duplicates.
If you're starting a fresh and going it alone
You're more than welcome to not use my blank file and to create your own. You'll need the column headings from the required list above. Then just populate your sheet with the desired values.
Importing the users
Once you have successfully populated your sheet you'll need to save it as a CSV, however, if you've used any formulas to create your list you won't be able to simply go File > Save As because your CSV would contain very little useful data.
Copy the values only into a new sheet
- Create a blank sheet if you haven't got one in your workbook already.
- Select your data, including the first row with the attribute headings.
- If you're using my blank file, you don't want columns U and V from the FormulaVersion sheet, these are only used to build the rest of the data and have a yellow background.
- Choose Copy
- Browse to your blank sheet and use Paste special (or equivalent) to paste the values into the blank sheet. This removes any formulas.
- With your formula-free sheet selected, go File > Save As and save it as a CSV. You'll probably be warned that only the active sheet was saved and that's fine - it's exactly what you want!
Run the import
Now it's time to transfer your CSV to the server and run the csvde tool to perform the import. I'm assuming you're ok copying the file to the server so we'll jump straight onto the command line:
| Using csvde |
|
Microsoft Windows [Version 5.2.3790] |
Any errors (for example, caused by duplicate entries, will be reported and you'll need to rectify these. Note that any imports prior to the problematic row will have happened.
Where next?
If you browse to the relevant location in Active Directory you should find your users all created and waiting. They'll have no password (as CSVDE can't create passwords) and they'll appear as disabled. Additionally, no home directories will have been created for them. It's possible to create home directories en masse using the Active Directory Users and Computers tool - see how to do this.
You'll need to also set passwords for all the users and enable the accounts when they need to be used (this may not be immediately of course). Fortunately, there are instructions on Using Visual Basic Scripting to set passwords for users in an OU to help you.
