After exporting grade CSV file, excel won't restore the rows & columns properly

[PeterFimmers]

I need a excel file from the grades in Canvas.

Copying manually is not possible due to large number of students.

Exporting works but after loading CSV to excel the file is not organize well and unusable.

Think the separation of data is not done propperly by Canvas or conversion by MS is mismatching.

Can someone help me generating the right excel file with correct rows & columns

thx

Peter

[JeffCampbell · Accepted solution]

I tried to see if there is a setting on my end to change but did not find it right away. Here is the easy fix for what you already have:

1. When looking at your file, the first row (column headings) seems to be properly sorted into columns. It is all of the other rows that are not. Starting with cell A2, select all of the cells in Column A that have data. Go to the Data menu in your Ribbon and find the option for Text to Columns. Select that. Make sure the option for Delimited is selected, then click Next. On that next window, change the setting to Comma (and turn off any others). Then select Finish. This will sort your data out for you. Make sure to say Yes when asks if you want to replace the existed data. I see your decimals show up as commas. Do not worry about that. It appears to be properly sorting.

I thought I had found something that suddenly caused your file to open correctly, but I am unable to recreate it. I tried downloading a CSV of my gradebook and it properly imported. Somehow, the file you are getting has extra quotation marks in the file (when opened in Notepad). This is causing the data to be interpreted incorrectly by Excel. It would require significantly more time to correct this manually than if you just use the steps above to let Excel do the work. Without seeing/interacting with your gradebook to run some tests, it is hard to say what is causing the extra characters. You might try deleting the file then exporting it again. See if it works that time. 

By the way, learning how to split text into columns is useful beyond this situation. I've done a copy/paste on stuff from sources that do not give me an export option. Then just use the text splitter to sort it back out. It has a nice preview window so you can try different elements to split the text.

As a side note, go ahead and remove the attached file. It contains personal information about your students. - This was removed by Canvas to protect the data. It is protected data. In the future, rather than sharing that kind of file in the post, do it via DM with someone helping you. 

[JeffCampbell]

By the way, let's say you do delete the file and re-export the data and the problem remains. You could submit a Support Ticket to Canvas using the Help menu on the far left side menu. Someone on their end might be able to look at your stuff and see what might be causing it to export that way. If it is something you are doing, they could advise you on how to fix. If it is something on their end, this will alert them to a potential bug in the system to investigate.