Softerra Adaxes Tutorials. Import User Accounts from a CSV File

Adaxes enables you to import and export Active Directory data in a number of data formats (LDIF, DSML v.1, DSML v.2, MS Excel, CSV, Plain Text, 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 to Active Directory.

Import CSV File Using Adaxes Administration Console

Create a CSV file with information about users.

CSV file with user accounts

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, and User Logon Name fields, then this file should contain the following column headers: cn, givenName, sn, department, sAMAccountname, unicodePwd (or AccountPassword).

View Property Name Mapping

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
Email	mail
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
Direct Reports	directReports
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 may contain separate columns for user Account Options.

View Columns for Account Options

The table below provides the list of all columns for Account Options. You can specify true or 1, false or 0 for each column.

Column Name	Description
CannotChangePassword	Specifies whether the account password can be changed.
ChangePasswordAtLogon	Specifies whether the password must be changed during 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 specifying Account Options in separate columns, you can specify all of them in a single column - userAccountControl. Some of the possible values for the userAccountControl property:

512 - enabled account
513 - disabled account
66048 - enabled account, password never expires
66050 - disabled account, password never expires

For 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.

To set initial passwords for the imported users, you have two options:

Add the AccountPassword column to your CSV file and specify a password for each user in this column.
Configure Property Patterns to automatically generate initial user passwords for new users. For details, see Generate initial password on user creation.

To enable the imported user accounts, you can:

Add the Enabled column to your CSV file and define true or false for each user in this column.
Configure Property Patterns to set corresponding account options for new users. For details, see Set default account options for new users.

Launch Adaxes Administration Console, expand your Adaxes service, right-click Active Directory, point to All Tasks and click Import Data. The Import Data wizard will open.

On the File Name page, click Browse and select the file to import. The format of the selected file will be displayed in the Document type drop-down list. Click Next.

File Name wizard page

On the Formatting Options page, click Next.

On the Content Options page, perform the following steps:

Select the Get name from a column and add parent DN option.
In the Column field, select the column that contains user full names.
In the Parent DN field specify the DN of the container or OU where you want to import users. Click the button to select this location.
Enable the Use specific object type option and select User in the combo box located next to this option.
Click Finish.

Content Optsions wizard page

You can also update existing user accounts during CSV data import. To update existing objects, enable the Update existing objects option at the 4th step of the wizard.

Import CSV File Using PowerShell

To import CSV files using PowerShell, you can use a standard PowerShell cmdlet Import-CSV. This cmdlet will read in the CSV file and create a set of PowerShell objects based on the data inside this file. To create user accounts in Active Directory, you can pass the results of this cmdlet down the PowerShell pipeline to the New-AdmUser cmdlet from the Adaxes PowerShell Module.

To use Adaxes cmdlets, you need to install the PowerShell Module for Active Directory component of Softerra Adaxes.

Create a CSV file with information about users.

The names of some column headers in CSV files intended for use with the Import Data Wizard and PowerShell cmdlets can differ.

View All Possible Columns

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

How to get the DN of an object

To get the DN of an Active Directory object:

Launch the Adaxes Administration Console.
Right-click the object you need.
In the context menu, open the submenu of the Copy item.
Click Copy DN. The DN of the selected Active Directory object will be copied to the clipboard.

For a list of all cmdlets contained in the Adaxes PowerShell module, see Adaxes PowerShell Module.

If your CSV file contains the AccountPassword column or columns with data of the Boolean type (e.g. Enabled or ChangePasswordAtLogon), these 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
}

Import Data from the Command Line

Sometimes it may be necessary to import or export Active Directory data from the command line. For example, you may want to set up a daily backup of your Active Directory data or make LDIF import a part of an AD synchronization process. For this purpose, admimex.exe tool can be used.

admimex.exe is a command line tool installed with Softerra Adaxes Administration Console that allows importing and exporting Active Directory data without running the Administration Console. All parameters are entered via the command line, and the tool runs in the unattended mode.

By default, admimex.exe is located in the C:\Program Files\Softerra\Adaxes N\Administration Console folder.

View the syntax and parameters of admimex.exe

/i
Specifies that 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 are:

LDIF
DSML1
DSML2
CSV
TXT
HTML
EXCEL

Softerra Adaxes allows exporting directory data in all these formats, however, import is only available in LDIF, DSML, and CSV formats.

/f filename
Specifies the name of the file to import from or export to.

/s service
Specifies the host of the target Adaxes service. 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 correspond to the specified filter will be exported. If this parameter is omitted, the default value is (objectClass=*).

/p value
Specifies the scope for exporting AD objects. Available values are:

BASE - export is only performed for the object specified by the /r parameter.
ONE - export is only performed for the direct children of the object specified by the /r parameter.
SUB - export is performed for the object specified by the /r parameter and all of its children direct and indirect.

If this parameter is omitted, the BASE will be used by default.

/a attrlist
Specifies a comma-separated list of AD object properties that will be exported.

