SIS Imports - Show Robust Errors and Human-Friendly logs

[millerjm]

This is based on idea posted by Chris Sweets that got 52 votes and 13 comments:  Robust Errors for SIS Import

Problem:  The SIS Import History is not in a Human-Readable Format

For all SIS Imports, even if 100% successful, the following data needs to be included: 

• Upload Time
• Begin Time
• End Time
• Import completion time (in hours/minutes/seconds) - not currently listed
• Number of records imported
• Imported Items (Courses, sections, users, enrollments, etc)
• Status:  Completed, Imported with Messages, Failed, etc.
• Reformat logs for previous SIS Imports into a human-readable format.
• Include a link to the error logs for previous SIS Import batches

Problem:  There is no easy way to see details about SIS Import Errors - information is vague

Example: We had a problem with our users file.  It has >30,000 rows and we had a student with a quote in her name "Denise", which caused the file to fail and not even load.  We would get a "malformed CSV" error.  This was REALLY hard to find in our file and took probably 5 hours of trial and error to find the line with the error since we didn't know which line or what could be causing the malformed error.

Example: Problem file that kept uploading via our automated process and we didn't realize that it was causing all of the other files to back up and we ended up having to call and have an engineer cancel all of our SIS imports because we had probably 20 files in the queue.  It was a mess because we have no way of canceling on our side, and even support and our CSM can't do it.  It took several hours for the engineer to cancel everything in the queue.

Example From Marissa Zelmanowicz

I feel this feature is MUCH needed, as we frequently get long lists of 60+ errors stating "canvas_enrollments.csv - An enrollment listed a section and a course that are unrelated" and have no idea what course and enrollment it is referencing. Each time this error appears, hours upon hours of digging is required to try to fix the issue. Since we have thousands of entries coming from our SIS each night, this is a very time consuming and frustrating process.

If an error is thrown, the following data in a READABLE, non JSON format would go a long way to helping admins figure out the problem and get us back to being productive. 

• File Name of CSV File that had error
• Type of import (courses, sections, users, enrollments, etc)
• The line number of the data file that caused the error in the .csv should be included.
• Field with Error (if possible)
• Type of Error (invalid character is the one we usually run into)
• Status:  Did it skip the line and move on or did it prevent any further records from importing?

Would be nice: 

• Add automated email options for errors and warning.  This will allow admins to keep an eye on automated imports.
• Have the ability to email the warnings that happen during an import to an admin.  Especially when these imports are automated and/or take hours, it's important to know about any errors.
• Ability to search for imports from a certain date or date range or Job ID #
  
• Ability to see all past history without having to do an API call

Format the error logs for previous SIS Import batches in a readable format.  We call the log page the "ugly page" at Seminole. 
PLEASE have the import status of prior SIS imports available in a HUMAN-READABLE format!

Thank you!

[millerjm]

Another VERY useful piece of information would be for the COURSES file import process.

If the account/subaccount does not exist and the course is added to the default/root/institution account because of it, a warning would be very useful because then I know that we have a brand new course and could make a subaccount and move the courses into it, instead of finding out later after running a provisioning report and I have 100 courses that were put into incorrect accounts.  

[RobDitto]

I believe Deactivated user‌ succeeded Linda Feng as the Product Team manager overseeing Data & Analytics.

[lars_vemund_sol]

As a part of error searching we very often have the need to run a new file/set of files. Currently we have to wait for the next automatic import.

Any chance to make a button "Trigger automatic import" ?(maybee not the best name )

[yves_schelpe]

I have recently been implementing an integration for SIS Import CSV to CANVAS, and had to go through all of these stages as well.

