Uploading Users with a Flat File (CSV)

Introduction

There are multiple methods to bulk create and manage user accounts on a Moodle site. The simplest method is to create a ‘flat file’ in a format called .csv and to upload this file to Moodle. Each row of a flat file contains profile data for a single user; each column contains the values for a specific field, such as username or email address. Taking a peek into the contents of a .csv file will quickly give an idea:

 

[Image 01]

Moodle takes the user data placed in the file and creates new, or modifies existing user accounts as needed.

The objective of this article is to provide the information needed to successfully upload user account data from an external source on to a Moodle site using the flat file (.csv) integration method. Using this information, you will be able to create new user accounts on a Moodle site, as well as modify and delete existing accounts.

Sections/Summary of the Steps

Before getting into the details of uploading user profile data using a flat file, please note the following steps:

• Step 1: Creating the flat file
• Step 2: Uploading the file
• Step 3: Previewing uploaded data & configuring how it will be processed
• Step 4: Confirming & reviewing the results

Beyond presenting the above basic workflow, this article will also expand upon the following:

• Best Practices and Common Mistakes
• Updating existing user accounts

Step 1: Creating the flat file

‘Flat file’ is the name of a simple data file formatted so that each row contains one user account, and each column contains values for a particular field of the account. The flat file can be created using a simple text editor such as Microsoft’s Notepad, or using a spreadsheet editor such as Excel. For example, the same .csv file whose content is shown above in Image 01, if opened with Notepad, will look like this:

[Image 02]

 

Notice that in Notepad, each value is separated by a comma - with NO additional space before or after the commas.

 

As you already are likely to know, unless you consciously select a specific file format, your software will save a newly created file in its own default format - i.e. as .txt in Notepad; and as .xsl in Excel. When creating your file using either of these programs, you will need to ‘save as’ .csv .

 

We will be using Excel for the examples in the rest of this document.

 

Each new account on a Moodle site requires the following minimum information on the owner of the account:

 

username, firstname, lastname, email

 

More commonly, organizations like to keep some further information as part of their users’ profiles. Here is an example of a slightly more loaded profile template, with the required fields highlighted:

 

username, firstname, lastname, email, city, country, institution

 

In order for Moodle to know which exact fields in the user profiles will be populated with the data in our file, a header row indicating what field data is carried in each column needs to be inserted to the very top, as shown in yellow highlight in the image below:

 

[Image 03]

 

In the rest of this document, we will dive further into this example and together walk through each of the steps of flat file user upload. As a sample file, we will use the file presented in Image 03.

 

We suggest at this point that you take note of the range of fields (i.e the column headings) included in this sample file, and compare with the actual range of fields your organization would like to include in user profiles on your Moodle site. For further information on the fields that may be part of user profiles, please see http://docs.moodle.org/26/en/Upload_users#Fields_that_can_be_included .

 

Here is what we have done so far:

 

• open the Excel file containing existing user data;

• save it in .csv format - good to do this from the outset, as it is easy to forget later;

• insert the header row indicating the fields that each column contains.

 

The ordering of the columns do not matter, so long as the correct field names are written to the top of each column.

When the file is ready, proceed to the next step of file upload.

Step 2: Uploading the file

As a best practice, we strongly recommend that you test the upload process with a file that only contains the top 3-4 rows of your master user data file, before going ahead with uploading the complete user data. This test upload will give you the chance to catch any glitches in the process and to review what the resulting new user accounts will look like. After confirming that these initial accounts were created as intended, you will be ready to upload the complete file.

 

To start the upload process, navigate to ‘Upload users’ under Site administration:

 

[Image 04]

 

You will be presented with a screen where you are invited to choose the file which contains the user data:

 

[Image 05]

 

Notice that you are presented with some further settings below the file upload facility. You may leave those as their default values.

 

Click on ‘Choose a file’ and you will be presented with a file picker. Click on ‘Upload a file’ and then click on the Browse button to select the file on your computer:

[Image 06]

 

Confirm that you have selected the correct file, and click the ‘Upload file’ button:

 

 

[Image 07]

 

Your file is now uploaded to the site and you are ready to ‘Upload Users’ contained in this file:

 

 

[Image 08]

 

At this point, Moodle has actually taken in the data presented in the file and now presents you with a screen featuring three sections:

 

• a preview window showing the uploaded data;

• settings that determine how the data will be interpreted and processed; and

• options for modifying the default values of a user profile.

 

[Image 09]

The next step in our workflow will be to go through each of these sections.

Step 3: Preview & configure

The preview window

 

The preview window gives you a glimpse into the end result. This is the place to catch major formatting-related issues.

 

Settings

 

These settings determine how the data in your file will be processed. Below is a brief overview of these settings:

 

• Upload type: This is the most important setting, and tells the system whether you are creating new users, updating existing accounts, or both.

• New user password: You are able to set passwords by including a field for it on the .csv - or, you can have the system generate them randomly.

• Existing user details: If you are updating existing users, this setting determines the particulars of how that will be done.

• Existing user password: If you will be overriding existing user data, the system will ask you to specify what to do with existing passwords specifically.

• Allow renames

• Allow deletes

• Allow suspending and activating of accounts

• Prevent email address duplicates: We recommend as a best practice not to allow duplicate emails on the site.

• Standardise usernames

• Select for bulk user actions

 

Default values

 

This section of the screen offers you quick access into the default values for user profiles on your site. This gives you the option to modify the values so that the profiles of the newly uploaded/updated users will reflect these modifications.

 

One setting worth noting is the first row in this section which allows you to ‘choose an authentication method’. The subject of authentication is beyond the scope of this document.  Please simply note that if you will be setting different authentication methods to be used with different groups of users on your site, you have the option to set these different preferences for groups of users ‘per flat file’.

Step 4: Confirm & review results

 

If you have reviewed the content of all three sections of the screen described above, you are ready to confirm the upload by clicking on the ‘Upload users’ button at the bottom of this screen. This is the moment when the user accounts are actually created or updated.

 

Once the process is complete, you are presented with a ‘results’ screen. In addition to listing the new/updated users, the bottom of this screen will give you the total number of users created, as well as if there were any errors.

 

[Image 10]

Best practices & common mistakes

 

• Ensure that you have saved the file in .csv format.

• Ensure that the field names are entered correctly on the top row of the .csv file.

• If using a text editor to create your CSV file, remember to place consecutive commas with no space between when certain fields are empty for certain users, in order to correctly match the user information to the correct field names.

• Country code convention: use two letter abbreviation (eg. United States = us, Canada = ca)

• Field size limits must be within your site maximum.

• Duplicate email entries used without selecting ‘update existing accounts’ as upload type will cause user account conflicts and cause the upload to fail.

• We recommend using UTF-8 encoding on the file.

• There is some further information here: http://docs.moodle.org/26/en/Upload_users#Hints

Updating existing accounts

 

(The section below is taken from http://docs.moodle.org/26/en/Upload_users#Updating_existing_accounts )

 

By default Moodle adds new user accounts and skips existing users lines where the username matches an existing account. Set "Upload Type" to Add new and update existing users, and existing user account will be updated.

 

• Add all, append number to usernames if needed

• Add new and update existing users

• Update existing users only

 

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

Deleting accounts

(The section below is taken from  http://docs.moodle.org/26/en/Upload_users#Deleting_accounts )

If the deletedfield is present, users with value 1 for it will be deleted. In this case, all the fields may be omitted, except for username. 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 suspended. 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