Importing external marks into Canvas

Importing external marks into Canvas

In some cases, you may want to manually import marks from a spreadsheet into the Markbook. Cases may include where there is an external marking tool such as Mark Sense Cards, ExamSoft, eOSCEs etc where final assignment marks must be uploaded to Canvas so they are visible to students and can flow through to the Grade Management System (GMS) to award student’s final grades. For more information, see How do I import grades in the Markbook? (Canvas Guides).

Canvas requires the following column fields and order to be uploaded via a CSV file.

• Student Name
• Student ID
• SIS User ID
• SIS Login ID
• Section
• Assignment IDs for existing assignments

As the above column field data is quite complex, the student data will need to be matched up with the mark data to be imported, and cannot simply be copy/pasted into a spreadsheet and uploaded to Canvas. Therefore, an Excel function called VLOOKUP is required.

To do this you will need to:

1. Get the Canvas spreadsheet - Download the Markbook export CSV to get the above column field headers and data as a template for uploading back into Canvas, once mark data has been added. For guidance, see How do I export grades in the Gradebook? (Canvas Guides).
2. Get the External Mark spreadsheet - Get a copy of the marks you wish to upload. You will need to ensure you have at least the S-number in this spreadsheet to match against the Integration ID (S-number eg s123456) in the Canvas spreadsheet.
3. Use formulas to get marks into the Canvas spreadsheet - Perform the VLOOKUP steps below
4. Import Canvas spreadsheet where marks have been transferred using the VLOOKUP steps.

VLOOKUP Steps

• In the Canvas spreadsheet, select the first cell in the Assignment Mark set column that corresponds with the first Integration ID [1].
• In the cell, enter =VLOOKUP( [2].
• Still in the Canvas spreadsheet, select the first cell containing the Integration ID and enter a comma after the first parameter value [3].
• Navigate back to the External Mark spreadsheet and select the two columns, Integration ID and the Assignment scores column all the way to the bottom (last entry in the spreadsheet) [4].
• In the Canvas CSV spreadsheet, add a comma after the second parameter value.
• Enter the value 2 followed by another comma in the Canvas CSV formula.
• Enter FALSE to find the exact match.
• Close the bracket of the formula and then press enter.
• Drag the formula down to calculate all cells.