Add a batch of dates to Google Calendar
I've always used several calendars to plan out my life. Until recently, I used a paper desk calendar to track work-related events like project milestones. I used an insanely hacked-up version of PHP Calendar to track daily appointments and travel plans. And I used a paper calendar hanging in the kitchen to track family events like birthdays and anniversaries. And to be honest, with all of the calendars I still wasn't very organized. The distinction between types of events and the calendars weren't as clear-cut as I'm describing them, and I'd often have a work project milestone on my kitchen calendar, or a birthday in PHP Calendar, not in their "proper" locations.
What I like about Google Calendar is the ability to lay several calendars on top of each other. So I can keep the family birthdays separate from the project milestones, but I can still show them all on one calendar if I need to. And with a click, I can remove the dates that aren't relevant for what I'm working on at the moment. The calendar list looks like this:
I decided to make Google Calendar my One Calendar To Rule Them All, and the switch has been very easy. The Ajaxy interface makes adding events insanely intuitive—click a day to add an event on that day. And I love the ability to click and drag several days to add weeklong events like conferences. The other big advantage to going digital is the ability to share calendars with other people. I can't easily send all of the data on my paper calendars to friends and family without Xerox and Fedex involved.
The one issue I ran into during the conversion was with family events. I had over 50 birthdays and anniversaries I wanted to add to a calendar, and the thought of clicking Create Event and adding data for each one, or worse—hunting and pecking to find a particular day to click—wasn't appealing. So I thought I'd share my method for dumping a bunch of dates into Google Calendar. You just need a little time to get your dates together, some Perl, and a Google Calendar account.
Import/Export
The Google Calendar doesn't have an API (yet), but it does have a hacker's little friend called import/export. Google accepts two types of calendar formats for import: iCalendar and Outlook's Comma Separated Values (CSV) export. So if you already have calendar data in Outlook or iCal you can simply import/export at will. (Yahoo! Calendar also exports to the Outlook CSV format, so switching is fairly painless.) But I didn't know the first thing about either of these formats, I simply had a list of dates I wanted to dump.
Gathering Dates
I had a head start because I already had a list of family birthdays and anniversaries in a text file. I massaged the list a little to get it into a data-friendly format, and ended up with a file full of dates that looked like this:
If you're building a list of dates from scratch you can use Excel. Just put dates in the first column in mm/dd/yyyy format, descriptions in the second. When you're done, save the file in CSV format, ignoring all the warnings about compatibility.
I called the file family_dates.csv. Yes, this is a comma-separated value list too, but not the format Google Calendar is expecting. Plus you don't want to add an event on April 18th, 1942. You want to add a full day event for April 18th, each year going forward. This is where I turned to Perl to massage the data.
The Code
This simple Perl script: calendar_csv.pl transformed the simple CSV list of dates and titles into the Outlook CSV format that Google likes to see. As you run the script it converts the year of the event into the current year, and adds an event for the next several years.
You'll need to customize the script a bit before you run it. Change
Keep in mind that the larger the amount of data in your calendar, the longer it will take Google to load that calendar when you fire up Google Calendar. I originally set the
In addition to Perl, you'll need the standard Date::Calc module.
And if you're not in the US and would prefer dd/mm/yyyy format, simply change this bit:
Once everything is set, run the script from a command prompt, like this:
A new file called import.csv will magically appear with your dates formatted as Outlook CSV events. With the file in hand you can head over to Google Calendar.
Importing Data
Over at Google Calendar, click Manage Calendars under your calendar listing on the left site. Choose Create new calendar, and give your calendar a name and any other details. Click Create Calendar, and you'll see the new calendar in your list. Now click Settings in the upper right corner of the page, and choose the Import Calendar tab. Click Browse..., choose import.csv from your local files, set the calendar to your new calendar, and click Import.
That's all there is to it. You'll get a quick report about the number of events Google was able to import. Go back to your main view, and you should see your imported dates on the calendar, in the color of your newly created calendar. With one import, my view of April went from this:
To this view with family birthdays the rust color:
(The details have been removed to protect the innocent.)
And once you have your calendar in Google, you can invite others to view and even help maintain the dates. Where I think this batch importing will be useful is for very large data sets. Imagine a teacher who wants to track the birthdays of students. It wouldn't be too hard to add the dates by hand. But a principal who wants to track the birthdays of everyone in a school will have an easier time putting together a spreadsheet than entering the days by hand. And even for my 50+ dates, writing a Perl script was preferable to entering the dates by hand.
So far I'm enjoying Google Calendar, and I haven't found any major problems beyond the limited importing ability. But now I really don't have an excuse for not sending out birthday cards.
Update (4/20): Google just released their Google Calendar API. I'll bet there are scores of hackers rushing to build bulk-import tools. Using the Calendar API would be a more stable way to import dates quickly. And wow! Hello, lifehackers!
What I like about Google Calendar is the ability to lay several calendars on top of each other. So I can keep the family birthdays separate from the project milestones, but I can still show them all on one calendar if I need to. And with a click, I can remove the dates that aren't relevant for what I'm working on at the moment. The calendar list looks like this:
I decided to make Google Calendar my One Calendar To Rule Them All, and the switch has been very easy. The Ajaxy interface makes adding events insanely intuitive—click a day to add an event on that day. And I love the ability to click and drag several days to add weeklong events like conferences. The other big advantage to going digital is the ability to share calendars with other people. I can't easily send all of the data on my paper calendars to friends and family without Xerox and Fedex involved.
The one issue I ran into during the conversion was with family events. I had over 50 birthdays and anniversaries I wanted to add to a calendar, and the thought of clicking Create Event and adding data for each one, or worse—hunting and pecking to find a particular day to click—wasn't appealing. So I thought I'd share my method for dumping a bunch of dates into Google Calendar. You just need a little time to get your dates together, some Perl, and a Google Calendar account.
Import/Export
The Google Calendar doesn't have an API (yet), but it does have a hacker's little friend called import/export. Google accepts two types of calendar formats for import: iCalendar and Outlook's Comma Separated Values (CSV) export. So if you already have calendar data in Outlook or iCal you can simply import/export at will. (Yahoo! Calendar also exports to the Outlook CSV format, so switching is fairly painless.) But I didn't know the first thing about either of these formats, I simply had a list of dates I wanted to dump.
Gathering Dates
I had a head start because I already had a list of family birthdays and anniversaries in a text file. I massaged the list a little to get it into a data-friendly format, and ended up with a file full of dates that looked like this:
4/18/1942,Uncle Bob's Birthday
4/28/1944,Aunt Sally's Birthday
7/23/1978,Lindsay and Tobias' Anniversary
8/10/1989,Cousin Maeby's Birthday
...
If you're building a list of dates from scratch you can use Excel. Just put dates in the first column in mm/dd/yyyy format, descriptions in the second. When you're done, save the file in CSV format, ignoring all the warnings about compatibility.
I called the file family_dates.csv. Yes, this is a comma-separated value list too, but not the format Google Calendar is expecting. Plus you don't want to add an event on April 18th, 1942. You want to add a full day event for April 18th, each year going forward. This is where I turned to Perl to massage the data.
The Code
This simple Perl script: calendar_csv.pl transformed the simple CSV list of dates and titles into the Outlook CSV format that Google likes to see. As you run the script it converts the year of the event into the current year, and adds an event for the next several years.
You'll need to customize the script a bit before you run it. Change
$datefile to the name of your simple CSV file, in my case family_dates.csv. You can change $importfile to your preferred name of the output file, the default is import.csv. And you can set the number of years into the future that you'd like the date to appear by adjusting the value of $yearsahead, the default is 5. (If your events should only be added in the current year, set this to 1.)
Keep in mind that the larger the amount of data in your calendar, the longer it will take Google to load that calendar when you fire up Google Calendar. I originally set the
$yearsahead value to 10, but with over 500 events, the calendar was noticably slowing the Google Calendar startup.
In addition to Perl, you'll need the standard Date::Calc module.
And if you're not in the US and would prefer dd/mm/yyyy format, simply change this bit:
my ($month, $day) = to this: my ($day, $month) =. Instant internationalization!
Once everything is set, run the script from a command prompt, like this:
perl calendar_csv.pl
A new file called import.csv will magically appear with your dates formatted as Outlook CSV events. With the file in hand you can head over to Google Calendar.
Importing Data
Over at Google Calendar, click Manage Calendars under your calendar listing on the left site. Choose Create new calendar, and give your calendar a name and any other details. Click Create Calendar, and you'll see the new calendar in your list. Now click Settings in the upper right corner of the page, and choose the Import Calendar tab. Click Browse..., choose import.csv from your local files, set the calendar to your new calendar, and click Import.
That's all there is to it. You'll get a quick report about the number of events Google was able to import. Go back to your main view, and you should see your imported dates on the calendar, in the color of your newly created calendar. With one import, my view of April went from this:
To this view with family birthdays the rust color:
(The details have been removed to protect the innocent.)
And once you have your calendar in Google, you can invite others to view and even help maintain the dates. Where I think this batch importing will be useful is for very large data sets. Imagine a teacher who wants to track the birthdays of students. It wouldn't be too hard to add the dates by hand. But a principal who wants to track the birthdays of everyone in a school will have an easier time putting together a spreadsheet than entering the days by hand. And even for my 50+ dates, writing a Perl script was preferable to entering the dates by hand.
So far I'm enjoying Google Calendar, and I haven't found any major problems beyond the limited importing ability. But now I really don't have an excuse for not sending out birthday cards.
Update (4/20): Google just released their Google Calendar API. I'll bet there are scores of hackers rushing to build bulk-import tools. Using the Calendar API would be a more stable way to import dates quickly. And wow! Hello, lifehackers!
