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

[stelpstra]

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 enrollments 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.

Update history:

• Version 1.0 original version (Apr 29, 2015)
• Version 1.1 fixed a bug when using the 64-bit version of Excel

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.

[scottdennis]

Thank you, Jaap!

[mnieckoski]

Great post and I’d like to add this suggestion which falls into the, “Don’t make the same mistake I did!” category.

In short: EXCEL automatically strips leading zeros from cell data!  You don’t want to do that with your CSV files!

EXCEL is great for creating and working with the .csv files, but it has one issue that you should beware of…  EXCEL strips any leading zeros from cells!   If you have an ID# or other field that uses one or more zeros as the first character(s), EXCEL will “help you out” and remove it from your ID#, when in fact, it’s no help at all and can seriously mess with your data. 

A couple of things to know:

1. If you are going to open an existing .csv file in EXCEL, don’t double-click on it, but instead open it from within EXCEL: 
   1. Open EXCEL\Go to the DATA tab\click, ”Get external data from Text.”  
   2. This will put you into the Import Wizard.  The Wizard will ask you 5 questions, no 3 questions ;-): is this a delimited file? Yes; NEXT, what is the delimiter? Comma; NEXT, Then click on any column in the wizard that has a leading zero and format the column as TEXT. 
   3. Click Finish. 
2. If you are going to be entering data into an open EXCEL spreadsheet that might include leading zeros in one or more cells, be certain to format the column(s) as TEXT before trying to enter any data. 
   1. In EXCEL, Go to the Home tab, Highlight the cells or column(s) you want to convert to text, go to the Number group and pull down the menu to format the column as TEXT. Another way of doing the same thing is, after highlighting the column you can right-click on the column header, open the Format Cells box, click TEXT. 

Your EXCEL spreadsheet will show an error message in the cell(s) and will tell you that you have a number in a text field, that’s OK, that’s what we want! When you Save As… a .csv file, it will save all your leading zeros. 

Zeros are important as place holders as we learned in school, and in this case, the Id# 567 is NOT the same as 00567 because of the zeros.

--mike

Michael Nieckoski

Canvas Administrator

Keene State College

Keene, New Hampshire, USA

[Robbie_Grant]

Michael,

Excel will also round up after decimal points, which I found out the hard way.  All of our SIS IDs have the Banner term and Course Reference  Number in the following format:  201510.12345.  Excel automatically rounds up after the third place so if you have 201510.12567, it changes it to 201510.126.  Really annoying.

Robbie

[jasond]

Awesome job!

[valla020]

This template did not work for me.  Really having trouble getting a csv that works for import!

[stelpstra]

Hi  @valla020 ‌, I'm not sure what you mean with your message as you don't specify any details. If you want any help, please specify enough details of what you do and run into so I can understand a bit of context. Did you look into this documentation SIS Import Format Documentation - Canvas LMS REST API Documentation ?

[valla020]

Sorry for the lack of details.  I was super frustrated!

When I click on the Generate Users CSV file I get a runtime error 3004.  Cannot write to file.  When I click on Debug - this line is highlighted in yellow:   BinaryStream.SaveToFile myFile, adSaveCreateOverWrite

I know little about VB code and macros so have no idea how to fix that?

[a_craik]

Hi Kelly

If you're using a recent version of Excel then you may be able to just save your CSVs without using this template any more - check if you can choose “Save As” > “CSV UTF-8”.

BW

Adam

[valla020]

I can but those fail the import too.  Error message says cannot find the Canvas headers.  The only thing that works is if I open the csv file from your SIS import how to in google, save it as a google sheet, enter all the data in that and download that file as a csv.  Then the import works. What is frustrating me is that I have a hard time with copy and paste into the google sheet if I already have the data in an excel file.

[stelpstra]

Hi  @valla020 ‌, I didn't use the Excel tool in years and at the moment I do not have access to csv import any more, so therefore I cannot test it myself. One thing I did find is that this message is triggered when using this workbook on a 64-bit computer:

That I could fix by following these instructions: The code in this project must be updated for use on 64-bit systems - Office | Microsoft Docs 

I created a new version (1.1) with this modification and on my O365 Excel 64-bit version it generated the csv without any issues. So I hope it also solves your issue. I attached version 1.1 to this document.

Important: if you are copy and pasting data to the Excel workbook, make sure you paste values only:

Other than that, what  @a_craik ‌ is saying is also right, it should work too if you save it in the UTF-8 format.

In your other post you mention you get a message headers are missing, you get that is this information is missing:

If none of these options work I would suggest you send details of your issue to support@instructure.com and attach the csv file so they can analyse what is cause is.