Yesterday’s Thoughts

June 27, 2005

Converting Race Results into Excel Format

From a question in

Does anyone know how to easily convert web-based race results into an Excel spreadsheet readable format or import it into Excel?

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.

Sorry, comments for this entry are closed at this time.