When uploading users, either via the manual method or the automated method, the first step is to create your CSV file. Below are some instructions and guidelines to help you create your CSV file.
| Table of Contents | ||||
|---|---|---|---|---|
|
General Guidelines
The following general guidelines are true regardless of what actions you're trying to accomplish with user uploads:
The CSV file has fields separated by a comma (or other delimiter) ONLY - no space. The first line contains the valid field names. The rest of the lines (records) contain information about each user.
Be careful with special characters in field information like quotes or other commas. Commas must be escaped. Rules and examples about how quotation marks are handled are discussed later in this document.
Test a CSV file with only one record before a uploading a larger file.
You can use a spreadsheet program such as Microsoft Excel to create the file with the required columns and fields, and then save the file as "CSV (comma delimited)". These files can be opened with simple text editors for verification.
Sample File
Here is a sample file that is valid for uploading users. You can either copy / paste it in your favorite CSV editor or download it here:
username,firstname,lastname,email
alex.bryan,Alex,Bryan,alex.bryan@somedomain.com
bea.chandler,Bea,Chandler,bea.chandler@somedomain.com
...
There are some important rules to keep in mind for the password:
If the password is included in the CSV file, the value that you specify should meet the requirements for the system's password policy
If the password is included in the CSV file, the wizard will show a setting labelled "Force password change". The default option is that "User's having a weak password" will be forced to change their password. In this case, "weak" is determined by comparing the password you put in the CSV file with the system's password policy.
This option will not appear if the password column does not appear in the CSV file.
If the password is omitted from the CSV file, a password will be generated for each user and welcome emails will be sent out. The text for the welcome email language string with the identifier 'newusernewpasswordtext' and can be customized by following the Modifying Language Strings procedure.
You can also use the value of "changeme" in your CSV file. This option will not send the user a welcome email, but it will require them to change their email address the first time they log in. This option is not considered secure, particularly if you are creating a lot of users at the same time. If you use this option, any user who can guess someone else's username can log in by entering "changeme" in the password field.
Preferred Language
If your system supports multiple languages (English and French), you can specify the user's preferred language. The valid values are "en" and "fr_ca".
lang
Country Codes and Provinces
The country field uses a two-letter code in upper case. The country code for Canada is "CA" and the country for the United States is "US". Entering "ca" or "USA" for example will generate a database error. If you are not sure what country code to use, you can consult the list of country names and code elements available on the web, such as Wikipedia.
...
The concept is the same for provinces, which also use a two-letter code. For Ontario for example, the province code is "ON". See https://en.wikipedia.org/wiki/ISO_3166-2:CA for a list of codes for Canadian provinces and territories.
country, provincestate
Authentication Method
The authentication method can also be specified. The valid values are "manual", "ldap", "oauth2" and "saml2". These values are case-sensitive.
...
If your learning environment has multiple sites (e.g. see /wiki/spaces/Healthcare/pages/34844303see What is Multi-site?), you can specify the theme to be used by individual users by using the "theme" column.
...
Each theme available in your system has a unique identifier, which is displayed in the theme's settings page. Using the example below (see image below), I would enter the value "adaptable" in the "theme" column in the CSV file to associate the user to the Adaptable theme.
...
Renaming Users
The username is considered a key in the system for many functions such as uploading learning records and attendance records. You can change the value of the username just like any other field, as long as "Allow renames" is set to "Yes'. In other words, if you had to change the username for a user where username=jsmith and idnumber=12345, you could create the following CSV file. In the "User mapping" section you would map the idnumber from the CSV file to the idnumber field in the system, and set "Allow renames" to "Yes". This would change the username from jsmith to jdoe. (Note that while "idnumber" is suggested in here, you can use any unique identifier in the user's profile.)
...
If you do not have any identifier other than username in your CSV or system, you must use a field called "oldusername" to rename users. Ensure you set the proper mapping and other fields in the user upload wizard properly as per the screenshot below.
...
Suspending Users
Special fields are used to suspend users.
...
When you delete a user, you delete all the information associated with the user, including grades, certificates of completion, and their history. It is therefore recommended to suspend users rather than deleting them. Suspending users de-activates their account, but retains their information.
Working with Custom User Fields
Custom user profile fields, which are described in more details here, can also be uploaded via CSV. As they are custom, the list and name of fields will depend on which field your System Administrators added to your system.
...
When uploading information in a checkbox, use the value of "1" to check it and "0" (zero) to uncheck it.
Field Type: Date/time
When uploading to a Date/Time custom user field, the date format must be YYYY-MM-DD. For example, to show a date of September 1, 2017, the date must be 2017-09-01. If you are specifying the time, the convention is 'YYYY-MM-DD-HH-MM-SS' using the 24-hour time format.
Field Type: Dropdown Menu
When uploading to a dropdown menu, the values must pre-exist in the menu. If the value does not pre-exist, the system will still store the value in the database (e.g. it will not report an error), but it will not add it automatically to the dropdown menu. For example, if you track cost centres using a dropdown menu and your organization adds a new cost centre, simply passing the new value in the CSV will not add the value to the dropmenu menu. The value must be added using the procedure described in Managing Custom User Profile Fields.
Field Type: File Upload
Given that CSV is text-based, you cannot add files to a "File upload" Note that no value (e.g. an empty cell in your spreadsheet or "") is equivalent to adding a "0". In other words, if you have a column for a field that is a checkbox and do not specify a value, the checkbox for the users in question will be unchecked as a result of the upload.
If the value for the checkbox is anything other than 0, 1 or a space, the system will store the value in the database but visually will render the checkbox as unchecked.
Field Type: Date/time
When uploading to a Date/Time custom user field, the date format must be YYYY-MM-DD. For example, to show a date of September 1, 2017, the date must be 2017-09-01. If you are specifying the time, the convention is 'YYYY-MM-DD-HH-MM-SS' using the 24-hour time format. Note however that if the field itself is not configured to accept the time (e.g. it only accepts the date), then the HH-MM-SS portion of the value will be ignored.
If the format of a date/time is not understood by the system, the system will not return an error. The field will simply be left empty for the record in question.
Field Type: Dropdown Menu
When uploading to a dropdown menu, the values must pre-exist in the menu. If the value does not pre-exist, the system will still store the value in the database (e.g. it will not report an error), but it will not add it automatically to the dropdown menu. For example, if you track cost centres using a dropdown menu and your organization adds a new cost centre, simply passing the new value in the CSV will not add the value to the dropmenu menu. The value must be added using the procedure described in Managing Custom User Profile Fields.
Field Type: File Upload
Given that CSV is text-based, you cannot add files to a "File upload" field type.
Field Type: OCI ID
When uploading to an OCI ID field, the value must adhere to a very specific convention that includes the country, province, and acronym for the college, followed by the unique identifier for the user. All components must be separate by a hyphen (-). The complete list supported by your learning environment can be seen by going to Site Administration > OCI > User Identifiers.
For example if uploading the OCI ID for a Physician affiliated with the College of Physicians and Surgeons of Ontario with CPSO #12345, the value would be:
CA-ON-CPSO-12345
Field Type: Social
In earlier version, the learning environment default fields to hold information for ICQ Number, MSN ID, AIM ID, Yahoo ID, Skype ID, Web page. Given that most of these 3rd party systems are now deprecated, these fields were also deprecated. That being said, organizations who still wish to track this information can store it in this special field type.
Field Type: Text Area
...
The values must be one of the following (case-sensitive):
For the System Administrator role, use "manager" (without the quotes)
For the System Coordinator rule, use "coordinator"
For the Supervisor role, use "supervisor"
For the Course Creator role, use "coursecreator"
sysrole1,sysrole2,sysrole3
...
The role can be "student", "editingteacher" or "teacher". The enrolment duration must be in days.
...
In the above example, session1 refers to the ID of the first session a user will be signed up for, session 2 to the second session, and so on. The ID of a session is identified by its URL when viewing a session. For example, the unique ID for "http://www.dualcode.com/mod/facetoface/sessions.php?s=139" is 139.
Note that adding users to classroom sessions can also be done using the procedure explained in the Importing Attendance Records article.
...
You can add users to sites and cohorts. Keep in mind that the concept of sites is restricted to /wiki/spaces/Healthcare/pages/34844303multi-site deployments.
In both cases, you would use the "cohort1" column in your CSV file. If you need to add a user to more than 1 site / cohort, you could use cohort2, cohort3, and so on. The '1' in cohort1 is important. Simply adding a column called "cohort" without the '1' will not work.
...
There are a few other rules to keep in mind when preparing your CSV file:
Commas within a field must be encoded as , - the script will decode these back to commas
For Boolean fields, use 0 for false and 1 for true
When dealing with quotation marks, the following, the following values will be transcribed (represented by → below) from the CSV to the system:
Ottawa → Ottawa
'Ottawa' → 'Ottawa'
"Ottawa" → Ottawa
""Ottawa"" → "Ottawa"
To prevent users from receiving a large number of e-mails from courses or forced subscription forums, use the maildigest field. The options for this field are:
0 = No digest
1 = Complete digest
2 = Digest with just subjects
Other Hints
Encoding File Format
On the initial "Upload user" screen, you may select the file encoding format from a pull down list. These include UTF-8 (the default), ASCII, ISO-8859-1 to ISO-8859-11 or any one of over 36 formats. You need to make sure that your CSV file matches the encoding format that you select here. If you are unsure, you may leave the default, which is to let the system auto-detect the encoding format.
Field Size Limits
Some fields have maximum character lengths. Typically the file will import to the preview list screen but not finish the process. Common fields to cause problems are "Institution" which is limited to 40 characters, and "City", also limited (20 characters). The error will be "User not added - error".
...
Maildisplay, htmleditor and autosubscribe can be passed in your CSV, but they can also be easily set using the wizard if you intend on using the same value for all users when creating them.