Creating CSV file for automatic calendar events import in Outlook/Google Cal
Today I had to create a bunch of Outlook 2016 appointments in my calendar. I wanted to avoid as much as possible the manual, one-by-one creation of the items, so I decided to lurk around for a method, which would allow me to do this work easier.
Quick Google search led me initially to the article “Create Appointments Using Spreadsheet Data”, which was showing up how this can be done with VBA macros. Although this was cool, programmatic way to accomplish the task, I was looking for the KISS principle method: just plain CSV import.
A bit more search and I was all set. The article “How to import a Calendar from Excel to Outlook” described quite straight forward process. However, I found a few discrepancies from what was desrcibed there, so I decided to sum up the differences I encountered, so the next time it’d be easier for me (and probably for my readers) to accomplish this task with Outlook 2016+.
The first difference was that there’s no XLS import in Outlook 2016. I had only CSV. This made useless to define namespace (as the article suggests), because CSV does not export that information.
The second problem, which was not outlined in the article, was which other fields I could use, in order to have more complete data (I needed Category, All Day Event, etc.). The article “Importing stuff into your Outlook Calendar (or Tasks) from Excel” led me to list of all common fields:
Subject, Start Date, Start Time, End Date, End Time, All day event, Reminder on/off, Reminder Date, Reminder Time, Meeting Organizer, Required Attendees, Optional Attendees, Meeting Resources, Billing Information, Categories, Description, Location, Mileage, Priority, Private, Sensitivity, Show time as.
The last issue I had was to find out how to do the Out of office status of the events. Each single event had to be marked as “Out of office”, so this information had to be present in the import file. This article informed me what the values of “Show Time as” column had to be, in order all this to work:
- 1: Tentative
- 2: Busy
- 3: Free
- 4: Out of Office
My final CSV looked like this (showing just the first row with column names, and the first data row):
Subject,Start Date,End Date,All day event,Categories,Private,Show Time as
This is All Day Event,1/15/2017,1/15/2017,1,MyCategory,1,4
If you save the above code fragment as CSV, the import in Outlook 2016 would be pretty straight forward. It’ll create an all day event with title “This is All Day Event”, marked as Out of Office, on Jan 15, 2017, with category name “MyCategory”.
The coolest thing was when I tried this CSV for Google Calendar too. It worked there as a charm, with the following exceptions:
- It added two default reminders for each day (sick! Why, Google?)
- It did not respect the Category name (I guess this is fair)
- It did not respect the out of office status (well, Google just does not support that, right?)
Except from this, the data was correctly imported there too.