Import Opscan Test Results to the Moodle Gradebook [1]
This page explains how to take test data from the Opscan office and import scores into the Moodle gradebook.
- Note: Teaching assistants do not have permission to use this function by default. See TA - manage gradebook [2]for instructions on how to allow TAs to import grades into Moodle.
Prepare an Opscan File for Import to Moodle
- After the Opscan office processes your Scantron sheets, they will send you an email with several files attached containing the data for your exam. In a spreadsheet application, open the Excel file. The file will include a row for each student who took the exam, and columns including the exam date, the instructor’s name, student answers for each question, and student names, IDs, and total grade for the exam.
- Note: To match data to the correct student during an upload, it is critical to have accurate data in the student ID column in your upload file. Be aware that it is common for students to enter an incorrect ID number. If IDs do not match what's in Moodle, or if there are duplicate Student IDs, import will fail and Moodle will provide an error report listing mismatched IDs to correct.
- To import the exam total grades into Moodle, the only columns you need from this file are:
- Student ID numbers (column AI)
- Student names (column AJ)
- Total grades (column AQ)
- The data you need is always in the same columns (AI, AJ, AQ). Delete columns AK through AP (column AQ will be renamed AK).
- Select the values in columns AI, AJ and AK (previously AQ), and copy the contents of the selected cells.
Note: Do not select the entire columns by shift-clicking the columns names, rather, drag across the cells to select only the data in the cells.
[3] (click to enlarge) - Open a new workbook and paste the cells into the new sheet.
- Add a row at the top of the sheet and label the headers for each column. You should label the column with your students' ID numbers "ID Number," student names as "Student Name," and the grades as an easily identifiable description such as "Exam", "Exam 1" or "Mid-Term Exam"
Note: Do not use punctuation in the column headers.
[4] (click to enlarge) - Save the file as a Comma Separated Values (*.csv) file. Your file is now ready for importing into Moodle.
Import the File to the Moodle Gradebook
- Open your course Gradebook in Moodle and click the Import Tab. The Import: CSV File screen will open.
- Note: By default, Moodle will import a Comma Separated Values (.csv) or Tab Delimited Text (.txt) file (you can change the file format by looking below the Import tab and clicking XML file).
- In the Import file area, drag and drop a file from your desktop or click Choose a file.... The File Picker will open. Use the File picker to select a file from your computer. Once your file is uploaded, the file name will be displayed.
- On the Import CSV screen, for Separator:
- If uploading a Comma Separated Values (.csv) file, leave the option set to Comma.
- If uploading a Tab Delimited Text (.txt) file, change the option to Tab.
- Leave other settings as default. Click Upload Grades. If the upload is successful, the Import preview screen will open.
- In the Import preview area, review the sample grid and verify the upload data.
Note: Data should appear in columns with headers above each column. If data not displayed in a table with columns and rows, this indicates a problem with Moodle reading the file. Make sure the file is saved in the correct format and then repeat the upload. - The Identify user by section is where you match the unique identifiers in your spreadsheet to students in Moodle.
- We strongly recommend you match users by their Student ID numbers, use the Map from drop-down menu to select ID Number, and from the Map to drop-down menu, select useridnumber.
Note: If your spreadsheet does not have a column named ID Numbers, select the column name which contains ID numbers for your students.
- We strongly recommend you match users by their Student ID numbers, use the Map from drop-down menu to select ID Number, and from the Map to drop-down menu, select useridnumber.
- The Grade item mappings section is where you match columns in your spreadsheet to grade items in Moodle. The column headings from your imported file will be listed at left, with a drop-down menu (to the right) listing existing grade items in the course gradebook.
- To import a column to an existing Grade item, select the Grade item name from the drop-down.
Note: This will overwrite existing data for each student in your spreadsheet. - To add a new Grade item, select New grade item.
Note: Each New grade item created in this manner will have a default maximum value of 100 points (If needed, you can adjust the maximum value after import). See Arrange & Customize the Gradebook in Moodle [5]. - To skip a column, and not import its data, leave the drop-down menu set to ignore.
- To import a column to an existing Grade item, select the Grade item name from the drop-down.
- Click Upload grades at the bottom of the screen. The upload will begin. If the upload is successful, you will see a "Success" message.
- Click Continue to return to the Grader report view of your gradebook. Make sure the new data is properly inserted in the appropriate columns.
Errors and Troubleshooting for Grade Import
If the import fails there are several possible error messages and remedies:
- user mapping error, could not find user with username "Name"
You did not properly set Step 6, (Identify user by). Upload and import the file again making sure to match ID Number to useridnumber. - user mapping error, could not find user with ID number "12345678"
Your file contains incorrect student ID numbers. The import process will be canceled and no values imported. Return to your spreadsheet and fix the problem data either by correcting ID numbers or deleting rows from the spreadsheet, then upload and import again. - user mapping error, could not find user with ID number ""
The file contains empty cells, or duplicate students.
Return to your spreadsheet, carefully select only the cells containing data, and copy and paste into a new spreadsheet. Another possible solution for a .csv file is to open the file in a text editor and remove the extra commas at the end of lines, or bottom of the page. Save the corrected file, then upload and import again.