Import User Accounts from a CSV File

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:

• Import Wizard
• PowerShell
• Command Line

Import Wizard

1. 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).

   ---

   

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

   

   Columns for 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:

   ---

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

   ---

   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:

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

   To enable user accounts, do one of the following:

   • Add Enabled column to your CSV file and specify true or false for each user in the column.
   • Assign default values for user account options. For details, see Set default account options for new users.

2. Launch Adaxes Administration Console.

   Expand your Adaxes service, right-click Active Directory, point to All Tasks and click Import.

   ---

   

3. Select the file to import and click Next.

   

4. If your CSV file contains multi-valued properties, specify what delimiter is used to separate property values.

   

   ---

   Click Next.

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

   ---

   

6. Click Next and follow the steps in the wizard.

PowerShell

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.

1. Create a CSV file with user data.

   The Import wizard and PowerShell cmdlets have different requirements on the data format and column names of CSV files.

   

   All 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

2. Open PowerShell command prompt.

   
   
   For information on how to install Adaxes Powershell Module, see Install Adaxes Powershell Module for Active Directory.

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

   How to get the DN of an object

   • Launch 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 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
   }

   ---

   ---

   For information about the cmdlets included in Adaxes PowerShell Module, see PowerShell Module.

Command Line

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]

admimex.exe /d LDIF /f c:\file.ldif /r "CN=John Doe,CN=Users,DC=example,DC=com"

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

admimex.exe /i /d DSML /f "c:\file.xml"

admimex.exe /d CSV /f c:\file.csv /r CN=Users,DC=example,DC=com
/user EXAMPLE\administrator /pwd secret /p SUB

admimex.exe @"C:\Examples\argfile.txt"

admimex.exe /d LDIF /f c:\file.ldif /r CN=Users,DC=example,DC=com /p SUB
/t (objectClass=inetOrgPerson)

admimex.exe /i /d LDIF /f C:\file.ldif /cf "CN=Users,DC=example,DC=com"
/ct "CN=New Users,DC=example,DC=com"

admimex.exe /d TXT /f C:\file.txt /r CN=Users,DC=example,DC=com /p ONE
/a displayName,telephoneNumber