Editing Lists Using Excel Files
Due to the large size of some department's list of residents, we came up with an approach to maintain them that is easier for most departments to manage. We have created a system that uses an Excel spreadsheet to hold all the data for the residents and make it easy to update each year. We have since made this format available for other listings.
Some things to keep in mind:
- Photos of residents should all be taken by the Medical Photography department. After they take photos and color-correct them, they are uploaded to the Find A Doc repository for photos and our spreadsheets look for the photos there, using the resident's username. It often can take a few days from when a photo is taken to when it appears on the site, but if it's been a few weeks, feel free to reach out to Photography.
- The fields displayed for each resident can vary by department, but once the spreadsheet is in place and working, the headlines and columns can not be changed or it will break the programming.
- The spreadsheet files must be saved with the same exension as it is on the live site, ala ".xls" should replace an ".xls".
Please contact the Edcomm Web Team to get an Excel resident list in place for your department if you don't already have one.
Steps to adding a new person -- or item
- add their image
- download latest excel sheet
- edit your excel sheet to create your new row
- save as an XLS file
- edit/re-upload excel sheet
- publish excel sheet
1. Add an image
The first thing you should do when adding a new person/item is adding their photo.
- Find the image folder: You can do this by right-clicking on an similar image on the page, and finding out what the URL path is. The path is usually either a "subfolder" inside the site's "images" (eg "images/residents") or to our database of images that is outside your site (eg. upstate.edu/scripts/faculty/thumbs.
- upstate.edu/site-assets/images/portraits/ - if your photos come from this folder, new photos can only be added by the Photography Department. Make sure the person you are adding has had their picture taken by the Photography Dept. Expect a new photo to be added within a week of being shot. If a photo of your new person does not exist, chances are it will be replaced with a "Photo Not Available" image. The image name is the email/ID name at Upstate (eg.
- yoursite:images/subfolder - if the images are found on your site within Cascade, you can add new photos using an available upload asset factory, eg. "New -> Upload -> Upload 135x177 Faculty Photo."
- Name of image must match ID - This needs to match the "ID" or "User ID" column in your excel sheet. if it's a person you are adding, make the image name the same as their email/LDAP user ID. The excel sheet will take the "ID" or "User ID" listed and add look inside a folder for that image. The image names should be lowercase and the type of image should be JPG unless otherwise noted.
If the image is not in the correct folder or correctly named, the image will not show up. Instead, depending on your settings, a default image will show, no image will show, or a broken image icon will show.
2. Download latest excel sheet
Your excel sheet is in the "files" folder inside your Cascade site.
If you have made edits before and have a version of the XLS on your computer:
- Click on the "files" folder - this gives you the names of the files within the "files" folder, including the excel sheet you need to edit. Check the "Last Modified By" column. If your username is listed, you can be confident that the XLS on your computer is the most recent version. Unless, you are disorganized like me, and you could have multiple versions of the XLS on your computer, unsure of which one is the most recent, at which case, download the latest version.
- Click on the ".xls" file you want to edit in Cascade - this will send you to the "View" tab of the file. Your browser may automatically ask if you want to download this file. You can choose "yes" or "save" here. If may even open-automatically or ask you to open, which you can do and edit if you feel confident. Or you can hit "cancel" and move to the next step.
- Click "Download" to download the excel file to your computer.
- Find the downloaded file and open it.
3. Editing the excel sheet
Things you can change in your Excel sheet:
- Rows - you can add/edit/delete rows in your sheet. To add new person/item, you can add them to first blank row in your sheet, or you can create a new row in between your data (Insert -> Rows). Exception: do not edit Row 1 (column headers).
- Sort order - for many excel-based listings on our site, the order of the listing is based on the order of the rows. You are free to change this if you'd like. To sort go to "Data -> Sort" but be sure not to sort Row 1.
- sheets - Do not edit a sheet's name. Do not delete a sheet. Do not create a new sheet and expect it to show up on the site. Ask Websupport to make any of these changes.
- columns - Do not edit a column's name (Row 1). Do not delete a column. Do not create a new column and expect it to show up on the site. Ask Websupport to make any of these changes.
4. Save as
- Click "File -> Save As"
- Change format to "Excel 97-2004 Workbook (.xls)"
- Click "Save"
5. Re-upload to Cascade
- Go to the xls file in "files"
- Click "edit"
- Re-upload excel sheet via "drag and drop" or "choose file..."
- Click "submit"
6. Publish changes
- In the menu for the excel file in Cascade, go to "Publish"
- Optional: test your change on "web" first. Otherwise select "web" and "www".
- Click submit / send to workflow
- Reload the url of the page your listing is on, to see your changes.