1000 Miles or Bust!

Switch from Yahoo Calendar to Google Calendar

I finally got around to switching my calendar from Yahoo to Google this weekend. It was, to say the least, an arduous process, and so I’ve written up some tips on syncing a Yahoo calendar with a Google calendar and the rest of the world’s iCalendar standard. Officially, Yahoo claims it will eventually provide its calendars in iCalendar format, someday… so I just hacked it, today.

I’ve been disappointed in the glacial pace of Yahoo’s web tools development for some time now, but I have been using Yahoo calendar for several years and I didn’t want to lose my appointment history, what with random phone numbers and emails and etc recorded in the entries. But since I abandoned Microsoft Outlook for Thunderbird mail a couple of years ago, I’ve been unable to sync my desktop calendar with my online calendar. In fact, I had pretty much given up bothering to keep a desktop calendar, although I had played with Mozilla’s Sunbird and felt it had promise. Now that the Lightning calendar extension for Thunderbird has finally become relatively mature (0.8 is due out shortly), I had decided to it was time to keep a synchronized desktop calendar again.

The process was not without pitfalls however, especially since Yahoo calendar does not export standard iCalendar files. It exports in a marginally useful CSV format that broke both the auto-import function in Google calendar and broke the automated Yahoo CSV to iCalendar convertor I link to in the resources section below. While in Google Calendar I just had to delete the erroneous calendar, Lightning hung so badly when importing the converted iCalendar file that I had to kill the process manually and the go into my Thunderbird profile directory and rename the (sql-lite) SDB file that Lightning uses to store calendar entries, losing the (mercifully few) entries I had put in by hand. I’m still not sure if the failures were due to the sheer quantity of entries (since the CSV file represents repeating events as single events, there are many more entries than in iCalendar format), or if the problem lies in the failure of the Yahoo CSV exporter to put a valid timestamp in certain fields (again apparently for repeating events), or something else, like problems with how Yahoo exported some long lists of tasks I had in the description field. In short, I had to edit the CSV file with OpenOffice before it was acceptable to Google Calendar or Lightning to import.

But all the difficulties are not just with Yahoo. There are also some quirks as to how Google Provider imports and works with with calendar files, particularly in that Google Provider doesn’t cache the online calendar locally and hence won’t fetch your calendar if you aren’t online (e.g., in an airplane). A more robust solution is to use the Java-based GCAL daemon to cache the calendar locally, and then just point Lightning to the synchronized ics (iCalendar) file.

While some useful links to my internet research follow later in the Resources section, first I’ll give a step-by-step synopsis of what actually worked.

Instructions:

1. First I downloaded and installed the Thunderbird add-on extensions Lightning and Google Provider. Then restart Thunderbird. [Alternately to using Google Provider (which requires you to be online to see your calendar), you may prefer to run the GCAL daemon to sync to a local version of the iCalendar file with Google (note: it’s a Java ap). Also, GCAL can also sync with other calendar programs like Apple’s iCal. So if all you are looking to do is convert your Yahoo calendar to iCalendar format or into Google Calendar, you obviously won’t need to do this step.]
2. Next I went to my Yahoo pages and exported my Yahoo calendar as a CSV file. Specifically I chose Yahoo Calendar–> Options–> Import/Export–> Export to Outlook as a CSV file.
3. I’ll save you the trial and error that happened next by suggesting that you now open up the CSV file in OpenOffice Calc or Excel. The first thing I did was condense the file by deleting the 25 years or so of future repeating events at the bottom of the CSV file. After browsing through the bottom, I discovered my important future events ended with a conference I have scheduled in late 2009, and I just went through and edited out extraneous repeating events between today (March 2008) and the last important event I had actually scheduled. However, I made sure to leave one copy of each repeating event I wanted to keep so that I could set up the repeating properly in Google once I completed the transfer (see the last step).
4. Next, I turned on the Data-> Autofilters functionality of the spreadsheet programs and then used the filters to inspect the remaining data. By setting the filters appropriately, I was able to delete some of the other potentially troublesome fields (e.g. lengthy task lists in the description field) or copiously repeating entries for past activities. Alternately you could do this by writing a quick “delete row if cell value equal this value” macro–that was much quicker for deleting really numerous entries.
5. After saving the CSV file, I imported it using the Google calendar CSV import feature. To do that, I first created a new Google calendar called named “Old Yahoo Calendar” (from experience, I can tell you it is wise not to risk corrupting an existing calendar). Then choose the little drop down arrow to the right of the word “Add” in the My Calendars sidebar, and import the edited CSV file, making sure to direct it into the new empty calendar.
6. Inspect the calendar and see if the calendar import has worked appropriately. You may still need to do more data massaging of the CSV file…I did.
7. Next, Thunderbird must be setup to show your Google calendars. To use Google Provider (simpler), just choose File–> Create–> Calendar–> On the Network –> Google Calendar. The location field should be XML address of your Google calendar. You can find that by clicking on the little down arrow by the name of your calendar (e.g. “Old Yahoo Calendar”) in the My Calendars sidebar of the Google interface, and then clicking on Calendar Settings in the popup menu. Right click on the XML icon next to the private address and choose copy link. Paste that link into the location field on the Thunderbird/lightning setup page and you are good to go. [Alternately, if you are using the GCAL daemon, you’ll need to choose File–> Create–> Calendar–> On the Network –> iCalendar (ICS) and specify the location of the GCAL ics file.]
8. Last, you will need to edit any repeating calendar entries to convert them from single events. To do this, I chose to copy the events out of my “Old Yahoo Calendar” and into my main Google calendar from within Google’s interface rather than from within Lightning’s, largely because Google’s copy event function seemed to pickup my default Google calendar preference to send me a reminder email one day in advance of a new event while Lightning didn’t. After I made sure each birthday, regular weekly or monthly meeting, future multi-day conference, upcoming trip out of town, etc., was set to repeat and notify me automatically, I deleted the future entries out of my old Yahoo calendar and was done. The past entries I simply kept in my old Yahoo calendar for reference.

Resources:

• The Mozilla Lightning (Sunbird-clone) calendar extension for Thunderbird can be found at: http://www.mozilla.org/projects/calendar/lightning/
• The Google Calendar Provider to Lightning can be found at: https://addons.mozilla.org/en-US/thunderbird/addon/4631
• GCAL daemon, which creates a local calendar server that can share your Google calendar as an iCalendar file, can be found at: http://gcaldaemon.sourceforge.net/
• A useful collection of scripts for modifying the Google calendar can be found at: http://mashable.com/2007/08/30/google-calendar/
• The Online Yahoo Calendar convertor from CSV to iCalendar formats can be found at: http://manas.tungare.name/projects/yahoo2ical/
• The Wikipedia entry for the iCalendar can be found at: http://en.wikipedia.org/wiki/ICalendar

March 9th, 2008 Posted by Tim | Hacking, Every last post | one comment