View source for Upload users

{{Accounts}}
==Uploading users via text file==

There are many options for uploading information (fields associated with a user) with this method: from enrolling users in multiple courses with course specific [[Roles|roles]] to updating user information in the [[User profile]] to deleting users from the site.

''Tip:'' It is usually not necessary to upload users in bulk with Upload users. To keep maintenance work down you should first explore forms of authentication that do not require manual maintenance, such as [[External database authentication|connecting to existing external databases]] or letting the users create their own accounts ([[Self enrolment]]). See [[Authentication]] for more information.

==File formats for upload users file==
The upload users 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.

''Tip:'' Avoid special characters in field information like quotes or other commas. Test a file with only one record before a large upload.

''Tip:'' You can use a spread sheet program to create the file with the required columns and fields. Then save the file as "CSV (comma delimited)". These files can be opened with simple text editors (eg, [https://notepad-plus-plus.org/ Notepad++]) for verification. 

===Valid upload file for testing===
Here is an example of a simple valid upload file:
(Column headers on the first line of the file are only highlighted in bold in this example to distinguish it from the rest of the of the data/user details)

 '''username,password,firstname,lastname,email,course1,group1,cohort1'''
 jonest,verysecret,Tom,Jones,jonest@someplace.edu,math102,Section 1,year 3
 reznort,somesecret,Trent,Reznor,reznort@someplace.edu,math102,Section 3,year 4

== User Fields that can be included==

''Tip:'' We strongly recommend that you test a file that contains fields you proposed to use with one user before attempting a file upload for the first time. 

===Required fields=== 

These are the required user identification fields:
<code>username,firstname,lastname,email</code>
Validity checks are performed for:
* '''username''' can only contain alphabetical '''lowercase''' letters , numbers, hypen '-', underscore '_', period '.', or at-sign '@' 
* '''email''' is in the form: ''name@example.com''

===Passwords===

The "password" field is optional if the 'New user password' setting on the upload screen is set to "Create password if needed and send via email"  but is required if the setting to "Field required in file".

If included, values should meet the requirements for the site's [[Site_policies#Password_policy|Password policy]]. To force password change for a particular user, set the password field to <code>changeme</code>. If omitted, a password will be generated for each user (during the next Cron job) and welcome e-mails sent out.

Note: the text for the welcome e-mail is in the language settings in ''Site administration > Language > Language customisation''  with a String identifier of 'newusernewpasswordtext'.

===Optional user fields===

To provide values other than the default include one or more of these

<pre>institution,department,city,country,lang,auth,timezone,idnumber,icq,phone1,phone2,address,url,description,mailformat,maildisplay,htmleditor,autosubscribe</pre>

Most of the these are user profile fields or user preference fields that belong to the user profile and are the filled in the user or at manual creation. Some however require specific formats:

See [[Additional name fields]] for more details. Key things to note are:

'''country-''' use a country TWO LETTER CODE, in upper case, eg AU,ES,GB,US. These are all UPPER CASE.

'''lang''' - use the two letter (or extended four lettter) code as defined in the Moodle language packs, e.g. en, es, en_us, de

'''auth''' - The auth field must be used if the site uses an alternative authentication method, such as LDAP, as otherwise the authentication method will default to manual and users using a different auth method won't be able to log in. Use the shortname codes defined in Plugins > Authentication for the various type, e.g. manual, ldap, cas, mnet, none.

'''timezone''' - Should be in the format as found in the Location settings in terms of Zone/Region, eg. Australia/Sydney, Asia/Kathmandu, Europe/Madrid, etc.

===Custom profile field names===

These are optional and depend on whether you have created any custom profile fields in your site. The name of the header in file is of the form 'profile__field_xxxxx' where xxxx is the unique shortname of custom user profile field name as you created it. The field name must be lower case.

<pre>profile_field_xxxxx</pre>

'''Example''': To create a custom field "genre", you must write a shortname "genre" in the new field, and write "profile_field_genre" in the header of the .csv file.


For custom profile fields that are dates, use the ISO standard format YYYY-MM-DD, eg. 2014-06-19 which will then be properly localized in the interfaced. For example, a field called dohire for date of hire, the fields could be:
<pre>
username,firstname,lastname,email,profile_field_dohire
blumbergh,Bill,Lumbergh,blumbergh@example.com,1990-02-19
pgibbons,Peter,BGibbons,pgibbons@example.com,1996-06-05
tsmykowski,Tom,Smykowski,tsmykowski@example.com,1970-01-01 
</pre>


For custom profile fields that are a menu, use the corresponding value in the menu list from field as you defined it. For example: a custom field 'corporatedivision' with one of three values 'Management', 'Development' or 'Training'. Just insert one of those three words (e.g. 'Training') as the value for that field. Eg.

<pre>
username,firstname,lastname,email,profile_field_corporatedivision
blumbergh,Bill,Lumbergh,blumbergh@example.com,Management
pgibbons,Peter,BGibbons,pgibbons@example.com,Development
tsmykowski,Tom,Smykowski,tsmykowski@example.com,Training 
</pre>

=== Special user change fields===

Three special fields are used for managing user accounts, '''oldusername''', '''deleted''' and '''suspended'''.  [[#Allow_renames|See below for details]].

===Enrolment fields===

(Optional)
:<code>course1,type1,role1,group1,enrolperiod1,enrolstatus1,course2,type2,role2,group2,enrolperiod2,enrolstatus2</code> etc.

:* Header fields must have a numeric suffix such that <code>type1</code>,<code>role1</code>,<code>group1</code>,<code>enrolperiod1</code> and <code>enrolstatus1</code> all apply to <code>course1</code> for course<code>1</code> to course<code>n</code>.
:*<code>course</code> is the "shortname" of the course, if present the user will be enrolled in that course.
:* <code>type</code> sets the role to be used for the enrolment. A value of <code>1</code> is default course role, <code>2</code>  is legacy Teacher role and <code>3</code> is legacy Non-editing Teacher.
:* <code>role</code> may be used to specify roles directly, using either role short name or id (numeric names of roles are not supported).
:* <code>group</code> may be used to assign users to groups in course, using name or id (numeric group names are not supported)
:* <code>enrolperiod</code> may be used to set the enrolment duration, in days, for each course.
:* <code>enrolstatus</code> can suspend users from a course when set to 1 or left blank for enrolled. If a user is previously set as inactive / suspended then a value of 0 will unsuspend them.

*'''Cohort field''': (Optional):
:<code>cohort1</code> 

:Internal cohort id numbers or non-numeric Cohort IDs of existing cohorts must be used; names are not allowed.
*'''mnethostid''' (Optional)

Existing [[MNet]]users can be added to courses, groups or cohorts as below:
#enrolling to courses: username+mnethostid+course required
# adding to group: username+mnethostid+course+group required
#adding to cohort: username+mnethostid+cohort required
#suspending/reviving accounts: username+mnethostid+suspended required
All other operations are ignored. You can not add users, delete them or update them (such as change names or email, profile fields, etc.)

*'''System role''' (Optional)
:<code>sysrole1,sysrole2,sysrole3</code> etc
Users may be uploaded to a system role (usually Manager or Course creator)  by entering the shortname of that role. Other roles can only be uploaded if they have already been assigned in the 'system' context. See [[Creating custom roles]]. Multiple roles can be assigned using <code>sysrole2</code>, <code>sysrole3</code>, etc. fields. Note that the number suffix in no way relates to the number suffixes on the enrolment fields. The numbers must go up in sequence starting at 1.

*'''Unassigning roles'''
Users can also be removed from a given system role by entering the shortname of that role prefixed with a minus symbol: '-'. If the user is currently assigned to that role, they are removed from it. If the user is not currently assigned to that system role, the field value is ignored. However, the field value must refer to a system role that does exist on the system, otherwise an error will occur.
[[File:GlobalRoles1.png|thumb|500px|center|Example of a file for uploading users with global/system roles]]

Commas within  a field must be encoded as &#44 - the script will decode these back to commas.

For Boolean fields, use <code>0</code> for false and <code>1</code> for true.

To prevent users from receiving a large number of emails from courses or forced subscription forums use the '''maildigest'''.  The options for this field are 0 = No digest, 1 = Complete digest and 2 = Digest with just subjects.

===Enroll users to Cohorts (system groups)===

You can enroll users to any Cohort (system level group) by using only the "username" and the "Cohort ID".
Here is a sample CSV file:

 '''username,cohort1'''
 teacher1,system-teachers
 teacher2,system-teachers
 teacher3,system-teachers

==Upload user process==

# Create file for uploading
# Go to ''Site administration > Users > Accounts > Upload users''
# Add file to upload
# Upload users preview - check settings and default user profile settings
# Upload users preview - click "Upload users"
# Upload users results - shows list of users, exceptions made in upload and summary of number of users
# Upload users results - click "Continue"
# Returns to Upload users screen

==Updating users preview==
There are various settings to better control the desired upload behaviour. These settings are found on the "Upload users preview" page.

'''Warning''': errors updating existing accounts can affect your users badly. '''''Be careful''''' when using the options to update.

====Upload type====
The Upload type specifies how to handle existing accounts.
;Add new only, skip existing users : is the default Moodle upload type. It creates a new user account for each new record in the uploaded file. If an existing <code>username</code> is found (i.e., the <code>username</code> in the uploaded file matches an existing <code>username</code>, that record is '''skipped'''. By skipping the existing user account, the data in the existing record is not touched (in contrast to the "Add new and update existing users" option) and a second new user account is '''not''' created (in contrast to the "Add all, append number to usernames if needed" option). 
;Add all, append number to usernames if needed : creates a new user account for each record in the uploaded file. If an existing user account is found, a new account will be created with a number appended to the <code>username</code>. For example, if a user account for <code>username</code> 'jsmith' already exists and a new record in the uploaded file contains a record for <code>username</code> 'jsmith' an additional user account is created with a 1 '''appended''' to the <code>username</code> to produce user 'jsmith1'. 
;Add  new and update existing users : creates a new user account for each new user in the upload file. If an existing user account with the same <code>username</code> is found, the account information is '''updated''' by the data in the uploaded file. 
;Update existing users only : ignores any new users found in the upload file and updates the user account if a matching <code>username</code> record is found in the uploaded file.

====New user password====
When creating a new user account Moodle can create a new password (if one is not provided) or require a password in the uploaded file.
;Create password if needed : creates a default password for the new user account if one is not provided in the uploaded file.
;Field required in file : requires that a password be provided in the uploaded file in order. If a password is not provided, an error is generated and the user account is not created.

====Existing user details====
The Existing user details options are only available when the Upload type allows existing user accounts to be updated. It specifies how Moodle should process user detail information for existing users.
;No changes : ignores user detail data in the uploaded and leaves the existing user account data unchanged.
;Override with file : overwrites data in the existing user account with the data provided in the uploaded file.
;Override with file and defaults : overwrites data in the existing user account with data provided in the uploaded file and fills in the default values for existing user details when no data is provided in the uploaded file.
;Fill in missing from file and defaults : adds data in the existing user account with data provided in the uploaded file if the field is empty (does not already contain data) and fills in the default values for existing user details when no data is provided in the uploaded file.

====Existing user password====
The Existing user password option specifies how to handle password data for existing user accounts when Existing user details is set to overwrite data.
;No changes : ignores password field in the uploaded user file and leaves the existing user account password untouched
;Update : overwrites the existing user account password with the password provided in the uploaded file

====Force password change====
The Force password change option specifies when to tag a user account so that the next login attempt will require the user to change the user's password.
;Users having a weak password : If the user account has a weak password as defined by the stie's [[Password_policy#Password_policy|Password policy]] then the user will be forced to change the password during the next login attempt. This option is not shown if there the site does not have a [[Password_policy#Password_policy|Password policy]], in other words <code>$CFG->passwordpolicy</code> must be set to see this option. 
;None : None of the users in the uploaded file will be forced to change the password during the user's next login attempt.
;All : All of the users in the uploaded file will be forced to change the password during the user's next login attempt.

====Allow renames====
If the uploaded flie contains the special <code>oldusername</code> field, it is possible to rename a user from the <code>oldusername</code> to a new <code>username</code>. The default setting is to '''not''' allow renames. Keep in mind that renaming a user will require the user to use the new username when logging in.
;No : ignores the <code>oldusername</code> field and leaves the existing user account's <code>username</code> field unchanged.
;Yes : allows the existing user account's <code>username</code> to be changed by the data provided in the uploaded file's <code>username</code> field. The <code>oldusername</code> will be searched for and then updated with the data provided in the <code>username</code> column. 

====Allow deletes====
If the uploaded file contains the <code>deleted</code> special field, it is possible to use the upload file to delete existing user accounts. The default setting is to '''not''' allow deletes. Keep in mind that deleting a user account will prevent that user from logging in. As a protection, site administrator user accounts cannot be deleted with this method. 
;No : ignores the <code>deleted</code> special field in the uploaded file and leaves the existing user account unchanged
;Yes : allows the existing user account to be deleted when the value of the of the <code>deleted</code> field is 1. 

====Allow suspending and activating of accounts====
If the uploaded file contains the <code>suspended</code> special field, it is possible to use the upload file to either suspend or make active (unsuspend) existing user accounts. The default setting is to allow suspending/activating of existing user accounts. Keep in mind that suspending an existing user account will prevent that user from logging in. 
;Yes : allows the existing user account to be suspended when the value of the of the <code>suspended</code> field is 1. 
;No : ignores the <code>suspended</code> special field in the uploaded file and leaves the existing user account status unchanged.

====Prevent email address duplicates====
It is possible, but '''not''' recommended to upload users with duplicate email addresses. By default, uploading users with duplicate email addresses is prevented. See MDL-38104 for some discussion. Further, since MDL-41115 added the ability for users to login with their email address it is even more important that duplicate email addresses be avoided.

To allow duplicate email addresses, go to Site administration ► Plugins ► Authentication ► Manage authentication. You can tick "Allow accounts with same email". Then on the upload users screen you will be allowed to change the "Prevent email address duplicates" setting. However, doing this is not recommended. For more info, see the [[Managing_authentication#Allow_accounts_with_same_email|Managing authentication]] docs page
;Yes : prevents user accounts from being created from the uploaded if an existing user account already has the same email address as found in the uploaded file's  <code>email</code> column.
;No : allows user accounts to be created if an existing user account already has the same email address found in the uploaded file's  <code>email</code> column.

====Standardise usernames====
Standardise usernames is used by default to convert the username to all lower case and to strip out illegal characters. It is possible to not standardise the usernames; however, doing so is '''not''' recommended.
;Yes : standardises usernames found in the uploaded file before updating existing or creating new user accounts so that the username contains only lowercase letters and numbers.
;No : skips standardising usernames found in the uploaded file so that the newly created or updated usernames will be exactly as they are in the uploaded file ('''not recommended''').

For those seeking a more technical explanation, the process for standardising the usernames consists of ensuring the characters are all UTF-8 (fix_utf8) encoded, converting the username to lower case, and then stripping out non-letters/non-number characters (unless ''Site administration > Security > Site policies > Allow extended characters in usernames'' is set on) with something similar to: 

<code>$username = preg_replace('/[^-\.@_a-z0-9]/', '', $username);</code>

====Select for bulk user actions====
After the uploaded file has finished being processed (all new accounts have been created and existing accounts updated as specified by the previous settings), there is an option to select some of those user accounts to perform additional [[admin/user/user_bulk|bulk user actions]] such as 
*Confirm user accounts created through Email-based self-registration which are not yet confirmed by the user
*Send a message (requires Messaging to be enabled)
*Delete user accounts
*Display a list of users on a page
*Download user data in text, ODS or Excel file format
*Force users to change their passwords
*Add users to a cohort

By default, no users are selected for [[admin/user/user_bulk|bulk user actions]].

;No : No users are selected for [[admin/user/user_bulk|bulk user actions]]
;New users : Only newly created users are selected for [[admin/user/user_bulk|bulk user actions]]
;Updated users : Only updated user accounts are selected for [[admin/user/user_bulk|bulk user actions]]
;All users : All users found (existing updated users and newly created user accounts) in the uploaded file are selected for [[admin/user/user_bulk|bulk user actions]]

===Default values===

You can provide default user values for some fields not included in the uploaded file. Some fields include:
*Email display
*Forum auto-subscribe
*City/town
*ID number
*Institution
*Department

==Upload user results ==
After accepting the preview settings by clicking on "Upload users", you should see the the Upload users results screen.
[[File:Upload users results 2.0.JPG|thumb|center|The results screen; everything went well!]]
This screen will show you any exceptions or changes that were made to each user in the upload process.   For example if you were updating user information, the updated information will be shown.  Or if a user was not added that record will be highlighted.

The screen will summarize how many users were uploaded or updated, indicate the number of weak passwords and the number of errors.

==Advanced potentials of Upload user==
===Templates===

''Note: This section needs checking and updating if necessary for Moodle 2.0. Please do so and remove this note when finished.''

The default values are processed as templates in which the following codes are allowed:

* %l - will be replaced by the lastname
* %f - will be replaced by the firstname
* %u - will be replaced by the username
* %% - will be replaced by the %

Between the percent sign (%) and any code letter (l, f or u) the following modifiers are allowed:

* (-) minus sign - the information specified by the code letter will be converted to lowercase
* (+) plus sign - the information specified by the code letter will be converted to UPPERCASE
* (~) tilde sign - the information specified by the code letter will be converted to Title Case
* a decimal number - the information specified by the code letter will be truncated to that many characters

For example, if the firstname is John and the lastname is Doe, the following values will be obtained with the specified templates:

* %l%f = DoeJohn
* %l%1f = DoeJ
* %-l%+f = doeJOHN
* %-f_%-l = john_doe
*<nowiki> http://www.example.com/~%u/</nowiki> results in  <nowiki>http://www.example.com/~jdoe/</nowiki> (if the username is jdoe or %-1f%-l)

Template processing is done only on default values, and not on the values retrieved from the CSV file.

In order to create correct Moodle usernames, the username is always converted to lowercase. Moreover, if the "Allow extended characters in usernames" option in the Site policies page is off, characters different to letters, digits, dash (-) and dot (.) are removed. For example if the firstname is John Jr. and the lastname is Doe, the username %-f_%-l will produce john jr._doe when Allow extended characters in usernames is on, and johnjr.doe when off.

When the "New username duplicate handling" setting is set to Append counter, an auto-increment counter will be append to duplicate usernames produced by the template. For example, if the CSV file contains the users named John Doe, Jane Doe and Jenny Doe without explicit usernames, the default username is %-1f%-l and New username duplicate handling is set to Append counter, then the usernames produced will be jdoe, jdoe2 and jdoe3.

===Deleting accounts===

If the <code>deleted</code> field is present, users with value 1 for it will be deleted. In this case, all the fields may be omitted, except for <code>username</code>. After uploading the file, be sure to change the "Upload type" to "Update existing users only" and the "Allow deletes" option to "Yes".

:''Tip:''  A similar field is available for <code>suspended</code>. This enables a user account to be temporarily disabled rather than completely removed.

Deleting and uploading accounts could be done with a single CSV file. For example, the following file will add the user Tom Jones and delete the user reznort:

 username,firstname,lastname,deleted
 jonest,Tom,Jones,0
 reznort,,,1

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

==Hints==

===Spreadsheet===

If you use a spreadsheet program such as Excel to create your .csv file, check the resulting output in a text editor before you upload it.  It is possible to get trailing commas on each line from an empty field if you have added and deleted columns of information prior to saving the final file. Also check the character encoding. A csv file is a simple text file (ASCII or Unicode) that can be used to upload user accounts.

Excel translates passwords that begin with - (minus) or + (plus) as zero. Even when saving as .csv and saying "Yes" to "Keep this format, and leave out any incompatible features." Check for this before uploading, as a zero halts the upload process.

If you use a formula in Excel to create fields (for example, the concatenate function to create a user name), then remember to copy the cells with the formula and use special paste with values checked to make them into an acceptable data for a csv file.

The upload will also fail if you have trailing spaces at the end of your data fields. Often, this can not be removed with a simple Find " " and Replace with "". If information has been copied from web sources than it is possible to include non-breaking spaces which will prevent your upload from being completed correctly. To find these invisible spaces, use the Find and Replace function in Excel. In the find field, hold alt and type 0160. Leave the replace field blank. 

===Country===
The country should be written as a two letter code, in capitals. For example, use BE for Belgium or NL for the Netherlands.  Using "be" or "nl" or "USA" as a country code will result in a database error.
:''Tip:''  If you are having trouble working out the two-letter code for a country, you can consult the list of [http://www.iso.org/iso/country_names_and_code_elements country names and code elements] available on the ISO Website. A common error is to use UK for United Kingdom; it should be GB.

===Field size limits===
Some fields have maximum character lengths, as defined in the database fields.  Typically the file will import to the preview list screen but not finish the process. Turn on debug to see the fields that are too long. The error will be "User not added - error".

The sizes of some common fields, in number of characters, are currently (3.2):

*username - 100 	 
*password - 255 	 
*idnumber - 255 	 
*firstname - 100 	 
*lastname - 100 	 
*lastnamephonetic - 255 	 
*firstnamephonetic - 255 	 
*middlename - 255 	 
*alternatename - 255 
*institution -  255 	 
*department - 255 	 
*address - 255 	 
*city - 120 	 
*icq -15 	 
*skype - 50 	 
*yahoo - 50 	 
*aim - 50
*msn - 50 	 
*phone1 - 20 	 
*phone2  - 20

===Time zones===

The entry is case sensitive so <code>Europe/London</code> will work but <code>europe/london</code> will not. Use the names from the Timezone list found in ''Site administration > Location > Default timezone'' in their proper format.

===All user fields listed here===
:All the user fields that are valid in an upload file are listed below, except for any custom fields you may have created (for which see below.)
 
<pre>firstname,lastname,username,email,password,auth,idnumber,institution,department,city,country,timezone,lang,mailformat,maildisplay,maildigest,htmleditor,autosubscribe,skype,msn,aim,yahoo,icq,phone1,phone2,address,url,description,descriptionformat,oldusername,deleted,suspended,alternatename,lastnamephonetic,firstnamephonetic,middlename</pre> 

The enrolments into courses information are <pre>course1,type1,role1,group1,enrolperiod1,enrolstatus1</pre> 
where each enrolment is grouped by by number as<pre>course1,role1,course2,role2,course3,role3,course4,role4</pre>
with the number designating the fields that go together.

===Capabilities===

You may wish to create a limited role to allow some users access to this function. Create a role and assign to the user at the system/site level with the following capabilities allowed
* moodle/site:uploadusers
* moodle/role:assign
* In 'Allow role assignments' permit this new role to assign the required roles

In particular, don't forget the moodle/role:assign capability (even if these users have it in the courses they will be enrolling users in - it won't work).

== See also ==
* [http://youtu.be/PFGLZnuu_JI Adding users by using a CSV in Moodle]  MoodleBites video on YouTube
*[[Flat file]] enrolment
* [[User profile fields]] for details of how to include data about custom user profile fields in the upload users file
* [[Upload courses]]

Using Moodle forum discussions:
*[http://moodle.org/mod/forum/discuss.php?d=36851 Can I auto enroll from Excel?]
*[http://moodle.org/mod/forum/discuss.php?d=97903 Uploading users to custom roles]
*[http://moodle.org/mod/forum/discuss.php?d=181259 User upload option: standardise usernames]
*[http://moodle.org/mod/forum/discuss.php?d=144569 Matriculacion con flat file csv] - discussion in Spanish

[[fr:Importer des utilisateurs]]
[[ja:ユーザのアップロード]]
[[de:Nutzerliste hochladen]]
[[es:Subir usuarios]]