How to Generate SVG Timetable Images for MVTA.com

Purpose

This procedure describes how to convert route timetables from an Excel file into SVG vector image files for use on MVTA.com.

Route timetables are maintained in Excel for use in pocket schedules (printed and on MVTA.com) as well as for timetable images posted on individual route pages on MVTA.com. However, they must be converted from their Excel format into a vector image format suitable for viewing on the web. This article describes how to perform that conversion using a PowerShell script, Convert-ExcelToSVG.ps1.

Scope

This article describes only how to convert an Excel file of timetables (of the format described later) into SVG images. It does not include how to manage timetables within Excel, how to generate them, or how to post them on the website. Further information on the full service change procedure for MVTA.com can be found in this article.

Responsibilities

Planning Department: Generate timetable images as described in this article, including maintaining the Excel file (including the content and formatting of the timetables), running the script, verifying that the SVG images produced are appropriate, and sharing those SVG image files with Customer Service for inclusion on the website. Also responsible for reporting issues with the script to IT for troubleshooting.

IT Service Desk: Ensure Inkscape is installed on computer(s) that will be running the script. Route tickets received other than Inkscape installation to the IT System Administrator assigned to the MVTA.com website.

IT System Administrators: Troubleshoot script issues, including adapting the script for future needs as communicated by Planning, and provide accurate documentation (like the article you're reading).

Customer Service Department: Post timetable images to the MVTA.com website (as documented in separate article -- see Scope section)

Background

MVTA.com's new individual route pages include a section for timetables. These timetables can be used by riders to see when a route will depart a specific stop and are useful to riders who prefer route tables rather than Trip Planner or similar services.

To ensure timetables can be portrayed cleanly and transferred to the website easily, each timetable is stored as an SVG image. SVG (scalable vector graphics) is an image format that preserves quality regardless of how closely you zoom in. While most image formats describe an image in terms of the precise color at each coordinate in the image, SVG images are instructions on how to draw the image, which means they can adapt perfectly to different zoom levels or screen sizes.

In this procedure, we'll be using a PowerShell script to convert Excel sheets into SVG image files. The purpose of using PowerShell to do this conversion is to (1) enable SVG images to be created, which is not natively possible in Excel, and (2) automate the otherwise laborious process of converting each timetable manually.

The images can then be shared with Customer Service for upload to the website.

Prerequisites

Required programs: The computer running the script must have the following programs installed:

Microsoft Excel
Inkscape (https://inkscape.org/) v1.4 or higher

Script location: The Excel sheet, script, and images are all kept in a folder within the Planning SharePoint called "Timetables (Shared)". As its name implies, this folder has been shared with Planning, Marketing, IT, and Customer Service. Because the folder structure is defined, and the Timetables (Shared) folder is shared among departments, all relevant parties should be able to access timetable images in these folders without needing to email them, copy them to shared drives, share in Teams, etc.

Within this folder, there are folders for each service change, like this:

Timetables (Shared)
- 2024 Fall
  - Copy of script
  - Excel file (exactly one Excel file, no more, no less)
  - Timetable images
- 2024 Winter
  - Copy of script
  - Excel file (exactly one Excel file, no more, no less)
  - Timetable images

Here is the link to the Timetables (Shared) folder:

Timetables (Shared)

Excel file: The Excel file should conform to a few requirements:

Each sheet of the Excel file will be converted into its own SVG timetable image, so each sheet in the file should contain exactly one timetable image.
Everything on the sheet will be included in the image, so avoid adding text or formatting outside of the timetable, unless you want it included in the SVG image.
Use sheet names that are concise, descriptive, and consistent (ex: 499 WB MF) since SVG images are named based on the Excel sheet names.
The exact formatting of the Excel sheet will be preserved in the SVG image, so make sure formatting (colors, left/center/right justification, etc.) is how you want it to appear on the website.
Some changes to the Excel file formatting (such as using a different font or changing the overall size of all of the text) may require additional configuration in the script. If such changes need to be made, run the script and observe the output SVG images; if they do not display as expected, reach out to IT via ticket for assistance.

Procedure

Within the Timetables (Shared) folder, set up the following:
- A folder for the service change (ex: 2024 Fall)
- Within that folder, place a copy of the script file Convert-ExcelToSVG.ps1 (either from the most recent service change folder, or download the copy attached to this guide)
- Within that folder, make sure your Excel timetables file is the one-and-only Excel file in the folder.
Close the Excel timetable file, which no one can have open when the script runs (or it will give an error and not work)
Right-click on the script, then select Run In PowerShell
A PowerShell console will appear and begin running the script. It will check whether you have Inkscape and Excel installed, try to find and open your Excel file, and process each sheet into SVG images.
1. If successful: The script will end with "Script run completed. SVG files should be located within <folder>", then wait for you to hit the Enter key on your keyboard before closing.
2. If not successful: The script will attempt to communicate the specific error message in red text, then wait for you to hit Enter before it closes. In the event that the script produces errors or is otherwise not performing as expected, create a ticket with IT and we will assist in troubleshooting. Where possible, attach a copy of the log (which is automatically saved in the same folder as the script) in your ticket.
After the script has run successfully, you should have an SVG image for each sheet that was in your Excel file. They will be named like <name of Excel file>-<name of sheet>.svg. Inspect a few of these images by opening them (typically they'll open in Edge or Chrome) to make sure they appear as expected -- particularly if you've made significant changes (i.e. beyond changing the times).
1. If any images did not generate properly or do not appear like they did in Excel, create a ticket with IT and we will assist in troubleshooting. Please include a copy of the Excel sheet, script log, and the offending SVG image in the ticket.
Finally, if the rest of this process was successful, send a message to Richard in Customer Service so he can upload the images to the website.