Adaxes enables you to import and export Active Directory data in a number of data formats, including LDIF, DSML v.1, DSML v.2, XLSX, CSV and HTML. In this tutorial, you will learn how to import a CSV file (a text file with columns separated by commas) with user accounts into Active Directory.
There are three ways to manually import data from a CSV file:
Create a CSV file with user data.
The column headers in the file must exactly match property names of user objects in Active Directory. For example, if you want your CSV file to contain Full Name, First Name, Last Name, Department, User Logon Name and Password fields, the file should contain the following column headers: cn, givenName, sn, department, sAMAccountname, unicodePwd (or AccountPassword).
Display Name | Property Name in Active Directory |
---|---|
Full Name | cn, name |
First Name | givenName |
Last Name | sn |
Initials | initials |
Description | description |
Office | physicalDeliveryOfficeName |
Telephone Number | telephoneNumber, otherTelephone |
Web Page | wwwHomePage |
Web Page (Other) | url |
User Logon Name | userPrincipalName |
User Logon Name (pre-Windows 2000) | sAMAccountname |
Password | AccountPassword, unicodePwd |
Account Expires | accountExpires |
Protect from Accidental Deletion | ProtectedFromAccidentalDeletion |
Street Address | streetAddress |
P.O.Box | postOfficeBox |
City | l |
State/Province | st |
Zip/Postal Code | postalCode |
Country/Region | c, co, and countryCode |
Title | title |
Department | department |
Company | company |
Manager | manager |
Profile Path | profilePath |
Logon Script | scriptPath |
Home Directory | homeDirectory |
Home Directory Drive | homeDrive |
Home Phone | homePhone, otherHomePhone |
Pager | pager, otherPager |
Mobile Phone | mobile, otherMobile |
Fax | facsimileTelephoneNumber, otherFacsimileTelephoneNumber |
IP Phone | ipPhone, otherIpPhone |
Notes | info |
Employee Type | employeeType |
Employee ID | employeeID |
See also: User Object User Interface Mapping.
Also, your CSV file can contain separate columns for user Account Options.
The column values can be set to true or 1, false or 0.
Column Name | Description |
---|---|
CannotChangePassword | Specifies whether the account password can be changed. |
ChangePasswordAtLogon | Specifies whether the password must be changed on the first logon. |
PasswordNeverExpires | Specifies whether the password of the account can expire. |
Enabled | Specifies whether the user account is enabled. |
AccountNotDelegated | Specifies whether the security context of the user is delegated to a service. |
TrustedForDelegation | Specifies whether an account is trusted for Kerberos delegation. |
AllowReversiblePasswordEncryption | Specifies whether reversible password encryption is allowed for the account. |
SmartcardLogonRequired | Specifies whether a smart card is required to logon. |
PasswordNotRequired | Specifies whether the account requires a password. |
UseDesEncryptionTypes | Specifies whether Data Encryption Standard (DES) is supported for the account. |
DontRequireKerberosPreAuth | Specified whether Kerberos pre-authentication is required. |
Instead of setting Account Options in separate columns, you can specify all of them in a single column - userAccountControl. Here are some of the possible values for the property:
For more details, see: How to use the UserAccountControl flags to manipulate user account properties.
By default, all imported user accounts are disabled and have a blank password.
You can set initial passwords in two ways:
To enable user accounts, do one of the following:
Launch Adaxes Administration Console.
Expand your Adaxes service, right-click Active Directory, point to All Tasks and click Import.
Select the file to import and click Next.
If your CSV file contains multi-valued properties, specify what delimiter is used to separate property values.
Click Next.
In the Name section, select the column that contains the full names of users.
Specify the Organizational Unit or container where the imported user accounts will be created.
In the Object Type section, select User.
If your CSV file contains DN and objectClass columns, the location where the objects will be created, their names and object types can be retrieved from the columns.
To import user accounts using PowerShell, you can use a standard PowerShell cmdlet Import-Csv. The cmdlet will read in the CSV file and create a set of PowerShell objects, one object for each row of the CSV file. To create user accounts in Active Directory, you need pass the objects to the New-AdmUser cmdlet.
Create a CSV file with user data.
Column Name | Description | Example | Type |
---|---|---|---|
AccountExpirationDate | The expiration date for the account. When set to 0, the account never expires. |
4/17/2006 Monday, April 17, 2006 Monday, April 17, 2006 2:22 PM Mon, 17 Apr 2006 21:22:48 GMT 05/01/2012 5:00:00 PM |
Date |
AccountNotDelegated | Specifies whether the security context of the user is delegated to a service. |
true false |
Boolean |
AccountPassword | The user password. | secret | Secure String |
AllowReversiblePassword Encryption |
Specifies whether reversible password encryption is allowed for the account. |
true false |
Boolean |
CannotChangePassword | Specifies whether the account password can be changed. |
true false |
Boolean |
ChangePasswordAtLogon | Specifies whether the password must be changed during the first logon. |
true false |
Boolean |
City | The user's town or city. | London | String |
Company | The user's company. | Acme | String |
Country | The country or region code for the user's language of choice. |
US FR |
String |
Department | The user's department. | Sales | String |
Description | The description of the user. | External subcontractor | String |
DisplayName | The display name of the user. | John Smith | String |
Division | The user's division. | Software | String |
EmailAddress | The user's e-mail address. | johndoe@example.com | String |
EmployeeID | The user's employee ID. | A123321 | String |
EmployeeNumber | The user's employee number. | 112233 | String |
Enabled | Specifies if the account is enabled. |
true false |
Boolean |
Fax | The user's fax phone number. | +1 (999) 555 1122 | String |
GivenName | The user's first name. | John | String |
HomeDirectory | The user's home directory. | \\SERVER\johnsmith | String |
HomeDrive | The drive that is associated with the UNC path defined by the HomeDirectory property. | D: | String |
HomePage | The URL of the home page of the user. | http://example.com/jsmith | String |
HomePhone | The user's home telephone number. | +1 (999) 555 2222 | String |
Initials | The initials that represent part of the user's name. | L | String |
LogonWorkstations | The computers that the user can access. | COMP1,COMP2.example.com | String |
Manager | The user's manager. |
john.doe CN=Doe,CN=Users,DC=acme,DC=com 7D1D1508-2A07-47D8-8933-C9E557ED86D0 S-1-5-21-1233211223-291919 |
ADUser |
MobilePhone | The user's mobile phone number. | +1 (999) 555 3333 | String |
Name | The user's full name. | John Smith | String |
Office | The location of the user's office or place of business. | B1021 | String |
OfficePhone | The user's office telephone number. | +1 (999) 555 4444 | String |
Organization | The user's organization. | Accounting | String |
OtherAttributes | Values for user properties that cannot be specified in the CSV file columns. |
'extensionAttribute1'=value 'customAttribute'=value1,value2 'attr1'=val; 'attr2'=val1,val2 |
TTT |
OtherName | The name in addition to a user's given name and surname, such as the user's middle name. | Peter | String |
PasswordNeverExpires | Specifies whether the password of the account can expire. |
true false |
Boolean |
PasswordNotRequired | Specifies whether the account requires a password. |
true false |
Boolean |
Path | The DN of the Organizational Unit (OU) or container where the new user will be created. | CN=Users,DC=acme,DC=com | String |
POBox | The user's post office box number. | 25656 | String |
PostalCode | The user's postal code or zip code. | 18711 | String |
ProfilePath | The path to the user's profile. | \\SERVER\profiles\johndoe | String |
ProtectedFromAccidental Deletion |
Specifies whether an object is protected from accidental deletion. |
true false |
Boolean |
SamAccountName | The user's logon name (pre-Windows 2000). | johnsmith | String |
ScriptPath | The path to the user's log on script. | \\SCRIPTS\johnsmithLogin | String |
SmartcardLogonRequired | Specifies whether a smart card is required to logon. |
true false |
Boolean |
State | The user's state or province. | Nevada | String |
StreetAddress | The user's street address. | 100 Main Street | String |
Surname | The user's last name or surname. | Smith | String |
Title | The user's title. | Sales Manager | String |
TrustedForDelegation | Specifies whether an account is trusted for Kerberos delegation. |
true false |
Boolean |
UserPrincipalName | The user's logon name. | johnsmith@example.com | String |
Open PowerShell command prompt.
Type the following command, and then press Enter:
Import-Csv "C:\Users.csv" | New-AdmUser -Path "CN=Users,DC=example,DC=com"` -AdaxesService localhost
The parameter -Path specifies the distinguished name DN of the Organizational Unit or container where new users will be created.
Click Copy DN. The DN of the selected object will be copied to the clipboard.
If your CSV file contains column AccountPassword or columns with data of the Boolean type (e.g. Enabled or ChangePasswordAtLogon), the columns must be processed in a special way.
$importedUsers = Import-Csv "C:\Users.csv" foreach ($user in $importedUsers) { $user.AccountPassword =` ConvertTo-SecureString -AsPlainText $user.AccountPassword -Force $user.Enabled = [System.Boolean]::Parse($user.Enabled) $user.ChangePasswordAtLogon = [System.Boolean]::Parse($user.ChangePasswordAtLogon) $user | New-AdmUser -Path "CN=Users,DC=example,DC=com" -AdaxesService localhost }
To import and export Active Directory data from the command line you can use utility admimex.exe. It is installed with Adaxes Administration Console and by default is located in C:\Program Files\Softerra\Adaxes N\Administration Console.
admimex.exe [@argfile] [/?|h|help] [/v|version] [/i] [/d LDIF|DSML1|DSML2|CSV|TXT|HTML|EXCEL] /f filename [/s service] [/user user] [/pwd password] [/r baseDN] [/t filter] [/p BASE|ONE|SUB] [/a attrlist] [/lbs NONE|WIN|UNIX] [/page size] [/dh] [/di] [/dd] [/ds] [/cf fromDN] [/ct toDN] [/fs separator] [/vs separator] [/tq qualifier] [/sr number] [/el SINGLE|MULTI] [/ec TEXT|GENERAL] [/eh]
/i
If specified, the import operation is performed. If not specified, the export operation is performed.
/d value
Specifies the format of the file to import from or export to.
Possible values for data export are LDIF, DSML1, DSML2, CSV, TXT, HTML, EXCEL.
Data can be imported in LDIF, DSML, and CSV formats only.
/f filename
Specifies the name of the file to import from or export to.
/s service
Specifies the Adaxes service host. If not provided, the default value localhost is used.
/user user
Specifies the user account used to perform the export or import operation.
/pwd password
Specifies the password of the user account used to perform the export or import operation.
/r baseDN
Specifies the object DN to start the export from.
/t filter
Specifies the LDAP search filter for exporting AD objects.
Only the objects that match the specified filter will be exported.
If the parameter is omitted, the default value (objectClass=*) is used.
/p value
Specifies the scope of objects to export. Available values are:
If the parameter is omitted, BASE is be used by default.
/a attrlist
Specifies a comma-separated list of AD object properties to export.
/lbs value
Specifies the way line breaks will be inserted in the result file. Possible values are:
If the parameter is omitted, WIN is used by default.
/page size
Specifies the search page size for data export.
The page size specifies the maximum number of records returned for each search request.
If the parameter is omitted, the default value 500 is used.
/dh
Disables generation of the document header. The document header contains export date/time and information about the source, etc.
/di
Disables indenting in the exported document. The option can be used for DSML1, DSML2, and HTML formats.
/dd
Disables the transformation of property values into human-readable form,
so all properties are exported as they are stored in the Active Directory.
If the option is omitted, values of some properties will be converted into human-readable form. The option can be used for TXT, CSV, HTML and Excel formats.
/ds
Specifies that SAM-specific properties are not excluded during export or import.
/cf fromDN
Specifies the DN suffix to replace.
If specified, the suffix of the imported or exported objects is replaced with the suffix specified in the /ct parameter.
This can be done, for example, to import exported objects to another location.
/ct toDN
Specifies the suffix, with which an object DN part will be replaced.
The value replaces the suffix of the imported or exported objects specified in the /cf parameter.
This can be done, for example, to import exported objects to another location.
/fs separator
Specifies the delimiter used to separate columns in TEXT and CVS files.
If the parameter is omitted, the default value ';' is used.
/vs separator
Specifies the delimiter used to separate property values in of multi-valued properties.
If the parameter is omitted, the default value ',' is used.
/tq value
Specifies the text qualifier for TEXT and CSV files.
Text qualifiers are used to identify the boundaries of strings.
If a field separator character appears within the boundary, it won't be considered as a delimiter.
If the parameter is omitted, the quotation mark (") is used as the default value.
/sr number
Specifies the row, starting from which TEXT or CSV file will be imported.
If the parameter is omitted, the import starts from the first row.
Specify the parameter, if your file contains a header.
/el value
Specifies the layout for EXCEL documents. Possible values are:
If the parameter is omitted, the default value SINGLE is used.
/ec value
Specifies the cell format for EXCEL documents. Possible values are:
If the parameter is set to GENERAL, Excel will auto-detect the cell format depending on the value of each particular cell. If the parameter is omitted, the default value TEXT is used.
/ue
If this parameter is specified, the 'Object already exists' error will be thrown if an imported object already exists in Active Directory.
If the parameter is omitted, existing objects will be updated with the data from the CSV file.
/cd value
Specifies how DN suffixes are replaced in properties that refer to AD objects (DN syntax properties).
This parameter is used only with /cf and /ct parameters. Possible values are:
/nco
If specified, all records will be imported in the order in which they appear in the import file.
If not specified, the order will be changed if necessary (child objects will be imported after parent objects).
/pu
If specified, all object properties that refer to other objects (DN syntax properties) will be saved to the directory after
all objects are imported. This is useful if a parent object contains a DN syntax property referring to a child object.
/eh
Forces to freeze the header in EXCEL documents keeping it in view while scrolling through the file.
Example 1: Export a specific user account to an LDIF file.
admimex.exe /d LDIF /f c:\file.ldif /r "CN=John Doe,CN=Users,DC=example,DC=com"
Example 2: Export the objects located in the Users container to an LDIF file.
admimex.exe /d LDIF /f c:\file.ldif /p ONE /r CN=Users,DC=example,DC=com
Example 3: Import data from a DSML file.
admimex.exe /i /d DSML /f "c:\file.xml"
Example 4: Export the objects located in the Users container using the specified credentials.
admimex.exe /d CSV /f c:\file.csv /r CN=Users,DC=example,DC=com /user EXAMPLE\administrator /pwd secret /p SUB
Example 5: Export the objects located in the Users container using an ARG file.
admimex.exe @"C:\Examples\argfile.txt"
File content example: /d LDIF /f c:\file.ldif /r CN=Users,DC=example,DC=com /p ONE
Example 6: Export only inetOrgPerson objects from the Users container.
admimex.exe /d LDIF /f c:\file.ldif /r CN=Users,DC=example,DC=com /p SUB /t (objectClass=inetOrgPerson)
Example 7: Import objects to the New Users container, if they were exported from the Users container.
admimex.exe /i /d LDIF /f C:\file.ldif /cf "CN=Users,DC=example,DC=com" /ct "CN=New Users,DC=example,DC=com"
Example 8: Export only the Display Name and Telephone Number properties of AD objects.
admimex.exe /d TXT /f C:\file.txt /r CN=Users,DC=example,DC=com /p ONE /a displayName,telephoneNumber
Automated Provisioning
Business Rules that are triggered after creation of new user accounts are also applied during data import. For example, if you have a Business Rule that creates home folders for new users, it will also create home folders for the users created during the import process. For details, see Automate User Provisioning.
Default Property Values
With the help of Property Patterns you can assign a default value to any property of Active Directory objects. If a user account is created during data import, and the value of a property is empty, the default value will be set for that property. For details, see: