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:

download the Touch & Go Excel template spreadsheet
in accordance with the instructions below, modify the spreadsheet to include your timetable data
compile the spreadsheet using the provided utility program to produce a CSV file that can be downloaded to Touch & Go
publish the CSV file on a web server and test that it downloads correctly to Touch & Go
tell your friends, colleagues and/or customers the URL for your timetable so that they can download and use it
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 first worksheet must be called "Network", and captures high-level information about the timetable (network) being encoded
the second worksheet must be called "Stops", and lists all of the physical stops (taxi-ranks, bus stops, tram stops, train stations or airports) that can form part of the routes within your timetable
the third worksheet must be called "Routes", and lists all of the routes within your timetable
the fourth and subsequent worksheets each detail the services for a single route, and must be named with the "id" of the route they relate to.

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:

ID
AVAILABLE - one of YES or NO
NAME
DESCRIPTION
URL
VERSION
REGION
REGION_FOCUS_LATITUDE
REGION_FOCUS_LONGITUDE
REGION_RADIUS
IMAGE_URL
INFO_URL
INFO_PHONE
MIN_TANGO_VERSION

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:

ID
LATITUDE
LONGITUDE
RANGE - one of SMALL, MEDIUM or LARGE
TYPE - one of TAXI_RANK, BUS_STOP, TRAM_STOP, TRAIN_STATION or AIRPORT
DESCRIPTION
LOCALITY
ENQUIRY_KEY

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:

ID
NAME
DESCRIPTION
DAYS - a seven character string representing the days Sunday through Saturday, and where each character is "-" to indicate no service, or any other character to represent service. For example, the string "S--WT-S" means the route runs on Sunday, Wednesday, Thursday and Saturday
TYPE - zero or more of E (for Express/Commuter Service), S (for School Service), A (for Airport Service), T (for Tourist Service), C (for Corporate Service) and F (for Free Service). For example, "AF" for a Free Airport Service
VEHICLE_TYPE - one of CAR, BUS, TRAM, TRAIN or PLANE
RETURN_ROUTE - the exact ID of the return route, or blank if there is no return route

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:

DAYS - the days the service runs, formatted as per the DAYS column of the Routes worksheet
[one or more columns] stop info - see below

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:

stop is the ID of a stop listed on the "Stops" worksheet
flags is zero or more of U (for Pick-Up), D (for Set-Down), H (for Hail Driver) or I (for Interchange). These flags are default values that apply to the services listed below: if the service provides its own flags for the stop in question, then the service's flags are used

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:

- a single hyphen/dash indicates that the service does not stop at that stop
blank - a blank cell indicates that the service does stop, but that there is no formal schedule for this stop (i.e., the stop time is somewhere between the last non-blank cell and the next non-blank cell)
stopMins - indicates that the service stops at the specified time
stopMins/flags - indicates that the service stops at the specified time, and with the flags specified (format as per flags above).

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.