/lbs value
Specifies the way line breaks will be inserted in the result file while exporting the directory data. Possible values are:

NONE - no line breaks
WIN - (CR LF)
UNIX - (CR)

If this parameter is omitted, WIN will be used by default.

/page size
Specifies the page size for export. The page size specifies the maximal number of records returned for each search request. If this parameter is omitted, the default value is 500 records.

/dh
Disables generating of an extra header while saving results of the export operation. The extra header contains export date and time and information on where the data were exported from.

/di
Disables line indenting while generating results of the export operation. This option is applicable to DSML1, DSML2, and HTML. If this option is enabled, the resulting file is easier to read, while the deactivation of this option makes the file smaller in size.

/dd
Specifying this option disables the transformation of property values into human-readable format, so all properties are exported as they are stored in the Active Directory. For example, though 'objectSid' is a binary property, Softerra Adaxes displays it in the SDDL form that allows displaying a security descriptor as a text string.

If this option is omitted, Softerra Adaxes will transform values of some properties into human-readable format. This parameter is applicable to TXT, CSV, HTML and Excel formats only.

While exporting files without this parameter specified, such files might not be imported back into the directory since not all properties can be transformed back from human-readable representation.

/ds
Specifies that SAM-specific properties are not excluded during export or import.

/cf fromDN
Specifies the suffix of an object DN 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. This 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 field separator used when importing/exporting TEXT and CVS files. Field separators are used to separate columns in the export or import file. If this parameter is omitted, the default value ';' will be used.

/vs separator
Specifies the property value separator used when importing/exporting TEXT and CVS files. Value separators are used to separate values of multi-valued properties in the export or import file. If this parameter is omitted, a comma ',' will be used by dafault.

/tq value
Specifies the text qualifiers used when importing/exporting TEXT and CSV files. Text qualifiers are used to identify the boundary of the text value. If a field separator character appears within this boundary, it won't be considered as a delimiter. If this 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 this parameter is omitted, the import starts from the first row. It is necessary to specify this parameter, if you file contains a header, as headers must not be imported.

/el value
Specifies the EXCEL file type layout. Possible values are:

SINGLE - all objects are represented in a single table
MULTI - each object is represented in a separate table

If this parameter is omitted, the default value SINGLE will be used.

/ec value
Specifies the cell format for the EXCEL files. Possible values are:

TEXT
GENERAL

If this parameter is omitted, the default value TEXT will be used. With this parameter set to GENERAL, Excel will be able to auto-detect the cell format depending on the value of each particular cell.

/ue
Specifies that if an imported object already exists in Active Directory, it will be updated instead of throwing the 'Object already exists' error.

/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:

NONE - do NOT replace DN suffixes in object properties.
EXISTING - replace only DNs that refer to objects from the import file.
ALL (default) - replace all DNs.

/nco
If this parameter is specified, all records will be imported in the order in which they appear in the import file. If this parameter is not specified, the order will be corrected if it is necessary (child objects will be imported after parent objects).

/pu
If this parameter is specified, all object properties that refer to other objects are saved to the directory after all records 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 of the EXCEL document keeping it in view while scrolling through the file.

View Examples of using admimex.exe

Example 1: Exporting a single user to the LDIF format
admimex.exe /d LDIF /f c:\file.ldif /r "CN=John Doe,CN=Users,DC=example,DC=com"

Example 2: Exporting the Users subtree to the LDIF format
admimex.exe /d LDIF /f c:\file.ldif /p ONE /r CN=Users,DC=example,DC=com

Example 3: Importing data from a DSML file
admimex.exe /i /d DSML /f c:\file.xml

Example 4: Exporting the Users subtree 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: Exporting the Users subtree using the ARG file
admimex.exe @"C:\Examples\argfile.txt"

The ARG file contents: /d LDIF /f c:\file.ldif /r CN=Users,DC=example,DC=com /p ONE

Example 6: Exporting only inetOrgPerson objects from the Users subtree
admimex.exe /d LDIF /f c:\file.ldif /r CN=Users,DC=example,DC=com /p SUB /t (objectClass=inetOrgPerson)

Example 7: Importing data to the New Users container, though it was 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: Exporting 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

Business Rules and Property Patterns are also applied to the Active Directory objects created during data import. For example, if you have a Business Rule that creates home folders for new users, this Business Rule will also create home folders for the users created during data import. For details, see:

Or, if a Property Pattern generates initial passwords for new users, this Property Pattern will also generate passwords for the user accounts created during data import (unless passwords are defined for each user in the import file). For details, see:

Tutorials

Import CSV File Using Adaxes Administration Console

View Property Name Mapping

View Columns for Account Options

Import CSV File Using PowerShell

View All Possible Columns

How to get the DN of an object

Import Data from the Command Line

View the syntax and parameters of admimex.exe

View Examples of using admimex.exe

Delegating Permissions

Automating Daily Tasks

Simplifying Data Entry

Active Directory Management

Web Interface Customization

Self-Service