Creating a CSV File for Importing Learning Records
When uploading learning records, either to create them or void them, the first step is to create your CSV file. Below are some instructions and guidelines to help you create your CSV file.
General Guidelines
The following general guidelines are true regardless of what actions you're trying to accomplish with learning records:
- 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 learning record.
- 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,course_shortname,completed_on,expires_on,final_grade
alex.bryan,WHMIS,2022-12-01,2023-12-01,85
bea.chandler,Privacy,2022-12-01,2023-12-01,85
Required Fields
All 5 columns above are required in your CSV file. As such, when adding learning records, the following fields are required in the 1st row of your CSV:
username,course_shortname,completed_on,expires_on,final_grade
While the field / column is required in the CSV, you are not required to add a value for "expires_on" nor "final_grade". In other words, if a course does not expire or you do not track grades, the following file is valid:
username,course_shortname,completed_on,expires_on,final_grade
alex.bryan,WHMIS,2022-12-01,,
bea.chandler,Privacy,2022-12-01,,
Username Field
The username field must match the username of an existing user in the learning environment. If the user does not exist, the upload will report an error. This is intentional. The system will not create users when you upload learning records. If you need to create users, consider following the Uploading Users procedure first.
username
In the past, this field was called "actor" as per the xAPI specification. The field name of "actor" will still work, but the for consistency, our documentation now refers to "username".
Course Shortname Field
The course_shortname field must match the shortname of an existing course in the learning environment. If the course does not exist, the upload will report an error. This is intentional. The system will not create courses when you upload learning records. If you need to create courses, consider following the Importing Course Shells procedure first.
course_shortname
In the past, this field was called "object" as per the xAPI specification. The field name of "object" will still work, but the for consistency, our documentation now refers to "course_shortname".
It's also worth nothing that while the LRS (Learning Record Store) does generate learning records for other types of objects, notably programs and activities, you cannot upload learning records for these types of objects using a CSV file. You can only upload learning records for courses.
Completed On Field
The completed_on field must be in the YYYY-MM-DD format.
completed_on
Optionally, you can specify the time as long as you follow the ISO 8601 format, such as 2022-12-01T15:19:21+00:00, where:
- 2022-12-01 is the date in YYYY-MM-DD format (based on the Gregorian calendar)
- (optional) 15:19:21 is the time in hh:mm:ss format (based on the 24-hour clock system). If you omit the time, the system will log the time as 12:00 AM. If including optional time you MUST include a time offset from UTC.
- (optional) +00:00 is the time offset from the UTC (Coordinated Universal Time). For example, an organization located in Ottawa or Toronto would write -04:00. If you omit the the timezone, the system will use the system's default timezone.
- Alternatively, because Microsoft Excel automatically converts dates from YYYY-MM-DD to MM/DD/YYYY, you can specify your dates in the MM/DD/YYYY format, if and only if you do not need to specify the time of the learning record. If you need to specify the time / timezone of the learning records when importing them, you need to continue to use the ISO 8601 format described above.
In the past, this field was called "timestamp". The field name of "timestamp" will still work, but to better differentiate the time of completion from the date the learning records was stored in the system, our documentation now refers to "completed_on".
Expires On Field
The expires_on field must appear in the 1st row of the CSV, but the value is optional. In other words, if the column is not in your CSV file, the upload will fail. But it's ok to leave the value empty for a learning record if it never expires.
expires_on
If specified, the field must respect the same formatting rules as the "completed_on" field explained above.
In the past, this field was called "expires". The field name of "expires" will still work, but for consistency, our documentation now refers to "expires_on".
Final Grade Field
The final_grade field must appear in the 1st row of the CSV, but the value is optional. In other words, if the column is not in your CSV file, the upload will fail. But it's ok to leave the value empty for a learning record if you do not track grades.
final_grade
If specified, the final grade must be a float value. Do not include a % symbol, ex: 93.75. It is based out of 100. In other words, if you enter "9", the system will report that the user has a final grade of "9 out of 100".
In the past, this field was called "finalgrade". The field name of "finalgrade" will still work, but for consistency, our documentation now refers to "final_grade".
Verb Field
In the past, a field called "verb" was required. Note that this field is still supported but no longer required in the CSV.
verb
If specified, the field must contain the value "completed". No other values are supported.
Course ID Number (Optional Field for Version Tracking)
It is also possible to specify a course ID number that is different than the "Course ID number" value that is in the course. This column is optional. If this value is specified, it will be used to populate the "ID Number" in the learning record. If it is not specified or left blank, the learning record will simply use the current "Course ID number" in the course. If the specified course ID number is longer than 100 characters, an error will be displayed.
course_id_number
Voiding Learning Records
A special field is used when voiding learning records.
voided
If you add this column, a value of "1" will void a learning record while a value of "0" will unvoid a learning record. Leaving the value blank will leave the learning record's status unchanged. It is not possible to delete a learning record. You can only void / unvoid it.
When voiding (or unvoiding) learning records, we recommend that you go to the Learning Records report in either a course or at the system level, find the records you want to void, and export them in a CSV file. Then, simply add the "voided" column to that exact same spreadsheet with a value of 1 for each record, and re-upload it as per the steps above.
Other Rules
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
- 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"
Other Hints
Encoding File Format
On the initial "Upload learning records" screen, you may select the file encoding format from a pull down list in the "Show more..." section. 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.
Spreadsheet
If you use a spreadsheet program such as Microsoft 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 learning records.
Excel translates strings 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 username), 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 cannot 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.