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:

For instructions on how to automate the import of user accounts, see Schedule Import of Users from a CSV File.

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


    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.


    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:

  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.
You can also update existing user accounts during data import. To do it, check the Update existing objects checkbox on the Import Settings step of 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.


    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.

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


Syntax:

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]



Parameters:

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

  • BASE - only the object specified by the /r parameter is exported.
  • 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 descendants.

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:

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

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:

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

If the parameter is omitted, the default value SINGLE is used.

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

  • TEXT
  • GENERAL

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:

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

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




Open tutorial filtering

Got questions?
Support Forum