CSV Update

Intruduction

A CSV file is a convenient format to use for updating Active Directory attributes, as it's normally quite easy to extract data from various systems to a CSV file. A CSV file can also be created quickly in NotePad or Microsoft Excel.

Instructions

Create your CSV file - see the section entitled "CSV Update File Format" for more information.
From the "Bulk Modify" menu, select "CSV Update"
Step 1 asks for a CSV file. Click the folder icon to select your CSV file.
Step 2 asks you to select a binding column. The binding column is used to map rows in the CSV file to user accounts in Active Directory. Normally you would choose a unique attribute - sAMAccountName, userPrincipalName, ADsPath etc.
Step 3 asks you to select attributes to update. Review the list of attributes and make your selection. Ensure that the names correspond to Active Directory attribute names. Consult the Active Directory Schema Guide to ensure the correct name is used.
Step 4 allows you to select additional options:
- Multi-Valued Separator char - If this option is selected, mutli-valued attributes can be updated using the CSV file, using the separator value specified.
- Allow multiple users per CSV row - By default only a single user is allowed to be updated by a single row in the CSV file. If the binding column is not unique, there is a possibility that multiple user accounts will be matched to the binding column. If the "Allow multiple users per CSV row" option is not selected, the update will fail for that CSV row. When the "Allow multiple users per CSV row" option is enabled, all matching user accounts are updated.

CSV Update File Format

CSV files are text files with columns delimitated by a comma and rows delimitated by a new line character. A double quotes text qualifier can be used to include commas or new line characters within the dataset. Please see this Wikipedia link for more information about the CSV file format.

The CSV file used for the update must include a header row at the top of the CSV file. The column names used must correspond to Active Directory attribute names. For example, you would use "sn" as the column name if you wanted to update the Last Name attribute. See the Active Directory Schema Guide to identify the correct names to use.

One of the columns in the CSV file is used to map rows in the CSV file to user accounts in Active Directory - this is the binding column. Normally you would want to map a single row in the CSV file to a single user in Active Directory. The username (sAMAccountName) or user principal name (userPrincipalName) attributes are a very good choice of binding column as they are guaranteed to be unique in the domain.

It is also possible to use non-unique attributes as the binding column. For example, you might want to use the Email (mail) attribute as the binding column. This attribute might have a high probability of being unique, but it is not guaranteed to be unique. By default, the update will fail if multiple user accounts are found. You can also enable an option that will allow more than one user account to be updated from a single row in the CSV file.

This feature can also be exploited for another type of update. For instance, you might have a list of departments and several attribute modifications that should be made to user accounts in those departments. The example below shows you how you can create a CSV file to update the Fax number and manager for users in the sales, marketing and manufacturing departments.
e.g.