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

Leading Zeros CSV SIS Imports

Jump to solution

I am working with our SIS, Tyler SIS, and the data we pull comes with leading zeros for our teacher IDs. After I export it, manipulate it in Excel or Google Sheets, then create the CSV file and re-open that CSV file I find that the user_id leading zeros are missing. This is very annoying.

I found a solution by creating the CSV file and never opening it but go straight to importing it into Canvas. It will work.

I am asking if there is a better solution than what I am doing?

1 Solution

Accepted Solutions
Highlighted
Navigator

Two things come to mind. The second is probably better.

  1. Format the ID column as TEXT rather than numeric. Then it will keep all the 0's. You might (I haven't tested) be able to do this in the CSV itself by putting quotes around the values "0000009". The problem with this is that it is no longer numeric. Most of the time, that won't matter; but if you needed to do calculations on the ID itself, Excel won't recognize it as a number unless you take it's value().
  2. Use the custom formatting to leave it as a number, but format it with extra zeros. This has all the benefits of having a number and having it look right, but it's something you'll need to do each time you open the CSV and the save it again.

Here's how to do it in Excel:

  1. Highlight all of the cells containing the IDs
  2. Right click on any of those cells.
  3. Select Format Cells
  4. Scroll down to Custom
  5. You'll see Sample, followed by Type, followed by a list of formats. In the Type box, type 00000000 (with as many digits as are in your IDs)

In Google Spreadsheets, the process is similar. You can find the custom format through the menu: Format > Number > More Formats > Custom number formats

View solution in original post

3 Replies
Highlighted
Navigator

Two things come to mind. The second is probably better.

  1. Format the ID column as TEXT rather than numeric. Then it will keep all the 0's. You might (I haven't tested) be able to do this in the CSV itself by putting quotes around the values "0000009". The problem with this is that it is no longer numeric. Most of the time, that won't matter; but if you needed to do calculations on the ID itself, Excel won't recognize it as a number unless you take it's value().
  2. Use the custom formatting to leave it as a number, but format it with extra zeros. This has all the benefits of having a number and having it look right, but it's something you'll need to do each time you open the CSV and the save it again.

Here's how to do it in Excel:

  1. Highlight all of the cells containing the IDs
  2. Right click on any of those cells.
  3. Select Format Cells
  4. Scroll down to Custom
  5. You'll see Sample, followed by Type, followed by a list of formats. In the Type box, type 00000000 (with as many digits as are in your IDs)

In Google Spreadsheets, the process is similar. You can find the custom format through the menu: Format > Number > More Formats > Custom number formats

View solution in original post

Highlighted
Community Advocate
Community Advocate

I appreciate the quick response. From what you described it sounds like formatting the cells is the right way to go. However, it looks like my SIS is going to be changing from the 10 digit codes to any number for IDs which mean I won't have to retain the zeros. Should mean less effort on massaging the data.

Highlighted

Brrad,

Just an additional thought.   You can also use a text editor like Notepad++ which is free.  This will keep the leading zeroes, but depending on the work you want to do Notepad++ may not work for you.

I also find that Excel loses this formatting when the file is opened again so you have to reformat the cells.