Importing data into Google Calendar

Entering data into Google calendar can be tedious, but it turns out that there is a relatively easy way to perform a bulk import of data. At the bottom left corner of the calendar page, there is an "Add" button. Click on this, and one of the options is "Import Calendar". The Google Calendar import function is expecting a comma-separated values (CSV) file. Apparently if you export your Outlook calendar information using CSV format, Google will accept it. (I don't use Outlook, but I'm told it works.) However, you can also create the data manually. In this post I will provide an example of what such a file looks like, and then tell you how I created it.

To enter my son's baseball games, I used a file with the following format (what you see below is the first three lines of the file. You can have as many lines of data/events as you want.):

"Subject","Start Date","Start time","End Date","End time","All day event","Description","Location"
"Game at KWN (Away)",06/27/07,06:00:00 PM,06/27/07,08:00:00 PM,0,"Kenilworth North","Sears School 542 Abbotsford Rd, Kenilworth, IL"
" Game at J1 ( Away)",07/07/07,11:00:00 AM,07/07/07,01:00:00 PM,0,"James #1","James Park, Evanston IL"

The "subject" item shows up under "What" in Google Calendar (the event title), the date and time information under "When", "location" under "Where", and "Description" under "Description".

I haven't done a lot of experimenting, but I tried various things and the format above did work correctly. I haven't been able to find documentation of the import function, so this was based on trial and error and experimenting with a Perl script from this very useful page.

Note that Google calendar automatically creates a map link based on the "location" field.

You can create the example above with a text editor, or in theory, with any program that writes CSV files. I tried to use Excel, but Excel CSV files don't work since Excel doesn't put quotes around text. There is no option to format the files differently. The program that did work is the spreadsheet in OpenOffice, which provides CSV formatting options. Perhaps Excel works, but it didn't work for me when I tried it.

Accessing Google Calendar data offline

In order to use Google calendar you must be online, i.e., connected to the internet. The only required software is a web browser. However, if you install some extra software you can work with your calendar when you are offline.

To view and edit your calendars when you are not connected to the internet, you must have two additional pieces of software. First, you need a calendar application that can read and write iCalendar files. Second, you need software that can synchronize your calendar data files with the Google server. Setting up all of this requires that you edit configuration files and engage in similar Unix-istic behavior. (I hope this will change before long.) Once you have it set up, it is trivial and transparent to use. It is magical to make a change on your laptop calendar and have it show up on Google calendar.

I have been using Mozilla Sunbird as the calendaring application, and GCALDaemon as the connector between Sunbird and Google. I am not going to provide instructions here because the documentation that comes with GCALDaemon is exceptionally clear. (What an admirable piece of software!) I will only suggest that you wait until Mozilla Sunbird 0.5 is released. This should occur very soon. GCALDaemon works with any calendar software that can use iCalendar files. (There are numerous alternatives for Windows, OS X and Linux, but I don't believe you can use Outlook. I could be wrong.)

You should be aware that all of this is beta software. Lots of things can go wrong. Make backups!

If you're not sure what to back up or how to back it up, or how to figure out what and how, you should either wait a while before you try to make this work or else you should be prepared to roll up your sleeves.

If you're asking whether this really works, the answer is yes. My wife and I are both using Sunbird and sharing a set of Google calendars. It's very cool. And my wife has no idea (and more importantly, no need to know) what's happening under the hood.

More on Google Calendars

It turns out that Google's calendaring application has lots of very cool features. I will illustrate a few here. The main point of what I will say, however, is that if you create a calendar, there are lots of ways for it to be helpful to others, even if they don't have a Google calendar account.

1. You can create an HTML link that displays the calendar in any web browser. To see this with the District 65 calendar, click here. (You will see the actual URL after you click. You could easily send this in an e-mail message to a group.) Since the District 65 events don't start until September, click ahead to September to see dates with entries. Be sure to click on the "agenda" tab on the top right to see a list of the events in any given month.



2. You can embed a calendar in a web page. Google has a tool that writes the code to do this.
   



3. You can invite people to a specific event. You supply a list of e-mail messages and Google calendar keeps track of who responds. You can see the list by clicking on the event.



4. For a private calendar, you can decide who sees the calendar and who doesn't, who gets to edit the calendar, and even who gets to decide who sees it. (For all of this to work, everyone involved needs a google calendar account.)



5. You can synchronize Google calendar both ways with Mozilla Sunbird! The trick is to use an open-source program named GCALDaemon. This is not for the faint-hearted or the impatient, but it can be done. I will say more about this some other time.
   

The point is that good-enough shared calendaring is here now. It's easy to use and potentially very useful.

Scheduling is heck

It's time to do something about scheduling. I hope the following will be of help to at least some District 65 parents.

I have created a Google calendar based on the 2007-2008 District 65 calendar (Evanston/Skokie). I may have missed a date here and there; the paper calendar is pretty confusingly presented (come on folks, you can do better than that!). Below I will present some caveats. First, here are directions for accessing the calendar.

1. You need a Google calendar account. If you don't have one, go to calendar.google.com and follow the directions to register. You can use any e-mail address to create a log in.
   
   
2. In the Google calendar screen: On the left hand side, under "calendars", there is an "Other calendars" section. Click on the "+".
   
   
3. Enter "District 65 2007" in the "search public calendars" box. You should then see "District 65, Evanston/Skokie 2007-2008", with a button to add this calendar to your calendar.
   
   
4. Any changes I make to the calendar will then be reflected in the calendars of whoever subscribes.
   
   
5. After you've added the District 65 calendar, click on the "+" again and select "Browse Calendars" in the Add Other Calendar dialog. You'll want to add the US Holidays calendar.
   

The Google calendar is free and does not come with guarantees. They obviously have amazing folks working there, but there is no guarantee that a google-based calendar will not vanish forever due to some technological hiccup. There is no guarantee that it will not be hacked (boy, wouldn't some enterprising 8th grader love to hack Google's servers and create extra vacation days!) There is (to my knowledge) no way to hook a Google calendar into Outlook (but see this page for an ambitious attempt to integrate things).

Having said this, I expect the non-Microsoft world to move towards standards-based calendar integration. Mozilla Sunbird has a Google calendar connector, but Sunbird is still very much a work in progress. I would hope (with Google's CEO on Apple's board and the iPhone coming) that the next edition of Apple's iCal will connect to Google, but this is just a hope.

I hope that by creating and sharing calendars we can begin to attack the enormous inefficiency of everyone keeping identical lists of events.