cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Community Member

How to change number format in Excel for a Manual SIS Import?

Hello,
As I work in Canvas the challenge I am running into is getting the Start and End dates to be in the YYYY-MM-DD THH:MM:SSZ format in my CSV files. My process of creating the Courses CSV file for a manual SIS Import is to pull a report from our lead management system called Flatbridge that contains the course id, short name, long name, start date, and end date. When I pull the dates from Flatbridge they are in the MM/DD/YYYY format. To change the date format I created a Custom Number Format in Excel that allows me to use the required format listed above.
 
When I save the spreadsheet none of the adjustments I made to the start and end date column previously is saved and therefore I am not able to import the CSV file into Canvas successfully.
 
Does anyone have recommendations on how I can get the start and end dates to remain in the required format in Excel?
sis import‌ manual sis imports sis imports@
Chelsea
0 Kudos
3 Replies
Highlighted
Community Member

The only way I've gotten the date/time format to play nice is to start with a report exported out of Canvas (such as a Courses CSV provisioning report), and then copy a date and paste it into the file you're planning to import. Of course, doing that is overwriting the data you have from Flatbridge with whatever arbitrary timestamp you copied. So you have to edit it to match the correct date. If you have a file with the same start/end dates, it's easy to tweak it once and copy the rest of the cells. But if you have a file with lots of different start and end dates, this isn't a great workaround.

Highlighted
Instructure
Instructure

Hello, phil0150@stthomas.edu‌!

I wanted to reach out and see how things are going with your date formatting/manual SIS import. It looks like tdelillo@alamo.edu‌ gave you some things to try on how to get the date/time to format correctly for you - did that work for you?

I've gone ahead and shared this with https://community.canvaslms.com/community/answers/sis?sr=search&searchId=1bfbdcb9-224d-4b4d-a9fc-358...‌, https://community.canvaslms.com/community/answers/sis/k12-sis?sr=search&searchId=1bfbdcb9-224d-4b4d-...‌, and https://community.canvaslms.com/community/answers/sis/higher-education-sis?sr=search&searchId=1bfbdc...‌ to try and get some additional eyes on your question. For the time being given the time between your question and now, I am going to mark this question as "Assumed Answered". If you'd kindly update us on where things stand we'd love to hear from you!

Highlighted
Navigator

phil0150@stthomas.edu,

 

I must be missing something. I don't have a CSV file created by another program with the wrong dates, so I made my own CSV file with a date of 09/25/2017. When I opened it in Excel, it came through as midnight on that date.


Then I went in an set a custom date format of yyyy-mm-ddThh:mm:ssZ and saved the CSV file. When I opened it up, it kept the right date. The only thing I noticed different in yours was probably just a typo, but you had a space between the date and the T in the time and there is none. That's why I say I'm probably missing something.

Note that when I reopen the file in Excel, that value is no longer recognized as a date, it's now text.

Also note that the midnight that is assumed by Excel since it was only supplied a date is not accurate because your local timezone is not UTC. If you're in CDT, then it's 5 hours behind UTC, so midnight localtime on 9/25/2017 is actually 2017-09-25T05:00:00Z.

That may not be a solution since I'm unable to duplicate the problem right now.

In the past, I have had problems with certain features not being available when I open a CSV file. There are certain formatting settings (and possibly functions) that it just will not allow when I open an existing CSV file. What I do in cases like that is the reverse of what Tracey recommended. I create a new worksheet and then copy the contents of the CSV file into that empty one and close out the CSV file. Then I can do whatever I want, including setting the formatting on the dates, and save it.

Another possibility may be to import the CSV file into Excel rather than opening it directly. I don't do this myself, so I'm not sure if it would fix the issue or not.

For testing, until you get the process figured out, I would save it first as an Excel file and then as a CSV file. This can also be beneficial in the future because then you can open the Excel version back up and paste in the new data as values only, leaving the existing formatting, which would be in the proper format.