Import from an Excel file

<< Click to view the table of contents >>

Navigation:  Concepts >

Import from an Excel file

Active Directory One uses Microsoft Excel format to import lists of users or groups into the current project.


Excel sheet formatting for import: For the import algorithm to recognize The Excel worksheet as a valid document to be imported, it is necessary to set only two fields per column, on the first row the name of the Active Directory attribute or extra attribute and on the second row the descriptive string that will be used as the column header for the same attribute by the application interface, all fields on subsequent rows are considered values for that attribute.


Cells with no value (null values): if values for an attribute are missing in the file, on import those values will be cleared in the document, except for the Extra_InitialPassword attribute which is never cleared in the document, if an Extra_InitialPassword value is empty it is ignored by the import algorithm. Keep in mind that some attributes require a value in AD and therefore cannot contain null values such as name or Extra_ou, in this case when storing in Active Directory in an error will be generated.

Root Attributes: In the template it is possible to compute attributes on the basis of others, for example the mail attribute can be obtained from sn, givenName and other static strings, in this case mail is a dependent attribute of the two root attributes sn and givenName, visually the root attributes are shown in green in the main grid, for more information about root attributes see Templates. The root attributes should always be used in the import file, otherwise the import algorithm will not be able to calculate the dependent attributes.

Best practices: to minimize errors, it is recommended that as many attributes as possible be programmed into the template and as few attributes as possible be included in the import file, i.e., root attributes and those that cannot be calculated automatically.

Logic of the import algorithm: The algorithm recognizes the equalities between the incoming records and those already in the grid and performs merging of those considered equal, for example, with the same SID or with other fields that in AD do not allow duplicates such as name, userPrincipalName and sAMAccountName, if the template contains other fields marked as unique or contains an override list these are also evaluated. If the algorithm chooses to merge, the values contained in the import file take precedence over those already in the grid, Active Directory, or the default values of the template in use.

During import, after the value has been established, it is formatted by any rules defined in the template.

If there are Attributes in the Excel document that are not found in the project, you can choose whether to add them during import.

If in the Excel document to be imported there is the SID column that allows identifying an existing user in AD and also the project key attribute column, the import algorithm checks if the records contain the project key entity, if this is not found, the records will still be imported but will be marked as external to the project and highlighted with green color. On the imported records marked as external, the Store in Active Directory command can be executed, but in this case, after storing, the records will be excluded from the project because the project key entity is not present.

Important note: The algorithm operates only in organizational units and not in Built-in system entities of Active Directory


See also: Export