[Back to Touch & Go]

Encoding a Touch & Go Timetable with Microsoft Excel

This web page describes how you can encode and publish a timetable for the Touch & Go application using Microsoft Excel.

In summary, the steps involved in encoding and publishing a Touch & Go timetable using Excel are:
  1. download the Touch & Go Excel template spreadsheet
  2. in accordance with the instructions below, modify the spreadsheet to include your timetable data
  3. compile the spreadsheet using the provided utility program to produce a CSV file that can be downloaded to Touch & Go
  4. publish the CSV file on a web server and test that it downloads correctly to Touch & Go
  5. tell your friends, colleagues and/or customers the URL for your timetable so that they can download and use it
  6. if you want your timetable to be visible on the Touch & Go setup screen (say you're a bus operator), ask Funky People to include it on the master list of timetables. Note, there may be a small fee charged for this listing
Note: using Excel to prepare your timetable for Touch & Go is best suited to smaller timetables and manual data entry. If your timetable is larger, or perhaps already in a machine friendly form (e.g., in a database), please contact Funky People to learn about encoding your timetable in XML.

Note: when you publish a Touch & Go timetable it is your responsibility to ensure that the data is correct, and that it displays correctly within Touch & Go. Please check that everything looks ok before telling everybody about your timetable.

Excel Spreadsheet Format

Each Touch & Go Excel spreadsheet comprises a number of worksheets (pages), and each worksheet must be carefully named for the conversion utility to work correctly: The exact format of each worksheet is detailed below.

Network Worksheet

The Network worksheet captures high-level information about your timetable, and must be called "Network". Unlike the remainder of the Touch & Go worksheets, this worksheet is presented as rows of information (rather than columns). The rows are dervied from the Touch & Go data model and are:

Stops Worksheet

The Stops worksheet lists all of the stops that can be used to form the routes within your timetable, and must be called "Stops". Each row in the worksheet represents one stop, and the columns are:

Routes Worksheet

The Routes worksheet lists all of the routes within your timetable, and must be called "Routes". Each row in the worksheet represents one route, and the columns are:

Per-Route Worksheets

The fourth and subsequent worksheets each describe exactly one route, and must be named exactly as the ID of the route they describe. Each row of the worksheet describes a service operating on the route, and the columns are: The per-route worksheets are slightly unusual in that the heading row (row 1) not only hints at the data in each column, but actually contains data in its own right. More specifically, from column 2 onwards the first row lists the stops for the route by using values with the format stop (the simplest form) or stop/flags: Hint: if you find the flags confusing then just omit them. By default, the first stop in the route has the flag "U" (Pick-Up), the last stop in the route has the flag "D" (Set-Down), and all the intervening stops have the flags "UD" (Pick-Up and Set-Down).

From row 2 onwards, the stop columns (column 2 onwards) specify the stop time and/or flags for the service at that stop. The format of these values is one of: In most cases your schedule will be specified using either a stopMins value, for example 13:42, or a hyphen (to indicate that the service doesn't stop).

Converting your Timetable to CSV Format

Once your timetable has been encoded as an Excel spreadsheet it must be converted to a downloadable CSV file using the Touch & Go conversion utility. This utility is written in the Java programming language, and is run from the command line thus:

java -jar touchandgo.jar <Excel file name> [<CSV file name>]

If the CSV file name is not specified, it defaults to touchandgo.csv. The conversion utility provides rudimentary feedback, including any inconsistent or invalid data found within the Excel spreadsheet.

Note: the CSV file produced by the conversion utility is not the same as a file produced by simply exporting the Excel spreadsheet to CSV format.

Publishing your Timetable

Once the conversion utility has been used to obtain a CSV file, that CSV file should be published using a Web server that is visible to your iPhone or iPod Touch. When you download a timetable to Touch & Go by entering its URL, the application will automatically append touchandgo.csv to any URL that does not end with CSV. For example, a URL of bobsbuslines.com.au will be expanded to http://bobsbuslines.com.au/touchandgo.csv. Consequently, touchandgo.csv is the preferred and recommended name for your CVS file.

Once you have tested that your timetable downloads to Touch & Go, that the timetable data is correct, and that Touch & Go displays the data correctly you can then tell your friends, colleagues or customers where to find your timetable.

Listing your Timetable

The Touch & Go application includes a master list of timetables on the Setup tab; these timetables are seen by (and freely available to) all Touch & Go users. Please contact Funky People if you would like your timetable included on the master list. Note that a small fee may be charged for this listing, and the timetable data must be your's to list.

Resources

The Touch & Go template Excel spreadsheet is available here.

The Touch & Go conversion utility is available here.