ImportUsersCSV
Feature Description
You can use this application to import (synchronize) users and groups from a CSV file for the entire ALVAO system. You can use it e.g. to update users in ALVAO from a HR management system etc.
Command Line Syntax
ImportUsersCSV /csv "CSV file name and path" {/con "connection string" | /server "database server name" /db "database name"} [/key "key column"] [/cols "list of columns"] [/utcdate] [/nohdrs] [/add] [/remove] [/help]
Detailed Description of Parameters
| Parameter |
Description |
| /csv |
The import CSV file name and path. |
/con
/server
/db |
These parameters contain connection settings for the ALVAO database. You can use connection strings (e.g. /con "Data source=.\sqlexpress;Initial Catalog=test;Integrated Security = True"), or enter a specific SQL server and database (e.g. /server ".\sqlexpress" /db "test").
If you use the /server and /db parameters, the system will connect to the database using Integrated Windows Authentication. If you specify all parameters, only the /con parameter will be used. The parameters /server and /db will be ignored. |
| /key |
The data in the key column are used to search for existing users in Admin. If the parameter is not set, the Login column (must exist in the CSV file) will be used as the key column. |
| /cols |
This parameter allows you to specify the importance and order of columns in the CSV file (CSV file has no header). If 0 (zero) is entered in the list, the corresponding column from the CSV will not be imported (it will be ignored).
Note: The list in the command line does not have to contain the same number of columns as the CSV file (non-specified columns will be skipped, ignored). |
| /utcdate |
Date columns contain dates in UTC time. If no radio button is specified, the dates in the CSV files will be considered to be in the current time zone of the computer (i.e. local time). |
| /nohdrs |
The imported CSV file contains no header (first row) with column description. |
| /add |
Create new users in ALVAO.
Note: If this radio button is not specified, the import will create no new users; it will only update the existing ones. |
| /remove |
Remove users from ALVAO that are not stored in the CSV file. |
Description of All Supported Columns
Column name in CSV file
(- /cols in the command line) |
Meaning |
Matching column
in tPerson |
Note |
| ID |
Unique identification number of a person in the CSV file (e.g. ID in the system from which the CSV file was exported). |
- |
If the CSV file contains ManagerID or DelegateID, this column will be required by the import, otherwise the import will fail. |
| FullName |
First and last name of a person |
sPerson |
Can be blank; in that case the value will be combined to create [FirstName LastName] (if these are filled in). |
| Login |
Username |
sPersonLogin |
|
| Email |
E-mail |
sPersonEmail |
|
| Phone |
Phone |
sPersonPhone |
|
| Mobile |
Cellular |
sPersonMobile |
|
| Office |
Office |
sPersonOffice |
|
| Department |
Department |
sPersonDepartment |
|
| JobTitle |
Job |
sPersonWorkPosition |
|
| Pswd |
SHA1 has of the user's password |
sPersonPswd |
|
| IsAccountDisabled |
"Account is disabled" |
bPersonAccountDisabled |
Permitted values: 0.1 |
| OtherContacts |
Other contacts |
mPersonContact |
|
| Notes |
Notes |
mPersonNotes |
|
| City |
City |
sPersonCity |
|
| ManagerID |
Manager's identification number. The ID value must exist in the CSV file. |
- |
Requires the ID column. |
| DelegateID |
Identification number of substitute person. The person's ID must exist in the CSV and the related people must be assigned the same company (Company). |
- |
Requires the ID column. |
| PersonalNumber |
Personal number |
sPersonPersonalNumber |
|
| OutOfOfficeSinceDate |
Out of office since a date. All conventional date and time formats are supported.
Examples of supported formats:
• 2012-11-21
• 2012-10-9 15:24:48
• 15.8.2010 14:18:44
• 14.7.2010 |
OutOfOfficeSince |
|
| OutOfOfficeUntilDate |
Out of office until date and time. All conventional date and time formats are supported (see OutOfOfficeSinceDate). |
OutOfOfficeUntil |
|
| LocaleId |
Preferred user language expressed in a "language code", e.g.:
• Czech: 1029
• English: 1033
• Polish: 1045
• German: 1031 |
iPersonLocaleId |
An integer. |
| Company |
Organization |
- |
Search for organizations by name. If no name exists, a new organization will be created with the specified name. |
| FirstName |
Name |
sFirstName |
|
| LastName |
Last name |
sLastName |
|
| TimeZone |
Time zone – must match the Windows time zone ID – for more information, please refer here. |
TimeZone |
|
| AdGUID |
GUID from Active Directory |
sPersonAdGuid |
|
| AdPath |
Path in ActiveDirectory |
sPersonAdPath |
|
| AdSID |
AD SID as a text (S-1-5-21-55544...) |
sPersonSID |
|
| IsHidden |
"Hide in menus" |
bHidden |
Permitted values: 0.1 |
| IsShared |
This account is shared |
bPersonShared |
|
| Country |
Country |
sPersonCountry |
|
| CountryAbbr |
Country (abbreviation, 2 characters) |
sPersonCountryAbbr |
|
| CountryCode |
Country code |
iPersonCountryCode |
An integer |
| PreferredLang |
Preferred language (abbreviation, 2 characters) |
sPersonPreferredLanguage |
|
Note: The attributes can be mapped also to any existing custom item from the tPersonCust table, excluding the int type items which use the value list. The attribute name has to be "@"+ [tPersonCust] +[column name in the database], e.g. @tPersonCust.Title.
Scenarios
Structure of the CSV file recommended by ALVAO
ALVAO recommends that the imported CSV file contains the following columns:
Column name in CSV file
(- /cols in the command line) |
Meaning |
Note |
| ID |
Unique identification number of a person in the CSV file (e.g. ID in the system from which the CSV file was exported). |
If the ManagerID and DelegateID are blank, the ID doesn't have to be filled in. |
| FirstName |
Name |
Either the FirstName and LastName, or the FullName items need to be filled in. |
| LastName |
Last name |
Either the FirstName and LastName, or the FullName items need to be filled in. |
| FullName |
First and last name of a person |
Can be blank; in that case the value will be combined to create [FirstName LastName]. |
| PersonalNumber |
Personal number |
|
| Login |
Username |
This column has to contain values. |
| Phone |
Phone |
|
| Mobile |
Cellular |
|
| Email |
E-mail |
|
| Office |
Office |
|
| Department |
Department |
|
| JobTitle |
Job |
|
| Company |
Organization |
|
| IsAccountDisabled |
"Account is disabled" |
Permitted values: 0.1 |
| ManagerID |
Manager's identification number. The ID value must exist in the CSV file. |
Requires the ID column. |
| DelegateID |
Identification number of substitute person. The person's ID must exist in the CSV and the related people must be assigned the same company (Company). |
Requires the ID column. |
| LocaleId |
Preferred user language expressed in a "language code", e.g.:
• Czech: 1029
• English: 1033
• Polish: 1045
• German: 1031 |
An integer. |
Note: Optional columns can be blank; please note that in this case the matching data will be removed from ALVAO (will be blank).
An example of the CSV file
ID;FirstName;LastName;FullName;PersonalNumber;Login;Phone;Mobile;Email;Office;Department;JobTitle;Company;IsAccountDisabled;ManagerID;DelegateID;LocaleId;@tPersonCust.Title 44;David;Cooper;;PN1111;cooper;3256;+1987654321;cooper@demo.com;321;Marketing;Manager;;0;46;45;1029;Mr 45;Peter;Jenkins;;PN2222;jenkins;3247;+1111222333;jenkins@demo.com;323;Advertising;Manager;;1;;;1029;Mr 46;Veronica;Clements;;PN3333;clements;3244;+1121353255;clements@demo.com;111;Marketing;Department Manager;;0;;44;1033;Mrs
Import of CSV Files with Structure Recommended by ALVAO
The CSV file contains a header with column names. The import will perform full synchronization, i.e. remove users that are not found in the CSV file and create new users. The key column is the Login column. Import command line:
ImportUsersCsv.exe /csv "c:\data\users.csv" /server server\sql2005 /db alvao /add /remove
Less Frequent Scenarios
The HR system generates a CSV file with a number of columns, but we want to import only some data to ALVAO
The HR system generates a CSV file with a fixed structure. This structure cannot be changed. We want to create new users and, at the same time, remove users who are not stored in the CSV (full synchronization). Example: The CSV file contains 30 columns. You want to import only "login name" (first column) and "first and last name" (fourth column). Other columns are not important. The CSV file contains no header (column names). We will use the following command line:
ImportUsersCsv.exe /csv "c:\data\users.csv" /server server\sql2005 /db alvao /cols "Login,0,0,FullName" /nohdrs /add /remove
Importing users from AD with additional update of people from the HR system
We are going to import users from AD (new users are created, other users are deleted). We will need to update other data from the HR system – the key is the personal number (we don't want to create new users or delete other users based on the HR system). The HR system generates column names in the CSV file according to ALVAO definition.
Example of use:
ImportAD.exe ... ImportUsersCsv.exe /csv "c:\data\users.csv" /server server\sql2005 /db alvao /key PersonalNumber
Did not find what you were looking for? Ask our technical support team.
|