[ARCHIVED] Special characters in CSV export from gradebook

[baxl]

I recently had to fix a question for a teacher in an exam that was a duplicate question.  The problem is that the question type is "Matching" which doesn't trigger a regrade.  Over 200 students took this exam and the teacher wants to award full points to those students.  To give each student the extra 15 points, I'd either have to manually do it or export the gradebook.  I exported the gradebook and created a macro to give each student that took the exam an extra 15 points, but I can't import it back to the gradebook because of the special characters in the students' names.  These are international students that have accented characters in their name and after exporting the gradebook, the accents or tildes are changed to symbols or special characters.   I could go through the entire spreadsheet and find the students with special characters or symbols, but there are over 200 students in the course.  I can't do a find and replace, e.g., find ¢ and replace with ˜  or ` because the accents and tildes over a letter aren't always being converted to the same special character.

Has anyone else run into this and if so, do you have any tips?  

Also, here's the macro I created that prompts for a number to add to selected cells if anyone else would like to use it. In the input box, the default number is set as 0, but you can overwrite that with any number (whole or decimal, positive or negative).   

Sub AddNumberPrompt()
Dim ws As Worksheet
Dim rngSel As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Dim strPrompt As String
Set rngSel = Selection
lRows = rngSel.Rows.Count
lCols = rngSel.Columns.Count
strPrompt = "Enter number to add to selected cells"

On Error Resume Next
Num = InputBox(strPrompt, "Number to Add", 0)

If Num <> 0 Then
   If rngSel.Count = 1 Then
      rngSel = rngSel + Num
   Else
      Arr = rngSel
      For i = 1 To lRows
         For j = 1 To lCols
            Arr(i, j) = Arr(i, j) + Num
         Next j
      Next i
      rngSel.Value = Arr
   End If
End If

End Sub‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thanks!

[ColinMurtaugh]

Hi Lisa --

What software are you using to open/modify the file once you've downloaded it?  It should be possible to export/import gradebook CSV files containing accented characters as long as all of the software involved is handling the file encoding properly.  The CSV file that you download from Canvas is UTF-8 encoded, so you'll need to make sure that when you open the file locally you're handling it as UTF-8 data. 

When you see accented characters changed into strange symbols/characters, that usually means that there's a mismatch between how the data was encoded (when the file was written) and how it was decoded (when it was read), and the fix is to make sure that all parts of the system are using the same encoding standard. 

Hope this helps!

--Colin

[orjo]

Hi Lisa

I can relate to the csv-problem with all the special characters in Norwegian.

When opening the csv-file directly in Excel, the default encoding will be used - for my Norwegian Excel that would be 1252: Western Europe (Windows) while the export from Canvas is 65001: Unicode UTF-8 as Colin says.

so what I do is:

1) download the csv-file to a local drive/folder

2) Open Excel

3) Go to the DATA tab on the menu

4) In Excel 2013, click the «From Text» icon in the «Get external Data» area.

5) This gives you the Import Text wizard.

6) In the File origin drop down, you pick «65001: Unicode (UTF-8)

In excel 2016 you will have the same menu option, even if the wizard looks different.

Kind Regards

Ørn (that is O with a slash)

[baxl]

Thank you so much for your responses!  I'm going to test this out.  

[Robbie_Grant]

 @baxl ,

We are giving the Canvas Admins area a little bit of love (especially questions that are really, really old) and just want to check in with you.  This will also bring this question new attention. 

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