Race results

3Hank said:

Does anyone know how to easily convert web-based race results into an
Excell spreadsheet readable format or import it into Excell?
I've send several emails trying to get race results also posted in
Excell but nothing has changed yet. I enjoy doing post race number
crunching and with a spreadsheet it's so much easier. The ones I've
converted so far where rather time consuming and only for small races
with a limited (< 400) number of participants. I would copy/paste the
web-page content into a document and replace spaces with columns but
there is an enormous amount of time going into cleaning things up, even
after I've imported it into Excell. Any suggestions how to improve this
process or how to convince race directors to post results in Excell
would be greatly appreciated.
Thx, Hank.

Hi Hank,
Yes, instead of "pasting" do a "paste special".
Play around with the variety of selections there. Usually all you need
to do is to select "paste with formatting".

If you would like you can show me an example, and I can go through it in
detail with you.

Triathlete said:

3Hank said:

Does anyone know how to easily convert web-based race results into an
Excell spreadsheet readable format or import it into Excell?
I've send several emails trying to get race results also posted in
Excell but nothing has changed yet. I enjoy doing post race number
crunching and with a spreadsheet it's so much easier. The ones I've
converted so far where rather time consuming and only for small races
with a limited (< 400) number of participants. I would copy/paste the
web-page content into a document and replace spaces with columns but
there is an enormous amount of time going into cleaning things up, even
after I've imported it into Excell. Any suggestions how to improve this
process or how to convince race directors to post results in Excell
would be greatly appreciated.
Thx, Hank.

Hi Hank,
Yes, instead of "pasting" do a "paste special".
Play around with the variety of selections there. Usually all you need
to do is to select "paste with formatting".

If you would like you can show me an example, and I can go through it
in detail with you.

You can also paste it into a text file... (using notepad or something)

Now open the text file with Excel. It should give you the option of
specifying the column delimiter, and a bunch of other options for
getting the data into the correct columns.

You may have to replace the .txt of the text file with .csv (but I'm
not sure about this).

roger

--
To email me, remove the greeting from my email address.

(for athletes) The Digital Athletic Log http://rogercortesi.com/athleticlog/

(for nerds) The Online Equation Editor http://rogercortesi.com/eqn/

Triathlete said:

3Hank said:

Does anyone know how to easily convert web-based race results into an
Excell spreadsheet readable format or import it into Excell?
I've send several emails trying to get race results also posted in
Excell but nothing has changed yet. I enjoy doing post race number
crunching and with a spreadsheet it's so much easier. The ones I've
converted so far where rather time consuming and only for small races
with a limited (< 400) number of participants. I would copy/paste the
web-page content into a document and replace spaces with columns but
there is an enormous amount of time going into cleaning things up, even
after I've imported it into Excell. Any suggestions how to improve this
process or how to convince race directors to post results in Excell
would be greatly appreciated.
Thx, Hank.

Hi Hank,
Yes, instead of "pasting" do a "paste special".
Play around with the variety of selections there. Usually all you need
to do is to select "paste with formatting".

If you would like you can show me an example, and I can go through it
in detail with you.

You can also paste it into a text file... (using notepad or something)

Now open the text file with Excel. It should give you the option of
specifying the column delimiter, and a bunch of other options for
getting the data into the correct columns.

You may have to replace the .txt of the text file with .csv (but I'm
not sure about this).

roger

--
To email me, remove the greeting from my email address.

(for athletes) The Digital Athletic Log http://rogercortesi.com/athleticlog/

(for nerds) The Online Equation Editor http://rogercortesi.com/eqn/

Excel (2000 on Windows at least) has this wired. It will pull down and
convert data from the web automatically.

On a new spreadsheet do

Data -> Get External Data -> New Web Query

Enter a URL and some config and you are done.

I just set it to pull down all the data for the 40+ men at Wildflower
Long Course. For options, I used only the tables and kept no web
formatting. The data appeared to be perfectly formated, except for two
small issues. Some headers were split over two rows "Swim Time" ->
"Swim" in F1 and "Time" in F2, and in the last column, "Club," longer
club names were split over 2 columns. The Wildflower data wasn't in
html tables, they wrapped the whole in pre tags, so if you had data
that used tables, I predict that it wouldn't have these problems.

You can configure the Web Query so that if the results change on the
web, then your spreadsheet will change also. Actually that is the
default, so if you don't want it to change underneath you, turn it off.

--
Ray

I forgot about this method. It works very well.

Kernal Mustard said:

Excel (2000 on Windows at least) has this wired. It will pull down and
convert data from the web automatically.

On a new spreadsheet do

Data -> Get External Data -> New Web Query

Enter a URL and some config and you are done.

I just set it to pull down all the data for the 40+ men at Wildflower
Long Course. For options, I used only the tables and kept no web
formatting. The data appeared to be perfectly formated, except for two
small issues. Some headers were split over two rows "Swim Time" ->
"Swim" in F1 and "Time" in F2, and in the last column, "Club," longer
club names were split over 2 columns. The Wildflower data wasn't in
html tables, they wrapped the whole in pre tags, so if you had data
that used tables, I predict that it wouldn't have these problems.

You can configure the Web Query so that if the results change on the
web, then your spreadsheet will change also. Actually that is the
default, so if you don't want it to change underneath you, turn it off.

This is awesome, thanks! You have saved me a lot of time and
frustration 🙂