Uploading Users in Bulk

Uploading users in bulk is generally carried out when you first upload users into Moodle and then when new users are added each year.

Pupil information is typically available in an existing application (for example a school management system, such as SIMS). Data from this system can be exported to an Excel spreadsheet and then uploaded into Moodle.

File Format

Before uploading users, a csv file has to be generated which has to conform to a certain format. Excel will produce a file in this format and instructions for doing this are provided later in this section, so you don’t need to worry about this.

Moodle’s upload function supports three types of data fields: required, default and optional

Field Name / Fields
Required / username / firstname / email
password / lastname
Default / institution / country / auth
department / lang / timezone
city
Optional / idnumber / icq / phone1
phone2 / address / url
description / mailformat / maildisplay
htmleditor / autosubscribe / course1…5
group1…5 / type1…5 / role1…5

Required fieldsmust be included in the first row, and then defined for each user. Default fields are required, but do not have to be specified – if they are not included then the values are taken from the super admin account. Optional fields are completely optional and as we suggest they are not used, they are not dealt with any further in this training.

For suggestions on creating usernames, passwords, email addresses, etc. and the considerations around each of these areas, please see the Best Practice guide.

Creating a CSV File from MS Excel

Your final excel file should look like this:

  1. Create a blank spreadsheet with the following headings in the top row. There must be one word in each cell: firstname, lastname,password, username, email (see row 1 in the above example). As you complete each stage that follows you can copy the data into this spreadsheet.

Once you have inserted a formula you cannot copy and paste the contents in the usual way. To copy the information in a cell you need to ‘copy’ and then ‘paste special’ selecting ‘values’.
  1. Export a class list from SIMS as an Excel file. You need a list of names (first name and last name) and to know the year they will leave, or the year they started, Primary school (this helps to identify a cohort).
  2. If you have more columns of information than you need from SIMS, follow the instructions below.

Selecting requested columns

Create a new spreadsheet and copy & paste required columns across.

  1. If you have a column where both first and last name are in the same cell, follow the instructions below. It may be worth doing this on a separate spreadsheet and copying the names across.

Splitting names

  • Assuming your ‘name’ cell is A2 and the first name and last name are separated by a space (e.g. roger jones)
  • Insert the following formula in a cell in the same row to get the first name:

=LEFT(A2,FIND(" ",A2))

  • Insert the following formula in another cell in the same row to get the last name:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

  • Extend the 2 new cells downwards and the rest of the names will be changed accordingly.
  1. Every user needs a username. Guidance on creating usernames is included in the Best Practice Guide. This is one possibility.

Generating user names

A quick way to create user names is to use the first name and last name separated by a full stop. This works as long as you don't have a huge list of names, in which case you might end up with some duplicates. When this happens you may have to add a number.

Insert the following formula in the cell under ‘username’ =A2 & “.” & B2

  1. Generating passwords

If you enter ‘changeme’ for every user they will enter the password ‘changeme’ the first time they log on and then be directed to choose their own password for future logins.

  1. Generating Email addresses

If a school does not use email addresses it is necessary to create dummy emails which are then disabled in the user’s profile.

One possibility for email addresses is the following

  • Assuming your username is in C2, enter the formula =C2 & “@yourschool.lancsngfl.ac.uk” in the cell under ‘email’
  • Copy the new cell downwards and the email addresses will be created accordingly

It is possible when doing a bulk upload to give everyone the same email address.
  1. Spaces in fields

Spaces at the beginning or the end of a field can cause problems. Excel provides a TRIM(A1) function which removes all spaces from a text string except for single spaces between words.

Once your data is in the right shape it can be exported as follows:

In Word 2003 or older, go to File  Save As… and select ‘CSV (comma delimited)’. In Word 2007, select the Office Button, go to Save As  Other Formats and select ‘Comma-separated Text File (.CSV).Then you can give the file a descriptive name and click the Save button. You may be warned about formats at this point, but this is ok and you should just click to continue. This file can then be used from within Moodle.

Uploading Users

In order to upload users, you have to be logged in as Moodle administrator and select UsersAccountsUpload Users in the Site Administrationblock.

Setting / Description
File / The location of the comma-delimited text file on your local computer.
CSV delimiter / When you export from Excel this is usually a comma.
Encoding / Leave this as it is, on UTF-8
Preview rows / Number of users to be previewed during the upload process so that you can check the format looks right.

Moodle will then preview the users to be uploaded and give you some more choices about settings.

Setting / Description
Upload type / Most of the time you will want to upload new users and this can be left as ‘add new only’. There are also options for updating accounts, which should be approached with care.
New user password / The CSV file we created included a password so this can be left. If Moodle creates a password it tends to be very secure but also hard for young children to remember.
Email activated / If users will not be using email and/or do not have an account, this should be set to ‘This email address is disabled’.

Other settings are optional and can be left as default.

Once all settings have been specified and the ‘Upload Users’ button has been pressed, Moodle will start the actual importing process:

At the end of the user import, a short message is displayed summarising the upload. It contains the number of users added, the number of users updated and the number of errors which have occurred.

If any errors occur, it is recommended to identify the respective users immediately and modify their user settings manually.

CLEO Moodle MIS Integration

CLEO has developed a set of tools which allow the migration of information from SIMS to Moodle. The process covers two main stages:

  • Moodle SIMS.net Class & User Export
  • Moodle SIMS Import

You can further details of these on in Learning Platform section.

1