Preparing a File Exported from Active Directory
Export account information from Active Directory to a CSV file and use it for bulk user registration.
When using a file exported from Active Directory for importing, only user addition is possible. Editing or deletion of user information that has already been registered is not possible.
For information on how to export account information from Active Directory, please refer to the help information provided by Microsoft Corporation.
Preconditions
Active Directory must meet the following requirements:
It must be built via one of the following operation systems:
Windows Server 2012, Windows Server 2012 R2, or Windows Server 2016
The setting should be the same value as the characters prior to the User-Principal-Name "@" and SAM-Account-Name.
The csv file imported from Active Directory must be edited to confirm to the following specifications and then uploaded.
Specifications for uploading the exported csv file or editing the csv file outside of Excel
The character code must be UTF-16.
The file extension must be ".csv".
Specifications for editing the exported csv file with Excel
Select the csv file in [File] - [Open] from the Excel menu, and then open the file selecting the following items in the "Text Import Wizard" window:
Original Data Type: Characters such as commas or tabs separate each field.
Delimiters: Comma
Input the data into the first sheet. When the file has several sheets, the target for reading is the only first sheet.
After editing, save the file in the Excel book format.
The file extension must be ".xlsx".
Macros do not work.
Common specifications
Listed users must be within 150,000. Up to 150,000 users can be imported at once.
The maximum import file size is 30 MB.
The first line is treated as a key, and the second and subsequent lines are treated as data.
Vacant lines are invalid.
Export Method
Use csvde commands to export account information from Active Directory.
Example of export command: csvde
To export all users:
csvde -f <FileName> -u -r objectCategory=user -b <UserName> <Domain> <Password> -s <ServerName> -l "userPrincipalName,mail,sn,givenName"
To export all users except for invalid users:
Csvde -f <FileName> -u -r "(&(!(UserAccountControl:1.2.840.113556.1.4.803:=2))(objectCategory=user))" -b <UserName> <Domain> <Password> -s <ServerName> -l userPrincipalName,mail,sn,givenName,samAccountName,department
Active Directory | Display in this site | Column name with imported file | Remarks |
|---|---|---|---|
The characters prior to the User-Principal-Name "@" | User ID | userPrincipalName | All characters other than single-byte alphanumerics, hyphens, and dots will be overwritten with underscores. |
E-Mail-Addresses | Email Address | Although Active Directory can be set for the same email address for several users, this site cannot make this setting. When same email address is set with the several users, the second or later user gets a registration error. | |
Surname | Family name | sn | |
Given-Name | Given name | givenName | |
SAM-Account-Name | Multifunction Printers/Copiers - Device Account | sAMAccountName | |
Department | Department | department | |
- | Reply Email Language | - | In case of importing, Reply Email Language for user cannot be set, so the same Reply Email Language as tenant is set automatically. |
- | Role | - | In case of importing, Role cannot be set, so it is set as a user automatically. |
Exported sample CSV
DN,sAMAccountName,sn,givenName,userPrincipalName,mail,department
"CN=user 0001,CN=Users,DC=example.com,DC=com",user0003,user,0001,user0001@example.com,test@example.com,department1
"CN=user 0002,CN=Users,DC=example.com,DC=com",user0002,user,0002,user0002@example.com,test2@example.com,department2