Friday, June 13, 2008

Import List of Events into Google Calendar

If you're like me, you may have the data you want to put into your Google Calendar stored in some other format. For example, I already have some widgets with calendar-related information in them, and I need to be able to create Google Calendars by importing that information all at once. Luckily, you can do that using the Google Calendar Import feature.

To import the calendar data, you need a CSV (comma-separated-value) file, which is one of the formats that is compatible with Excel or any other spreadsheet program you might be using. These are the columns you need in the file:
Subject,Start Date,Start Time,End Date,End Time,All Day Event,Reminder On/Off,Reminder Date,Reminder Time,Meeting Organizer,Description,Location,Private
For your convenience, I've created a Google Calendar Import Spreadsheet you can use as a template. You can save this to your desktop, import it into your Google Documents (or open it in Excel, etc.), edit it, and export it as a CSV file.

When you have completed your file, here is how you import the events into a calendar. First, make sure you have created the calendar into which you will import the events. Then, click on the Add-Import Calendar option.



You can then browse to find and upload the CSV file of events you have created on your computer, and designate the calendar you want to use.


I've used this to import hundreds of events at once. Here are a few tips based on my own experience and from some information I found online:

Be careful. There is no "undo" option for the import. If you accidentally import the events into the wrong calendar, or import them twice into the same calendar, there is no way to mass remove the events. You have to remove them one at a time - so be careful!

Required fields. Every event needs a Subject, a Start Date and a Start Time. Other than that, all fields are optional and can be safely left blank.

Private/Public. If these events are for a calendar you want to share, make sure you make the Private field as FALSE for each event. That way you can be sure that each event you are adding will be publicly available.

You can use HTML in the Description. I've been able to create link sin the Description field using HTML. I'm not sure just how much HTML is allowed in the Description field (I could not find documentation for that online), but I was able to successfully include A HREF tags to create active links in the description for an event.

Although I have not had any trouble importing calendar data, it can be tricky working with CSV files. Google offers to help with problem files, as you can read at this Google Calendar Help page. If I learn some more about the "ins and outs" of importing calendar data, I'll be sure to update this post. :-)



Some dynamic content may not display if you are reading this blog via RSS or through an email subscription. You can always visit the How-To Tech Tips blog to see the full content, and to find out how to subscribe to the latest posts.

58 comments:

John said...

Laura,

Is there a way for me to copy your Google Import Spreadsheet from Google Docs to edit it? I haven't found a straightforward way.
http://spreadsheets.google.com/pub?key=p5CrDJySNh4OpA-6FsEynPg

Also note typo:

"CVS (comma-value-separated) file" in the second paragraph should be CSV as in the rest of your post

Laura Gibbs said...

Hi John, I've changed it to the raw csv file; see if that works better for you:

http://media.bestmoodle.net/calendar_import.csv

You can either use the Upload feature in GoogleDocs (and enter the address of the file on the web), or you can just right-mouse click and do file-save-as and save the document to your computer to work with in Excel (if I have a LOT of events, that's actually what I do; I haven't had good luck with GoogleDocs for really big spreadsheets).

:-)

John said...

Thanks! That works perfectly with the raw .csv file.

Laura Gibbs said...

Super! :-)

Goodson Family said...

Thanks for this helpful post.

A question for you:
Have you had any success putting any info in the "Meeting Organizer" field? I was hoping that I could actually create an event with a different organizer than myself - so that I could click easily through to be able to email them and such. But I can't figure out the correct format to get it to actually import any info from that field.

Laura Gibbs said...

Alas, I don't have any experience with that at all - and I can understand your frustration, too. It was just by trial and error that I figured out how I could insert html code - I remember looking online trying to find some kind of documentation about exactly what is possible for each of these fields, and I did not find anything. But that was a good six months ago - there MIGHT be something out there now. Google is an odd service, though - they are surprisingly un-good about documenting things publicly. Luckily there are lots of people posting tips online as I have done here.
Have you checked the Lifehacker blog? I sure have found a lot of useful tips there myself! They would probably be able to answer your question if anyone can! :-)

Mario said...

Thanks for this File. It helped me to understand the Google-Calendar Import!

Laura Gibbs said...

Thanks for your comment, Mario! It's funny - I only do this process two times per year, at the beginning of the Fall semester and then at the beginning of the Spring semester, and since it's not something I do often, I had to come back here to review my own notes to make sure I was doing it right! :-)

G.I. said...

Unbelievably useful! Thank you for your great description and the CSV file, I'll be using it combined with SMS alerts to the mobile phone to teach English to my boyfriend, one word per day. I'll just create a vocabulary list and format it as a list of events to be imported into the calendar with the automatic notifications set for SMS. Hopefully someday he'll be able to read this and thank you himself :)

Laura Gibbs said...

