[ARCHIVED] New issue with using Excel for CSV grades import?

[a_craik]

We've started having some real problems with importing Grades CSVs back into the Grades screen when they've been updated/saved using MS Excel (i.e. the file is not recognised).

It seems that by clicking save, the encoding of the CSV file changes from UTF-8 to ANSI. Having spent over an hour reading around this (and getting a headache), it's time to ask the admins!

• Have you had any similar problems recently?
• Is ANSI even your default encoding (as I understand this could be a regional Windows settings thing)?
• Have you always advised teachers on the need to follow certain steps to ensure the encoding is retained (like saving tricks... or even rinsing files through Google Drive!)?
• If ANSI is the Excel default... would that be a more accessible encoding for all???

Any advice or sharing of similar pains would be greatly appreciated 

Cheers

Adam

[bneporadny]

Hey  @a_craik ,

I haven't heard of any issues at my institution but I am also going to share this over with the Canvas Developers‌ to see if any of them have had a similar issue to yours.

Brian

[a_craik]

Thanks Brian! So at your institution, your teachers are able to use excel for updating the Grades csv and import back without any special steps required?

[bneporadny]

Hey  @a_craik ,

None of them are currently using that at my institution, but we have gone through the steps in out Test environment so if an Instructor does have a need to do it that we would have at least tried it out and gone through the motion. 

There were no special steps required that I can remember from when we tested it.

I did fine this thread on the community that I think may help you. #https://community.canvaslms.com/thread/15960-problem-importing-grades 

Brian

[a_craik]

Thanks again Brian - the (current) last comment from Canvas Support in that post is insightful, particularly this:

"This is behavior we've been seeing since the most recent update to Microsoft Office"

I would imagine that most institutions are using MS Office, and I think we've all got better at keeping up to date with new versions/releases (because it's got easier), so I was surprised that this wasn't already a hot topic in the community. It makes me wonder if 1. It's not been a global update rolled out to all Office versions, or 2. maybe the CSV Grade import function isn't widely used? I have also posted in Technet.microsoft.com, so maybe there'll be some clarification re no.1 there...

[a_craik]

Just updating further to the response I've had on Technet.microsoft.com (which you can find here) - this is what I've got so far:

Hi,

Excel opens in ANSI format and does not do code recognition, this is by design behavior. 

If you want to open UTF-8 csv directly in Excel, I suggest you add BOM to UTF-8 files first.

