Train Timetables in MS Excel format

Discussion:

(too old to reply)

David FitzGerald

2004-04-14 13:20:32 UTC

I've been asked to produce a series of Excel spreadsheets with train
timetable information for a number of National Rail (London only)
routes. Besides using the Network Rail journey planner and manually
typing/pasting them in, is there any way of getting this information
into Excel? It's getting a bit complicated and time consuming!

Thanks,
David.

Dan Gravell

2004-04-14 13:34:01 UTC

Permalink

Post by David FitzGerald
I've been asked to produce a series of Excel spreadsheets with train
timetable information for a number of National Rail (London only)
routes. Besides using the Network Rail journey planner and manually
typing/pasting them in, is there any way of getting this information
into Excel? It's getting a bit complicated and time consuming!
Thanks,
David.

This is also something I've wondered. I don't suppose it's published in
XML, CSV (anything!) anywhere?

It'd be quite nice to have this sort of thing available for unconnected
PDAs, smartphones etc.

I wonder who "owns" the information, the TOCs, National Rail etc?

Brimstone

2004-04-14 15:56:39 UTC

Permalink

Post by Dan Gravell
I wonder who "owns" the information, the TOCs, National Rail etc?

Try ATOC.

Dan Gravell

2004-04-15 07:59:34 UTC

Permalink

Post by Brimstone
Try ATOC.

Discussions about how to hack a PDF to Excel then apply some god awful
VB to it aside though, the point I'm making is that this information
should be made available free of charge in a malleable format (e.g. XML,
or something), hence my question about ownership.

Well, when I say "should", perhaps what I mean is "can't see any reason
why not given that they provide glossy leaflets with the data on".

Ultimately taking this long way round is a maintainability nightmare and
is subject to change when the timetables are changed.

Adam Jackson

2004-04-14 16:07:27 UTC

Permalink

Have a look at:

http://persoenlicherfahrplan.bahn.de/bin/pf/query-p2w.exe/en?

Won't do Excel, but will create PDF files, or files suitable to be read in
Palm Organisers. Don't be put off that it's a German site - works
beautifully for UK services & does connections, where needed too...

--
Adam

(Remove ".N*0*S*P*A*M" from email to reply!)...

Dan Gravell

2004-04-14 16:24:49 UTC

Permalink

Post by Adam Jackson
http://persoenlicherfahrplan.bahn.de/bin/pf/query-p2w.exe/en?
Won't do Excel, but will create PDF files, or files suitable to be read in
Palm Organisers. Don't be put off that it's a German site - works
beautifully for UK services & does connections, where needed too...

This looks very promising. I used the J2ME version, specifying London to
Streatham. Unfortunately it assumed I meant Victoria rather than a
general query, which I suppose is unsurprising.

Will definitely play around with this when I have time later.

Dan

Paul Terry

2004-04-14 16:40:10 UTC

Permalink

I don't know about the other TOCs, but SWT published its timetables in
PDF format:

http://www.swtrains.co.uk/traintimes/

--
Paul Terry

Matt Wheeler

2004-04-14 16:59:26 UTC

Permalink

For those TOCs who publish timetables on their website in PDF format,
could you do a cut/paste from acrobat reader into Excel ?

Richard J.

2004-04-14 18:09:56 UTC

Permalink

Post by Matt Wheeler

Post by David FitzGerald
I've been asked to produce a series of Excel spreadsheets with
train timetable information for a number of National Rail (London
only) routes. Besides using the Network Rail journey planner and
manually typing/pasting them in, is there any way of getting this
information into Excel? It's getting a bit complicated and time
consuming!

For those TOCs who publish timetables on their website in PDF
format, could you do a cut/paste from acrobat reader into Excel ?

I tried with one of the SWT timetables. You can cut and paste, but the
content gets garbled extensively in the process. Even the Column Select
tool doesn't seem to be much use, as it ignores blank entries in the
column. Text Select does the same horizontally. Possibly a Visual Basic
program could be written to disentangle it, but it doesn't look at all
easy.

--
Richard J.
(to e-mail me, swap uk and yon in address)

Paul Terry

2004-04-15 07:46:18 UTC

Permalink

Post by Richard J.
I tried with one of the SWT timetables. You can cut and paste, but the
content gets garbled extensively in the process. Even the Column Select
tool doesn't seem to be much use, as it ignores blank entries in the
column. Text Select does the same horizontally. Possibly a Visual Basic
program could be written to disentangle it, but it doesn't look at all
easy.

Surely it would be better to invest in one of the conversion utilities
available, given the extent of the job?

Able2Extract (A2E) is one of those that claims to convert from PDF to
Excel (I've not tried it myself):

http://www.investintech.com/able2extract.html

--
Paul Terry

James

2004-04-14 22:09:48 UTC

Permalink

With trains which are at all regular there is the addition trick:

For a regular 15 minute interval, say, you could type something like:
=b2+(15/1440)
in cell c2, then cut and paste as required.

If a station is always, say, 3 minutes on from the previous one, you
could use:
=b2+(3/1440)
in cell b3, then cut and paste as required.

The important step is to set the number type in the cells to hh:mm,
then remember the colon as you enter each point!

With these things done, it's lighter work, but it's still a bit of a
challenge. If you like, e-mail me (***@le.ac.uk) and I'll send you a
sample of a fantasy 2 hour period on the Midland Main Line I've
created for totally different purposes.