Excellent! I am glad you found it useful. I am working on something like that for my students, too - inspirational proverbs about teaching and learning. I've almost got all the proverbs ready to go! :-)

Alexis said...

Hi Laura,

Wondering if I need to repeat this process every year (looking to import reminders for client birthdays) or if there is a way to make an event date recurring.

Thoughts??
Thanks for the helpful post!
Alexis

Laura Gibbs said...

Yes, you can make events recurring but I think you have to do it manually; I don't think you can import that via the spreadsheet; here is the Google Help page on Import Calendar CSV which lists the possible column headers.

Deb said...

I cannot figure out how to import an updated CSV file to a current calendar without recreating it. I have 8 users that share this and really don't want to set it up each time. Ideally I need to update every week. Thanks

Laura Gibbs said...

I'm not quite sure what to say - I use this process to update my calendars every semester and have done so for about the past four years, and I have never had to create a calendar. The idea is that you import the CSV into an EXISTING calendar - make sure you are looking at your calendar, then choose the "Add" button at the very very bottom of the screen - under where it says "Other Calendars" - and then you will be prompted to upload your file and to indicate which calendar you want to add the events to. That is how I add new events every semester, and you should be able to do the same every week, no problem.

miprimerblog said...

Works perfectly, Thankssss

noam h said...

Do you know if there's a way to make all my events repeat yearly? I hope I won't have to do that manually...

Laura Gibbs said...

Hi Noam, the way I do that with my Roman Calendar is that I save the CSV file, and every year I just do find-and-replace to change the year. That's worked for me - there might be an even easier way to do that, but using find-and-replace in the CSV file is what I always do. :-)

triple-d said...

This has been quite helpful. I am trying what seems like ought to be a very simple project: adapt my existing spreadsheet of family birthdays so it can be imported into google. I do have one question: must all imported calendars be "other calendars", and not "my calenders"? It seems like other calendars don't sync into outlook. that's frustrating when you attach/import sports calendars for instance and they don't sync with outlook. I guess I would have to just import the calendar directly into outlook, and then would it become part of my calendar on google? if so, should i import this birthday file into outlook and when i google sync it will go into my google calendar? hope that's not too confusing.

Laura Gibbs said...

You can definitely do that! When you upload the CSV file, you will be prompted what calendar you want to add the events to, and you can definitely add it to the main "my calendar" - you should see that as one of the options when you upload the CSV file. :-)

triple-d said...

Laura, thanks. Could I have done that with calendars I now have as other calendars? If so, can I somehow remove then as other calendars and readd them as my calendars? [They were not imports].

triple-d said...

And if I may be so bold, how would I set up the csv file to calculate what birthday or anniversary? I.E. if I used start date as the date of birth or wedding date, any way to have the calendar display 40th birthday?

Laura Gibbs said...

Yes, it should be possible - you can EXPORT any other calendar into the standard format and then immediately re-ADD it, putting it into your "my" calendar. I've never done that, but it should work just fine! :-)

triple-d said...

thanks. continued follow up. most of my "other calenders" are things like sports team schedules and the USA holidays calendar that i found i think through the settings page. getting back to the google calendar sync question, as long as those are "other calendars" they don't come into outlook. would i have to export each of those and import them then as "my calendars"?

Laura Gibbs said...

There might be an easier way with Outlook, but since I am not an Outlook user, I am not sure about that at all. You might try Googling Outlook and Google Calendar and see if someone has put up some notes about that. :-)

triple-d said...

You have been very helpful. You missed my one post/question about whether my birthday and anniversary calendar that I import can calculate what birthday or anniversary it is this year? That does raise another question as well. Once I import this calendar, will it show up as a "my calendar"? I.E.when my sixth grandchild is born in January, can I add that to this calendar, or will I need to add it as a new event? Should I be asking one question per post?

Laura Gibbs said...

I don't do recurring events in my calendar, so I cannot help you there. Check the Google Calendar help to see what they say about recurring calendars since the birthday comes on the same day every year. That's not something I do with my class calendars.

tarakayan said...

Thanks for the CVS template. I was trying to do this using: http://cmichae.acm.jhu.edu/myearncal/MSFT+ANF+JNJ+WMT+GOOG+AMZN+STX+INTC+AAPL
but didn't work.

Is there any way we can download the data (showing stock name and the date) directly from financial websites (several like briefing.com offer this). Otherwise, we need to key in data in csv like stock symbol along with earnings$ or % for ready reference in each date.

Laura Gibbs said...

That is a pretty unusual type of project, tarakayan - I've not tried anything like that. I'd check the Google Help forum and see if anyone there can assist you. My guess is that you really want something more like a simple database for that, rather than trying to use a calendar system which is really event driven.

tarakayan said...

