Introduction
The CSV Update feature allows you to update user attributes in Active Directory from a comma-separated text file. CSV is a common format used for data exchange between different systems and most data sources will allow you to extract data in this universal format. It should be easy to extract employee information from your HR database to a CSV file that can be used as the input file for the update. It’s also easy to convert Excel spreadsheets to CSV files and to construct your own CSV files using a notepad application.
Generating a valid CSV file
The first step to updating user accounts from a CSV file is to produce a valid CSV file to be used by the update. A CSV file is simply a text file containing rows and columns of data like a spreadsheet and they can be read and written to by spreadsheet applications like Microsoft Excel. As mentioned previously, there is normally a way to extract data from data sources into CSV text files. It’s also possible to produce a text file manually by using an application such as notepad. See here for more information on producing CSV files. As well as ensuring that you have a well formed CSV file, there are also some additional requirements to enable the CSV file to be used as the input source for the CSV update. The CSV file must contain a header row with column names that correspond to the attributes in Active Directory that you want to update. You should be careful to use the correct attribute names as the attribute names don’t always correspond to the ones used in Active Directory Users & Computers. For example, instead of “Last Name” you need to use “sn” and instead of city you would use “l”. You might find the Active Directory Schema Guide on this website useful for identifying the correct attribute names. One of the columns in the CSV file must be used to identify the user account that you want to update in Active Directory. The attributes “sAMAccountName” (User logon name pre-Windows 2000) and “userPrincipalName” (User logon name) are ideal candidates because they are guaranteed to be unique and enable you to unambiguously identify the user account that you want to update. This isn’t a strict requirement so you can use other attributes such as “mail” or “EmployeeID” if no other link to your data source exists. In this case there is a possibility that a row in the CSV file will match to multiple user accounts. If this occurs the update will fail for that row by default, but you can also configure the application to update all user accounts. This might be useful if you know that there are certain people that have more than one user account for different purposes (e.g. system admins might have a normal account and an account with elevated privileges) and you want to update all the accounts linked to that person. You can also exploit this feature if you have a list of departments and information that you want to update for every user in that department (e.g. fax number, manager).
Sample CSV Files
Sample 1
This CSV file was produced from Microsoft’s AdventureWorks sample database and it has been converted into a format that can be used with the CSV update feature:adventureworks_employee.csv The following attribute names are included as column headings in the CSV file:
| sAMAccountName | This is the username (Pre-Windows 2000) attribute that will be used to link rows in the CSV file to user accounts in Active Directory. |
| EmployeeID | This attribute isn’t visible via the ADU&C interface, but it can be used by other LDAP directory enabled applications. This attribute will also be visible in ADSIEdit. |
| Title | This attribute corresponds to the “Job Title” field on the “Organization” page in ADU&C |
| streetAddress | This attribute corresponds to the “Street” field on the “Address” page in ADU&C. |
| l | This attribute corresponds to the “City” field on the “Address” page in ADU&C. |
| postalCode | This attribute corresponds to the “Zip/Postal Code” field on the “Address” page in ADU&C. |
| st | This attribute corresponds to the “State/Province” field on the “Address” page in ADU&C |
| department | This attribute corresponds to the “Department” field on the “Organization” page in ADU&C. |
| givenName | This attribute corresponds to the “First Name” field on the “General” page in ADU&C. |
| sn | This attribute corresponds to the “Last Name” field on the “General” page in ADU&C. |
| This attribute corresponds to the “E-mail” field on the “General” page in ADU&C. | |
| telephoneNumber | This attribute corresponds to the “Telephone number” field on the “General” page in ADU&C. |
The sAMAccountName column is particularly important as it provides a link between rows in our CSV file to user accounts in Active Directory.
Note: The CSV update tool isn’t limited to the list of attributes defined above and you can choose a different attribute as your link between rows in the CSV file and user accounts in Active Directory.
Sample 2
This sample CSV file demonstrates how the “Allow multiple users per CSV row” feature can be exploited to update user data for all users in each department.department_update.csv
| department | This attribute will be used to match rows in the CSV file to every user in those departments, demonstrating an alternative use for the “Allow multiple users per CSV row” feature. |
| facsimileTelephoneNumber | This attribute corresponds to the “Fax” field on the “Telephones” page in ADU&C. |
| manager | This attribute corresponds to the “manager” field on the “Organization” page in ADU&C. Please note that this attribute must be specified in distinguished name format. Note: Future versions of the application will provide an alternative method similar to the one available in this VBS script. |
Instructions
- Create your CSV update file as described in the “Generating a valid CSV file” section.
- Open Bulk AD Users and select “CSV Update” from the “Update” menu. (You can also click the button on the toolbar)
- In section 1, click the folder icon to browse the file system for your CSV file.
- In section 2, select the attribute that you want to use to identify the user accounts that you want to update. As mentioned previously, sAMAccountName (Logon name pre-Windows 2000) and userPrincipalName (Logon name) are both good candidates as they are guaranteed to be unique.
- In section 3, review the list of attributes and uncheck any attributes that you do not wish to update.
- In section 4, you can specify a separator character for multi-valued attributes, allowing you to assign more than 1 value to an attribute. You can also check the option to allow a single row in the CSV file to update more than 1 user account.
Known Issues
- Data might be incorrectly converted to a number by the Microsoft.Jet provider, resulting in data loss. e.g. The value 01234 is converted to 1234. This can be prevented by using quotation marks in your CSV file to surround the value. e.g. “01234” instead of 01234. It’s also possible to fix this problem using a schema.ini file.
Note: You might also experience a similar problem where a value gets converted to a date and the data appears in a different format in Active Directory. e.g. 01/01/2010 would get converted to a date and appear as “01/01/2010 00:00:00”. This is part of the same issue and the workaround above will also apply. - CSV files with delimiters other then the comma “,” character are not supported. (Using a semi-colon character “;” as a delimiter is also a popular file format)
These issues will be resolved in future versions of the application.