Next post Previous post

Awk and calendar events

December 7, 2011 at 1:16 PM by Dr. Drang

What follows is another post in which I use some computer-y tools to hammer one of my boys’ sports schedules into a format I can import into iCal. This time it’s my older son’s swim meet schedule, which started as a table on a website and is now parked comfortably in all of my digital devices via iCloud. As always, my goal is to make this transformation as quick and painless as possible.

Before I start, though, I’d like to make this plea to sports schedulers everywhere (but especially in Naperville, Illinois): Please think of the people who are going to use the schedules you make. On my son’s swim team, for example, every family has at least one parent whose calendar is kept on some combination of smart phone, iPad, and computer. Yes, many people still use paper calendars (my wife, for example), but that number is shrinking, and it’s easier to move a schedule from Outlook or iCal to paper than it is to move it in the other direction.

OK, let’s move on to what I actually did.

My primary tool in the transformation is my Events bundle for TextMate, initially described in this blog post and available to download from this GitHub repository. The Events bundle is a small set of tools that take a schedule in a format like this,

Event 1|12/07/11 9:00 am|1.00|Location|Notes
Event 2|12/10/11 4:00 pm|1.00|Location|Notes
Event 3|12/12/11 9:00 am|1.00|Location|Notes

where each event takes up a single line of the file and the information about the events—summary, start date/time, duration, location, and other—are in fields separated by pipe characters (|),1 and turns it into an .ics file that can be imported into Outlook, iCal, or Google Calendar. The format used by .ics files is the iCalendar format, described in painful detail in RFC 2445.

If I have to type in several entries, doing it in a text file is usually much faster than fiddling with a calendar program because I can take advantage of my text editor’s shortcuts. But the real advantage comes when I have a schedule in some sort of text format. Then I can simply rearrange the fields and do no retyping at all.

When copied from a table on the team website into a plain text document in TextMate, the swim schedule looked like this:

12/6/2011‣Tue‣vs. W. Chicago/W. Aurora/Glenbard Coop‣H‣5:00 PM‣Pool
12/9/2011‣Fri‣vs. New Trier‣A‣5:30 PM‣New Trier
12/10/2011‣Sat‣vs. Barrington "Bronco" Relays‣A‣1:30 PM‣Barrington H.S.
12/17/2011‣Sat‣vs. Neuqua Valley Invite‣A‣1:00 PM‣Neuqua Valley H.S.

where the ‣ characters represent tabs.

I then did a zero-width column selection (by holding down the Option key and dragging down) at the beginning of every line and typed Swim meet‣, which added that text and a tab to the beginning of each line. Normally, I have TextMate set to use “soft tabs,” which means that spaces get inserted when I press the tab key. To get a real tab, I turned soft tabs off for this file. There’s a little popup menu at the bottom of the window that makes this a snap.

That left me with

Swim meet‣12/6/2011‣Tue‣vs. W. Chicago/W. Aurora/Glenbard Coop‣H‣5:00 PM‣Pool
Swim meet‣12/9/2011‣Fri‣vs. New Trier‣A‣5:30 PM‣New Trier
Swim meet‣12/10/2011‣Sat‣vs. Barrington "Bronco" Relays‣A‣1:30 PM‣Barrington H.S.
Swim meet‣12/17/2011‣Sat‣vs. Neuqua Valley Invite‣A‣1:00 PM‣Neuqua Valley H.S.

Then came a call to Filter Through Command… from the Text menu to rearrange the fields.

The Awk script for transforming the data is a bit longer than the dialog box can show. Here’s the full command:

awk -F\t '{print $1"|"$2" " $6"|3.00|"$7"|"$4}'

This put the starting time together with the date, added the 3-hour duration, and put the location and opponent in their proper positions. The -F\t option told Awk to use tabs as the field separator instead of Awk’s default of any whitespace.2

I’m basically done. The last step in the transformation was just a little Find/Replace cleanup to delete all the vs. parts at the front of the opponents’ names in the Notes field. I ended up with this:

Swim meet|12/6/2011 5:00 PM|3.00|Pool|W. Chicago/W. Aurora/Glenbard Coop
Swim meet|12/9/2011 5:30 PM|3.00|New Trier|New Trier
Swim meet|12/10/2011 1:30 PM|3.00|Barrington H.S.|Barrington "Bronco" Relays
Swim meet|12/17/2011 1:00 PM|3.00|Neuqua Valley H.S.|Neuqua Valley Invite

The transformation complete, I ran my Make ICS command, which comes with the bundle. I saved the resulting .ics file to my Desktop, then dragged it into the iCal icon in my Dock. All the events went into my calendar without a hitch.

Was it worth the effort? Yes. Although I showed you only four of them, there were over two dozen events in the schedule; there’s no way I could’ve typed them all correctly in as little time as it took me to do these transformations. The hardest part was the little Awk script, which I had to run twice because I had a syntax error in it the first time (missing quote or extra quote—I can’t remember which).

I’m presenting this not because you’ll be able to transform one of your schedules by following these directions exactly. I probably won’t, either. In my experience, every new sports schedule is sui generis—even when the same people who made last year’s schedule make this year’s, they change the format in some way. But if have good tools at your disposal, like the Unix toolbox and an editor like TextMate or BBEdit or Vim or Emacs, and you learn how those tools work, you can whip up little solutions like this in no time.

Of course, the real solution is for the swim team to provide an .ics file in the first place. I’m not holding my breath.

  1. I chose the pipe character as the separator because tabs are invisible and characters like commas and dashes might actually be in one of the fields. ↩

  2. I realized afterward that I could have used a BEGIN {OFS="|"} block to specify the output field separator instead of adding all those explicit pipe characters. For a short script like this, I’m not sure that would have saved me much. ↩

Next post Previous post