Tips for quiz result export?

[g_schriek]

Hello,

Some of our teachers and program coordinators are experiencing problems with the csv when exporting quiz results: when students fill in (or copy text to) essay questions, the results in the csv file seem to become messed up and unreadable. So when they turn the csv into an excel file (via data > to columns) with readable rows and columns, it looks like the file does not show the answers in a correct way (answers in wrong rows/columns, empty cells etc). It seems that the only way is to doublecheck with the results per student on Canvas and then re-arrange them in the excel file by copy/cut and paste...
Are there any tips/tricks or do's / don'ts concerning setting up the quiz with both multiple choice and essay questions or the export file that will make their life a bit easier?

Thanks in advance!

[kblack]

Hi  @g_schriek  - This is an Excel issue more than a Canvas issue, and it occurs because of various technical things that I won't bore you with (and that I likely don't know fully enough, anyway), but I have a suggestion or two.  I was most puzzled by your statement that the answers do not line up with the questions, as that has NEVER been my experience, though my hunch is it's occurring because of the Data>>To columns that your teachers are doing.  Try this instead:

-Open Excel FIRST; do not open the CSV file.  Just open a blank workbook.

-Click (on Windows machines, at least) the DATA tab and then click the "From Text/CSV" button that is under that tab, in the "Get & Transform Data" group.  (Love that group name! Sounds like you're doing magic, which I guess you are in a way.)

-Find your CSV file and open it

-On the ensuing dialog box, in the File Origin drop-down menu select Unicode (ATF-8), which on my machine is labelled as 65001...and likely is on yours, too.

-Click Load, and it will load it into a Data Table format.

-You're not quite done yet; to make the text more readable, on the text-answer columns do WRAP TEXT by clicking the column heading letters.  Also (I find this bit is important...) LEFT-ALIGN them AND TOP-ALIGN them for better readability.

While some extraneous characters may still pop in, that should get rid of the majority of them.

I hope this helps a bit, Gerdien!

[g_schriek]

Thanks Ken, will try this out and see if this works better. I am aware that it is probably not a Canvas issue but an Excel one -  tbh, rather than trying out all kinds of options that are time consuming I thought I ask it here

[James]

 @g_schriek ,

It wasn't an issue for me because U.S. English has limited use of extended characters, but Canvas added some tweaks to allow people to customize their CSV exports for the gradebook. According to the note, it's limited to just the gradebook while you're working on quizzes. When I just checked it with the quizzes export, it didn't make a difference, so this might be more along the lines of things that might need fixed, rather than a quick fix that you can do within Canvas.

It's under the user account settings.

• Include Byte-Order Mark in Gradebook Exports
  Optionally include a byte-order mark in Gradebook exports so they can be imported into Excel for users in some locales.
• Use semicolons to separate fields in Gradebook Exports
  Use semicolons instead of commas to separate fields in Gradebook exports so they can be imported into Excel for users in some locales.
• Autodetect field separators in Gradebook Exports
  Attempt to detect an appropriate field separator in Gradebook exports based on the number format for your language.

Have you tried opening it directly from the operating system's file explorer rather than from within Excel? Have you tried opening as CSV-UTF8 rather than CSV? Here's a dated but probably mostly relevant article about how to do that: How to import a .csv file that uses UTF-8 character encoding 

Maybe someone could write a preprocessor that would take the CSV file, look for malformed entries, and fix them before people open them? That is, you would save it to disk, run the script, and then open it with Excel. That's where the suggestions above probably factor in. That's a hassle to be sure, but it may be able to save opening it by hand.

You can fetch the results of the individual questions through the submissions API by include[]=submission_history. You would need someone to program the code to make that human friendly, but since it is computer friendly, you can handle it without the problems with the CSV. The CSV has other issues as well.

Have you contacted Canvas support so they can look at what is happening? It may not be productive since they're not developing quizzes, but since it's an export, the code to export a CSV might be shared among other things. It's obviously not shared with the gradebook, though

[g_schriek]

Thanks so much James for all the insights. I know about the user account settings, but it is indeed limited to the gradebook and when it was released I can remember having talked about the issue with our csm too.
I suppose it may have something to do with the UTF-8 coding so I will focus on that first and see if I can get any further with the insights from you and Ken. Unfortunately we have no one here that can work on the programming side of things but will give this a go first.

[g_schriek]

Just tried and the routine as Ken describes it works great. Guess it was most in the UTF-8 and working order. Thanks so much Ken and James for the quick replies!

[kblack]

Glad to help a bit, Gerdien. You would do well to follow James here in the Community, though.  His Canvancements - Canvas Enhancements  are amazing, among other things he does.