Canvas stipulates CSV files must be UTF-8 encoded without BOM (see here - although it's written about files for SIS import I believe it stands for Grades CSVs too).

The investigation continues...

[dgilogley]

I've found that the best way is when saving as CSV is to choose "MS-DOS CSV format" and Canvas has always accepted it.

[a_craik]

Thanks Daniel - we've tested the MS-DOS CSV format and that does indeed seem to work! We're nearly in a position now where we can advise all staff on a revised approach to saving and importing Grades CSVs 

I'll post another update separately as I have responses from both Canvas and Microsoft that may be of value to the community

[a_craik]

Ok, so it turns out MS-DOS CSVs will only work providing none of your students have any special characters in their names, so for us an "often... but not always" fix :smileycry:. Here's a round up of things as-is from the different stakeholders:

Microsoft (online here😞

I find if you want to save CSV as UTF-8 encoding your Office build version must equal to or later than 16.0.7466.2023.

Canvas Support (email quoted):

This behavior has been reported and escalated to our Engineering team. They are currently researching this behavior. The post from the Community is indeed a Canvas Support response [the last one from problem importing grades], and the information in the response is valid - this behavior started to occur after the update mentioned. The workarounds mentioned are also the current best options at this time. 

I have attached this case to the tracker associated with the Engineering ticket, as that tracker is updated this case will be updated and an email will be sent to notify you. 

Me (sharing update with colleagues):

So to recap on where we are now in this messy CSV business:

Exporting a Grades file, opening in Excel, and…

ACTION:	WILL RESULT IN:
1. Clicking “Save”	Special characters remain intact, Field separations are broken (all fields compacted into one)
2. Choosing “Save As” > “CSV MS-DOS”	Special characters broken, Field separations are intact
3. Choosing “Save As” > “CSV UTF-8” (only available in recent build of Excel)	Both intact – this will upload back into Canvas successfully

• Action 3 won’t work if action 1 has already been performed
• Action 3 is only available to users with versions of Excel equal to or greater than 16.0.7466.2023

Will update again with more as it comes in - Happy Friday everyone!

[buddyhall]

I primarily use a Mac and Excel updated this morning. Now, I can Save As UTF-8 CSV and it works. Last week, that option still resulted in errors. I, however, can't seem to get Excel for Windows to update to a version that has this option. I seem to be stuck at 16.0.4639.1000.

[buddyhall]

My colleague who handles our Microsoft licenses says that they have confirmed with Microsoft that UTF-8 encoding support will only be offered in the Office 365 offering going forward. This is apparently a different product than the volume licensed Office 2016 version that most of our faculty have installed. If you remove the volume licensed MSI Office 2016 and install Office 2016 from the Office 365 website you gain the ability to save as UTF-8 CSV on Windows that I have on the Mac version. If you just save it, it will still convert it to ANSI encoding and this is apparently an intentional design decision (even if it is a really bad one), so you will have to remember to Save As UTF-8 CSV but that works.

tldr: So, I guess for now, our options are uninstall Office 2016 and install the Office 365 version of Office 2016 and remember to always "Save As" UTF-8 CSV, use Google Sheets, or use OpenOffice or LibreOffice.

[kmatson]

Yes we are seeing some of this behavior at the University of Oregon. In one case three characters were added to cell A1 in front of the word Student. Easy to clean that one up and re-import but we shouldn't have to and would like to get to the cause so we don't have to do work-arounds and look for what changed in the file.

Karen Matson

[cwhermsen]

Hi  @a_craik ‌ , we have this issue as well. Exporting and importing grades is a major hassle for us, not just due to having to convert MS Excel's ANSI coding to UTF-8, but also because of limitations in the .csv file format. As a tool to export and import grades, Canvas should use a fixed and stable file format, but .csv instead supports both comma's and semicolons as the field delimiters, and allows working with or without quotation marks. 

To complicate matters, Canvas requires comma's as the field delimiter, which causes problems importing grades in to Excel when you have students enrolled in three or more sections (the .csv export lists all Sections a student is present in, and includes a comma for three or more sections). Sometimes teachers use comma's in assignment names as well, further complicating importing grades into Excel, let alone the fact that the Dutch decimal system uses a comma as the decimal delimiter.

I am definitely interested in how other universities handle exporting and importing grades, especially for courses with hundreds or more students. An Excel export/import tool would definitely be an improvement!

[a_craik]

Hey  @cwhermsen ‌

The development of an Excel (specifically for non-O365) export/import tool would be a wonderful solution!

 @buddyhall ‌ has outlined the current options available in his post above (thanks for that BTW Buddy!), but google sheets is no longer an option for us in the UK due to new GDPR data compliance requirements. I'll add that we're currently looking at going down the "save as MS-DOS format" option, dealing with special characters by simply deleting the data in student names (which actually makes things quite neat for us as we need marking to be anonymous anyway).

So... this export/import tool - is it worth putting together a feature request do we think? I think many institutions are/will be looking to move to O365 (which solves the problem), so doing this could be considered a stop-gap measure. On the other hand, who knows how long volume licensed Office will remain prevalent? Can we make a strong enough case with enough support? Questions questions

[Robbie_Grant]

 @a_craik ,

Were you able to find an answer to your question? I am going to go ahead and mark this question as answered because there hasn't been any more activity in a while so I assume that you have the information that you need. If you still have a question about this or if you have information that you would like to share with the community, by all means, please do come back and leave a comment.  Also, if this question has been answered by one of the previous replies, please feel free to mark that answer as correct.

Robbie