Create UTF-8 encoded CSV files for SIS import from Excel

Document created by Jaap Stelpstra on Apr 29, 2015
Version 1Show Document
  • View in full screen mode

In case your institution doesn’t have any connections to a Student Information System (SIS) and you have to manage enrolments manually, than the SIS import feature could be your best friend. Same goes for creating users when university authentication services are not used for accessing Canvas.

 

Should you use the SIS import feature, than you have to create CSV files. These are plain text files, have a specific structure and need to be UTF-8 encoded without Byte Order Mark (BOM). You don’t necessarily have to understand this, it just needs to be correctly formatted and encoded to work in Canvas or you will get errors. UTF-8 encoding is important for names containing special characters like ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ.

 

To create correctly encoded CSV files for creating users and enrolments, attached Excel template can be very useful. This Excel sheet contains some macro functions (VBA) with which you can create CSV files based on the information you provide in the worksheets. The files are saved in the same location as where the Excel file is saved.

 

Fortunately great documentation is available about the SIS Import Format / CSV Data Formats:

https://canvas.instructure.com/doc/api/file.sis_csv.html

 

Note: the Excel file does not validate the your content, specified in the documentation mentioned above.

 

For creating users we only supply the following fields with data:

user_id, login_id, password, first_name, last_name, sortable_name, email and status.

 

Enter the details, save document and click the button:

Result:

 

In our institution, when creating enrolments we only supply the following fields with data:

course_id, user_id, role and status

 

Enter the details, save document and click the button:

Result:

For this to work, your courses need to have values in the SIS ID (menu Settings,  tab Course details).

Hopefully this Excel file can be beneficial to other institutions. In case you know VBA scripting you might even extend functionality in this Excel workbook and share it also in the Canvas Community.

 

Hint1: if you want to experiment with importing CSV files, try it using your test environment (usually yourinstitution.test.instructure.com)

Hint2: usually Excel blocks macros by default which will be indicated by a message under the ribbon. You have to enable marcos for it to work.

Outcomes