For starters it would be helpful if the DOCUMENTATION on SIS Import Format Documentation - Canvas LMS REST API Documentation  would be UPDATED with these facts:

 01. that there are illegal characters such as "/() 
       (there might be more illegal characters, but I haven't come across them so far...

 02. that the UTF-8 encoding cannot include the Byte Order Mark (in short "BOM", see Byte order mark - Wikipedia)

Thanks,

Yves

[yves_schelpe]

Correction, ()  are allowed, I just tested this. But then in your SIS import CSV you have to make sure they're between double quotes; e.g.:

  this is ok > "(Full)coursename","(Short)coursename"

  this will be malformed > (Full)coursename,(Short)coursename

What I did, as general rule is

 - strip characters \ and /

 - strip double quotes when the amount of double quotes is odd (or you could try to escape them with placing "" in stead of ")

 - place around each value double quotes, unless the value is empty, then leave out the double quotes

[Renee_Carney]

 @millerjm ‌ & All

The recent beta release introduces a new "SIS Import Errors API" which may be helpful.  I do recognize that this does not meet the desire to have a front end (human friendly) solution.

[Renee_Carney]

If you are interested in the "SIS Import Errors API", you can find more information about it in the https://community.canvaslms.com/docs/DOC-14438-canvas-production-release-notes-2018-04-21 

[oweng]

I was able to finish building a tool to display the errors using the API. 

[tross]

This looks great.  Are you willing to share?

[millerjm]

Can you please share???  That is awesome!

[s528180]

Here's our implementation:

Left side: canvas services status

Center: quick look for SIS imports, you can click on warning icon to view details warnings. Also download daily logs by clicking download button as a CSV

Right: Canvas calendar 

I am not sure if I can share this as I no longer work at the university. You can contact the university for further info

Contact:

Learning & Teaching Center – powered by CITE
B. D. Owens Library 246 | 660.562.1532 | ltcite@nwmissouri.edu

Thanks,

Monish

[millerjm]

Is there any update on the status of this?  I just spent an hour trying to figure out why my users file was failing with the vague error of "malformed CSV"

processing_errors": [
[
"attachment_393772720180803-8443-ehv4gf.csv",
"Malformed CSV"
]

After an hour of testing, I finally figured out that a student had been keyed into the SIS with a quotation mark instead of an apostropohe in her name.  The file had 30,000 lines in it.  

Can we please get an update on where this may be on the priority list since they just did a lot of work on the SIS Import Refactoring?  Or possibly a list of invalid characters so that we can run some validation prior to uploading our files?  

Thank you!

[Renee_Carney]

The Radar idea stage has been removed from the Feature Idea Process.  You can read more about why in the blog post Adaptation: Feature Idea Process Changes.

This change will only impact the stage sort of this idea and will not change how it is voted on or how it is considered during prioritization activities.  This change will streamline the list of ideas 'open for voting', making it easier for you to see the true top voted ideas in one sort, here.

[millerjm]

 @Renee_Carney ‌, would it be possible to get an update on this from the SIS team?  Or at least an answer to my question related to invalid characters so that we can write a validation process?  I've been trying to get a list of invalid characters since implementation to avoid problems that can completely break SIS Imports.  

Thank you!

Joni

[Renee_Carney]

 @millerjm  

I have not had an update on this idea, sorry.  I will pass on your question and see if we can get an answer that helps.

[cms_hickss]

Hey  @millerjm , not sure if this helps or not... but I did find this a while back on one of the SIS pages when I was looking for something else...

• Fields that contain double-quotes must also be surrounded by double-quotes, with the internal double-quotes doubled. Example: Chevy "The Man" Chase would be included in the CSV as "Chevy ""The Man"" Chase".

This of course means you would need to write something to catch the use of double quotes. 

[millerjm]

Thanks Susan!  I'm looking at doing a script for this - our CSV files aren't quoted so it would just be looking for those as well as the others we discover along the way that aren't allowed...it would be nice to have a list though because we find some that aren't allowed in certain fields even though they are UTF-8 characters. 

[yves_schelpe]

I feel there have been numerous amounts of requests - is there a status update on this.

Error messages stay cryptic and this makes creating automated systems extremely difficult due to them not being able to respond to these messages - or - we as developers can't diagnose the errors ourselves and have to rely on support cases.

[millerjm]

Today I got this error for a users file.  Complete failure and the data was not imported at all. 

Error while importing CSV. Please contact support. (Error report 45090000006448363)

I contacted support and was initially told "it looks like it should be a one time error, would you mind trying again"

I asked what caused it? "undefined method `update_record' for nil:NilClass"

Finally when pressed for more information: "our engineering team have described that this issue was caused by a failure in the service while creating the user....Our engineers have to look into our logs for specific information to determine it. I found an old ticket with the same error with the information. As it is a one time error, you should not be running into it again."

Can we PLEASE get an update on when there will be improvements made to the SIS Import Error Messages?

Thank you,

Joni

[tross]

Agreed.  It just takes so long to get these resolved through support when we would much rather just know the issue and move on.