Thanks Laura for your comments. We have several sites, eg. yahoo finance (http://biz.yahoo.com/research/earncal/20111017.html) gives this data which is just enough! As we spend time on calendar, thought it will be great if you can just track a few symbols of your choice. Shankar

deadmanbt said...

Hi Laura, I cannot get the csv file you have created to work at all. Can you think of any thing that may be going wrong? I continue to receive the "zero events were imported" note. I have read through all of the help sections I could find on google and still cannot figure it out! I have filled out all of the cells that are required and still nothing. Help with this matter would be greatly appreciated

V Shankar said...

deadmanbt,

csv file works well if you have all the fields (column heading) and data correctly done..Check google calender options too...I will send you one I created, but it's pain filling in data (stock symbol & dates). I wanted to add eps estimate along side and so created the csv file accordingly.

It will be great if we have this in excel format, just like the way yahoo spreadsheets can give stock info updates (ask, bid, last, inc, vol, eps, pe etc.)

Amanda Beck said...

Is there a way to import "guests" from the excel CSV file to the google calendar? I just don't want to manually go through and invite people once the calendar has been imported to Gcal.

Laura Gibbs said...

Hi Amanda, you'll need to check Google Calendar Help for that - since my calendars are all public, I haven't used any of the sharing features, guests, etc.

Big A said...

Hello. I am a novice at best with Excel. Is it possible for me to create vlookups for the specific columns you have in your sheet so that information is generated?

Laura Gibbs said...

I don't really understand your question. The spreadsheet is simply a way to get the data into Google Calendar; it is just a csv text file - no special Excel features are allowed.

V Shankar said...

I guess he is trying to set up a link to .cvs file.

Iris said...

Cools, thanks a lot! I didn't know Google Calendar had such function. I am incorporating all the work events into one Calendar. Niccceeee!!!

don said...

hi laura,
would you know what header to use with reminders. i am using an export from outlook 2010. and is much the same as your online template. the columns i require(mostly) use covered, but outlook has only one reminder,goolge calendars have two! popup and email, and i am try to not have either with my csv import, but dont know what headers to use?, can you help please?

Laura Gibbs said...

Hi Don, you'll need to check the Google Calendar help area - I don't use Outlook and I just use the three most basic columns for my calendar, nothing fancy! I'm not really using it as a calendar but more like glorified announcements for my classes. :-)

cwibowo said...

I cannot import the csv file into google calendar, the message said: Processed zero events.

Please help, I have enormous list dates that need to compile.

Thank you

Laura Gibbs said...

There are various tips on troubleshooting problems with importing events at the Google Calendar help pages:
http://support.google.com/calendar/?hl=en

chrismawibowo said...

Thank you, it is all about how you set the excel. I got it! Thank you ;)

Depends who's asking... said...

Just wanted to say thank you for the clear instructions and sample file. Just imported my university calendar into my google calendar.

Stanley Denman said...

Does not work for me. I get a message after the import giving me the number of events that have been added, which corresponds to my row number in the CSV file, but nothing is visible in the calendar. I then used your CSV file and got the same thing (no resulting Event entries)

Laura Gibbs said...

If it says the events were added, it sounds like the problem is how you are viewing your calendar and/or in the events data, not in adding the events. And are you sure you have the year correct in the events for example? I know it works; I did this at the beginning of the semester as I do every semester.

Sir Duke said...

That worked great, thanks so much for your help!

MD said...

Can we upload a .csv file every day into google calendar automatically using some schedular? if any plz help how ?

Laura Gibbs said...

There are lots of ways to do that but you need a computer programmer to help you; that has nothing to do with how Google Calendar operates - it's all about the programming skills you have to build the scheduler.

Stephanie said...

Thank you!

Mekey said...

Hi,
Thanks for the help. Also I would like to know how the Reminder columns works, coz I want the notifications for every event I add in calender.

Regards
Omkars

Mary W said...

Very helpful, thanks! Was looking for a way to import my boys' baseball schedules. It worked great except it imported them all as "All Day Events" even though I had a start time and end time column, as well as "False" in the All Day Event column (I used your template). Any thoughts on what I did wrong? Thanks, Mary

Laura Gibbs said...

Hi Mary, since I have never done anything other than all-day events I don't have any advice to offer but Google Calendar has a very active user forum. I am sure you can find some help there! (Just click on the Help thing in Google Calendar itself.)

Sara Whipple said...

For repeating events do the import for one year and then manually set each event to recurr.

Laura Gibbs said...

I wish I could take advantage of that! For my class calendars, the dates change every year depending on the start of the semester, and for the Roman calendar, the leap year wrecks everything - the way the Romans do their calendar makes leap year weirdly complicated (they name the dates by counting backwards from the upcoming first of the month). :-)

santiagopm88 said...

This is terrific, thank you!

Phill Klamm said...

Thank you. You just save me at least 30 minutes.

Amy Terry said...

Excellent post. I liked it a lot.

Jim said...

If you want to add guest lists to your event, there is a tool for that: http://excel2calendar.appspot.com