Uploading Historical Course Completion Data in Moodle Workplace

Uploading historical course completion data can be particularly helpful when certification and/or recertification is being used in Moodle Workplace. In the example we will outline here, a training session happened previously and now course completion needs to be updated to satisfy the requirements for a program that contains that course. This process assumes you have a pre-existing course in place that has course completion set. 

To upload attendance/completion data for training sessions involves two overarching steps: 1) Generating the completion spreadsheet to upload and 2) Uploading the spreadsheet to Moodle. 

Generating the spreadsheet

Step 1: Create your training record spreadsheet.

1. Create four columns with headings exactly as follows:

   1. username

   2. email

   3. coursecompleted1

   4. coursecompleteddate1

2. Complete the four columns as follows:

   1. Username: Leave this column blank for now.

   2. Email: Enter the email address of the user as listed in their Moodle profile.

   3. Coursecompleted1: In this column, enter the shortname of the completed Moodle course exactly as it appears in the Moodle course settings.

   4. Coursecompleteddate1: For this column, enter the completion date to be recorded in Moodle in the format YYYY-MM-DD.

Step 2: Create a master list of users for username lookup.

1. Log into your site.

2. Go to Site administration > Users > Bulk user actions.

3. Scroll down the page and click on ADD ALL to select all users on the site.

4. Select Download from the dropdown menu at the bottom of the page and click GO.

5. Confirm the file export type is set to Comma separated values (.csv) and click Download.

6. Create a new sheet in your training record spreadsheet.

7. Import or copy the data from the file downloaded from Moodle into the new sheet.

Step 3: Prepare the master user list spreadsheet for username lookup.

1. Rename the sheet with the user data exported from Moodle: Master user list.

2. Delete the first column, labeled id, from the sheet with the user data exported from Moodle.

3. Insert a column to the right of the column labeled email.

4. Copy (or cut) the data from the username column and paste it into the column to the right of the email column.

5. Delete the original username column from the sheet. The resulting sheet should appear as in the example below.

Step 4: Add the formula to the training record spreadsheet to lookup usernames.

1. Before moving back to the training record spreadsheet, scroll to the bottom of the Master user list spreadsheet and make note of the number of the last row.

2. Now, return to the training record sheet in your spreadsheet (the one with the coursecompleted columns).

3. In the first cell of the column labeled username enter the following formula: =vlookup(B2, ‘Master user list’!$A$2:$B$XXXXX, 2, false) where XXXXX is the number of the last row on the master user list spreadsheet.* 

4. Now, copy the formula from the first cell down to the bottom of the username column. The usernames should appear soon after.

5. Download the completed spreadsheet as a .csv.

If interested, this formula works as follows:

1. B2 refers to the cell we’re trying to match. In this case, we are looking for the email address of the user in the master list in order to find the username.

2. ‘Master user list’ refers to the name of the sheet to be used to look up the information.

3. $A$2:$B$XXXXX refers to the range on the master list to be searched. We use dollar signs in front of the row and column numbers so that those numbers don’t change as we copy the formula into other cells.

4. The 2 tells the spreadsheet which column, counting left to right, on the master use list to refer to when returning information. If we wanted to lookup, or return, the manager’s email address then we would enter 12 instead.

5. False refers to the fact that the data in the master list is not sorted in ascending order.

Uploading the spreadsheet to Moodle

To upload the spreadsheet to Moodle, complete the following:

1. Go to Site Administration > Users > Upload users.

2. Upload the spreadsheet in the designated area either by dragging the file into the space or clicking Choose a file > Upload a file. Click Continue.

3. On the preview page, configure the options as follow (if not mentioned, leave at default):

   1. Upload type: Update existing users only

   2. Allow suspending and activating accounts: No

   3. Standardize usernames: No

4. Click Upload users at the bottom of the page. Depending on the number of users in the spreadsheet, this process may take awhile. 

   1. Note that sometimes usernames can cause Moodle to generate errors during this process (especially if certain characters are used), but this should not impact the results.

   2. If there are any unique errors, make note or take a screenshot and Moodle Support can work to help you resolve them.

5. Confirm the process was successful by spot-checking completion data for a few users either directly through the course completion report within a course or through the Moodle Workplace report builder reports.