importing grades - why are there gaps in the student data?

[Nick_Riches1]

Hi. I am using the export/import function in grade centre to upload student grades. However, when I do there are gaps in the data which I am having to fill in by hand. I am working on a Mac, but I am taking care to ensure that the .csv file is UTF8 encoded. This OUGHT to work. This is a complex assignment where the grades are calculated by Excel, and being able to upload grades in a more programmatic fashion is important for reducing the risk of error. But the upload just isn't that reliable.

[James]

@Nick_Riches1 

The second or third thing I thought of is that when you import a CSV file, that the import process runs in the background and so it may look like grades haven't been imported, but it's really a case of they haven't been imported yet. How long that takes depends on the size of the course and the number of grades that you're importing.

The first thing I thought of is that I'm not exactly sure what you're talking about. Are you saying that some student grades import correctly while others do not? Or are you saying that some assignments (columns) import correctly while others do not? I've never had a problem with the grade import working as long as I follow the rules.

The CSV being UTF-8 encoding is probably not the issue unless you have international characters in your student names or assignment names. If you do not have any special characters, just save it as a CSV (Comma delimited) type. If there are special characters, then use the CSV UTF-8 (Comma delimited) type.

The UTF-8 may also pop up as the reason if you can identify the students or assignments where the import is failing. If it always happens when a student or assignment has UTF-8 in them, then I would look there. I will also mention that there are different types of UTF-8 byte-order marker (BOM) that might be an issue. Canvas has settings under your Account > Settings > Feature Options (scroll down to find).

The other thing I would suggest is opening up the CSV file that you are trying to send and double checking that everything looks good. Again, look for the obvious, but things that are different for the grades that failed to import that might indicate an issue.

I would also delete any unnecessary columns and see if that helps. If I'm only changing the grade for one assignment, then I would keep the mandatory columns, that assignment column, and delete everything else. If it works when I do this, then it suggest the issue is somewhere else, which isn't even necessary.

With complex calculations, you are probably copying your spreadsheet and pasting as values into a new spreadsheet and then save that as a CSV. This allows you to keep your calculations in one place but still generate the CSV for re-import. The copy/paste probably isn't an issue, but then that brings the proper BOM back into play. The file you downloaded might have it, but the one you copy/pasted into would not.

At this point, I'm grasping at straws, but if your download from Canvas opens in Numbers rather than Excel and then you copy/paste it into Excel, there might be something lost in the transition. I have no evidence to back that up other than when my students use Numbers in my stats class, they run into issues that they don't get if they use Excel. I mention it just as something that might be a potential issue.

A lot of the suggestions here have to do with the file you're sending. Other than a delay if it's a large file, I've not had issues with Canvas importing. But I also had no UTF-8 in the files. It's been a while since I've done a CSV import. I code some, so I write scripts that download the assignment submissions (to get the scores) directly from Canvas, do the calculations, and then update the grades in Canvas. All of that is done without having to download/upload a CSV file. I set that process to run on a server once an hour (sometimes more frequently) and I don't have to mess with it except at the start of a new term when I have to change the IDs for the courses and assignments.

[Nick_Riches1]

Thanks for a marvellously detailed answer, and sorry for slow response. You make a number of very useful suggestions which I will try out. Rather than seeing this as a glitch in Canvas it does sound as if there might be something about .csv file which is causing this. I will take a closer look. BOM is currently set to "off". It doesn't sound as if this is related to the issue, but I will try to fiddle with this setting.

[James]

@Nick_Riches1 

A good rule in debugging an issue is to look for patterns. Do those failed grades have anything in common? Finding that pattern can help you spot what the issue and solution are.

Related to what I wrote before, you might take a CSV import that has partial success. Then go through and delete all of the rows that were successful, leaving just the header and the failed rows.

Try importing the CSV file with just the header and previously failed rows. Does it work? I doubt it will, but if it does, then it may be an issue with Canvas failing to recognize something from the original file. If not, then it's likely issues with the CSV file directly.

Assuming that fails, take out all of the extra columns that aren't used and try sending it. Leave just the required columns and the assignment you're changing. This rules out that it may be something else in the CSV file messing it up.

If you are sending a text grade instead of a numeric score, make sure that the text in Excel matches the text in Canvas. There is a note that "letter grades and GPA scale assignments do not support any entries that are not part of the assignment's grading scheme."

This goes back to the first comment about noticing patterns. If you see that "Need additional work" is what is being rejected, then you check and see in Canvas that it is "Needs additional work". Or maybe you're sending "Awesome" but in Canvas, the grading scheme has "Awsme". I'm not holding out hope here because you likely would have noticed this when you were manually entering grades.

The rejection may be more subtle. Maybe there are a couple of grades that are consistently rejected instead of just one. That is still helpful. I do not know how picky that is, to know whether extra spaces or capitalization matters. Sometimes it's not obvious as people automatically hit space when done with a word (that could be in Canvas or in the Excel file).

You may want to open the CSV file with a text editor, rather than Excel. Look at the grades that are causing problems.  Is there anything unusual about them? For example, are some of the numbers enclosed in quotes for example? If so, that's an oddity that bears investigation.

[Nick_Riches1]

P.S. just to clarify. When I do this, most grades are uploaded. But there are a few odd gaps where grades should have uploaded and they haven't.