Keene is crazy about baseball and softball. In the men’s softball league alone there are 56 teams, they have played 73 games so far, and will play another 431 through August. I know this because the schedule was made in Excel, and published as a web page that Excel’s Data->From Web feature can easily read back.
That Excel spreadsheet isn’t at all useful, however, if you want to combine the schedule with other public calendars, or with your own personal calendar. For that you need an ICS feed. And almost nobody — from the major league websites to local leagues like mine — bothers to provide those.
So I made an ICS feed for Keene men’s softball, and I did it in an unusual way. My first thought was to point FuseCal at the schedule page, which is just an HTML table that looks like this:
|Fri. Apr 17||6:00 PM||D||Computer Solutions of
|Fri. Apr 17||6:00 PM||O||Peerless Insurance||C&S 1||D2|
But FuseCal wouldn’t read that page. It’s a service that specializes in digging structure out of unstructured text, and I guess it got freaked out when it saw too much structure in this page!
Normally in cases like this I’d write a script to read the HTML table, parse out the dates and times, and write an ICS feed. But that isn’t a skill most people have, and I’m looking for ways to help calendar curators do this kind of thing for themselves.
Then it occurred to me: What would FuseCal read? How about this:
|Fri. Apr 17 06:00 PM,
Computer Solutions of Keene vs. J.A. Jubb, Field D
|Fri. Apr 17 06:00 PM,
Peerless Insurance vs. C&S 1, Field O
In other words, the same stuff lightly reformatted, and coalesced into a single cell per row. And yes, FuseCal will read that.
So I added a column to the Excel sheet with this formula:
=CONCATENATE(A4, " ", TEXT(B4,"hh:mm AM/PM"), ", " D4, " vs. ", F4, ", ", "Field", C4)
Then I exported that column back out as this HTML page, used FuseCal to create this ICS feed, and bookmarked it for inclusion in the aggregator.
This has to be the weirdest maneuver I’ve ever thought of. Taking away structure in order to be able to add structure? Crazy! And yet it makes perfect sense. FuseCal is a component that specializes in turning weakly-structured calendar-like data into better-structured calendar data. It also knows how to do other useful things, like monitor the source of that data for changes, and convert the data into ICS format. If it’s easy enough to provide the sort of weak structure that FuseCal expects, why not just do that and leverage its strengths?
So I did, and here are the key outcomes:
- The softball events now show up on the aggregated calendar.
- They’re also available directly from the ICS feed, so that players and their families can add these events to personal calendars.
It would be even nicer if, as a member of, say, the Blazers, I could scoop up just my own team’s events. And in fact FuseCal does support filtering. As the creator of the feed, I can go into the application, type Blazers, and restrict the feed to just those events. But I’d have to create 56 separate filtered calendars to provide feeds for all the teams. Feature request for FuseCal: Support filtering on the feed URL, so I can form URLs like:
While we’re wishing, here’s a feature request for Yahoo Pipes: Add a module for ICS feeds! Pipes is a fabulous tool for transforming, filtering, and merging RSS feeds. It would be great to be able to do the same kinds of magic with ICS feeds.
10 thoughts on “Curating softball schedules”
While MLB may not publish ICS feeds of team schedules, third parties do:
Yep, just learned that from Stephen Judd over here: http://friendfeed.com/rooms/elmcity
Yahoo Pipes *can* read in ics files, they just don’t make that clear in the UI. I’m doing it myself to filter some calendars. I tried using it to merge several, but it seemed to lose some events, possibly because the merged file includes so many
> Yahoo Pipes *can* read in ics files,
> they just don’t make that clear in the UI.
Really? Great tip, Michael, thanks, I will give it a whirl.
Thought you’d like to know from the IESG:
” ‘Internet Calendaring and Scheduling Core Object Specification (iCalendar) ‘ as a Proposed Standard ”
The Calsify Working Group has done a lot of work to improve operability, and with RFC2445 (iCalendar) a Proposed Standard, software tools should soon be able to produce and consume standardized files.
That should read “interoperability”
What about using dabbleDB?
If you’re going to be using excel anyway it might make more sense to simply use starttime, startdate, subject as column headers (using a simpler version of your formula) then you can import them into Outlook using the built in tools (file -> import and export etc.). Fusecal would obviously be better for dynamic web content as it can react to changes in the HTML by updating the calendar for subscribers but for a one-off conversion that seems easier to me.
I only discovered that it was possible after trying your method and not liking the adverts. Of course you still have to get from Outlook to iCal format but according to the web Outlook 2007 can do that.
I very much want to subscribe to your blog as if it were a listing of calendar events. I would like for your blog postings to show up as calendar events with the body of the text appearing in the calendar description. I use google calendar and wait eagerly for the day when I can open my calendar and see the blog posts for that day appear.
Has anyone created a recipe or set of instructions on how to accomplish this? I apologize if this question is either redundant or posted in the wrong area.