Importing User Accounts via CSV Files

Instead of manually creating user accounts via the Administration Console as described in chapter Adding Users Manually, it is possible to import multiple user accounts into the Registration Server database from a file containing the account information as a CSV (comma-separated values) list.

The data import is done by polling a directory for files containing CSV data. The import is performed by a cron job that executes a separate PHP command line script csvimportregserver.php at a defined interval (every 5 minutes by default). On Red Hat Enterprise Linux and derivative distributions, you need to install the php-cli package which provides the required PHP command line utility.

The CSV file must contain the following fields, separated by comma or semicolon:

UserName:Unique user name which will be used to create the user account
Email:Email address of the user
Password:A password for the user. If empty, the user can define a password during the initial registration process as described in the Reference Guide.
Distributor:The Provider Code of the user’s Provider (currently unused)
ExtReference:A free text field which can be used to assign an external reference id (e.g. a cost center)
Department:A free text field which can be used to set a department reference for the user
Language:Language of the user

Example file structure:

UserName;Email;Password;Distributor;ExtReference;Department;Language
TeamDriveUser1;TD_User1@yourdomain.com;;XXXX;1234;Int1;EN
TeamDriveUser2;TD_User2@yourdomain.com;;XXXX;1342;Int2;DE

Note

Note that even though the CSV file contains a field to define a user’s provider code, this value is currently not used. Instead, the provider code is defined by the user that uploads the CSV file via the Administration Console or by the directory the file is located in. If you need to upload user accounts for multiple providers, create one file per provider account and upload them separately.

Enable CSV Upload via the Administration Console

Enable CSV import in the provider settings by adding the option CSVIMPORT/CSV_IMPORT_ACTIVE via the Administration Console and setting it to True. This enables the CSV import functionality via the Administration Console. In this mode, the CSV files and result logs are stored in the Registration Server’s database and can be managed via the Administration Console.

To upload your CSV user data manually via the Administration Console, follow the instructions outlined in chapter Adding Users via CSV File Import.

Uploading CSV Files to a Directory

As an alternative to the manual upload via the Administration Console, you can define a directory on the Registration Server that will be scanned for CSV files periodically.

This allows for an automated process to create or disable user accounts by uploading updated CSV files using tools like scp, sftp or rsync from another server. An example directory structure can be created in /var/tmp using the following command (replace XXXX with your provider code):

[root@regserver ~]# cd /var/tmp
[root@regserver ~]# install -m 700 -o apache -g apache -d csvimport
[root@regserver ~]# install -m 700 -o apache -g apache -d csvimport/XXXX
[root@regserver tmp]# for dir in error success ; do install -m 700 \
-o apache -g apache -d csvimport/XXXX/$dir; done
[root@regserver tmp]# tree csvimport
csvimport/
`-- XXXX
    |-- error
    `-- success

3 directories, 0 files

In addition to activating CSV import via the CSV_IMPORT_ACTIVE setting as outlined above, you need to add and configure the following Provider Settings:

CSVIMPORT/CSV_USE_FILESYSTEM:
Set this option to True to use a directory on the Registration Server for uploading user account information in a CSV file. You should only enable this setting after you created the required directory structure and updated the following settings accordingly.
CSVIMPORT/CSV_UPLOAD_DIR:
This directory is the location for uploading new CSV files that should be processed by the import script (e.g. /var/tmp/csvimport/XXXX/ in the example above). The name must end with a slash. Each provider must to use a different directory. It must be readable and writable for the Linux user that the CSV import job is running under (apache by default).
CSVIMPORT/CSV_SUCCESS_DIR:
This directory contains the log files for successful CSV imports (e.g. /var/tmp/csvimport/XXXX/success in the example above). The name must end with a slash. It must be readable and writable for the Linux user that the CSV import job is running under (apache by default).
CSVIMPORT/CSV_ERROR_DIR:
This directory contains the log files for failed CSV imports (e.g. /var/tmp/csvimport/XXXX/error in the example above). The name must end with a slash. It must be readable and writable for the Linux user that the CSV import job is running under (apache by default).

Now copy the CSV file containing your user accounts into the directory defined in CSV_UPLOAD_DIR (e.g. /var/tmp/csvupload/XXXX in the example above). Once the cron job has been enabled, the file will be processed. Afterwards, you can review the processing status via the Administration Console (Manage Users -> Download CSV / log files).

Enabling the CSV Import Cron Job

To actually perfom the CSV import, you need to enable the cron job that executes the import script in a periodic manner.

The TeamDrive Administration Console installation package installs a sample crontab entry in /etc/cron.d/td-regserver-csvimport. Open it with a text editor and adjust it to match your requirements. As a minimum, you need to remove the comment sign from the last line to actually enable the script. The default frequency is 5 minutes. Refer to the crontab(5) manual page for more details on the format of this line:

# Sample crontab script to enable CSV import of user accounts
# into the TeamDrive Registration Server database
#
# Use /bin/sh to run commands, no matter what /etc/passwd says
SHELL=/bin/sh
# Mail any output to the following user
MAILTO=root@localhost
#
COMMAND="/var/www/html/csvimport/csvimportregserver.php"
# Uncomment the following line to enable the CSV import script
# */5 * * * * apache flock -x /var/tmp/csv_import_running.lock -c $COMMAND

The import of a single user requires about 1 second. To be able to import more than 300 users, the call to flock prevents that another import process gets kicked off before the first one has finished.

If your list of users does not change frequently, it might make sense to keep the cron job disabled and only activate it temporarily, after a new CSV file has been uploaded.

Customizing a CSV Import

The CSV import can be further customized using the following Provider settings:

CSVIMPORT/DISABLE_MISSING_CSV_USERS:
If set to True, any user account not present in the CSV import will be disabled on the Registration Server. In this mode, your CSV user file always needs to contain all active user accounts.
CLIENT/USE_EMAIL_AS_REFERENCE:

Set this setting to True if you wish to use the user’s email address to reference your users between your system and the Registration Server. A few things need to be changed:

  1. The username fields in the CSV file must be left empty
  2. The Registration Server will generate a unique username for new users. The generated usernames will continue to be used internally, because the system is based upon using usernames. You will still see them in the Admin Console of the Registration Server and the Host Server Admin Console, however in the client application (required version at least 3.0.7 (257) ) users will only see their email address. The CSV import will match the email address back to the usernames (for users that existed before the import). You have to make sure, that no duplicate email address will be created using the Admin Console.
  3. The Client Setting allow-email-login must be set to true, so that your users can login using their email addresses.