I recently got a file with numerous dates to be “marked in my calendar” – the original file was a PDF file but what I really wanted was to have this data put into my calendar so that I have access to it when I look at my calendar. Last year, I had done this manually by creating a CSV in Excel and then importing it into the Outlook desktop client, as explained in this article at Tech Republic. We use Office 365, so this synchronizes to the online calendar and then on to all my devices. The online calendar in Office 365 only allows importing ICS (iCalendar) files and since that is what I use most of the time, I decided to see how I could create this file when I got the list of dates for this year. It’s also convenient to mail this file to others who can use it to import it into their calendars – making the whole process worth the effort.

Since I use Ruby for most such tasks, that was where I went – the whole process is quite simple and relies on the aptly named icalendar gem – the examples on the site are very good and help you get started quite quickly. Just remember to gem install icalendar or add it to your bundle.

The only issue that I faced was in creating events that showed without a time stamp. My first attempt to create the event ended up as an event that ran from 00:00 till 11:59pm. Since the calendar I was dealing with was holidays in another office, it wasn’t practical to block my calendar for the full day. I needed it to show up as an event without any specified time as below. In Outlook, this is done by marking the event as an “All day” event.

On the other hand, if you set the time from 00:00 – 11:59pm, it will show the whole day as busy – as shown below.

The icalendar gem is fantastic and has lots of options and features. The key thing here is to make sure that the output meets the following requirements:

• Start date: needs to be the date when you have the event (e.g., 2019-01-14 for 14th January 2019) without any time specified
• End date: needs to be the date 1 day after when you have the event (e.g., 2019-01-15 for the event on 14th January 2019), again without any time specified

With this in place, you will get the output that you desire – and when you import the resulting file into Office 365 online (or any icalendar-compatible application), it will show up correctly. The Ruby code below shows how to do this wih some simple sample data. Specifically, see lines 12 and 13 that use Icalendar::Values::Date.new to ensure that the date is returned without any timestamp.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
require 'date'
require 'icalendar'

# Create a calendar with an event (standard method)
cal = Icalendar::Calendar.new

# Parse through some events and create an ical
events = [{dt: '2019-01-07', desc: 'Event 1'},{dt: '2019-01-08', desc: 'Event 2'}]
events.each {|ev|
  dt = Date.parse(ev[:dt])
  event = Icalendar::Event.new #our new event
  event.dtstart = Icalendar::Values::Date.new(dt)
  event.dtend   = Icalendar::Values::Date.new(dt + 1)
  event.summary = ev[:desc]
  cal.add_event(event)
}

cal_string = cal.to_ical
puts cal_string

There is one other minor annoyance – even though the item does not occupy the whole day any more, Office 365 assumes that since you’re importing these events, it treats them as full day events for which you will be busy and shows a line down the side of the day to indicate that.

If you right click on the event and mark it as “Show as > Free”, you will see that the event does not occupy your full day. This is set on the basis of an attribute called TRANSP (for Transparency) and can be set explicitly. I prefer it to show me as available – this is equivalent to transp = 'TRANSPARENT'. Make the change as below (in Line 15) and it works fine!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
require 'date'
require 'icalendar'

# Create a calendar with an event (standard method)
cal = Icalendar::Calendar.new

# Parse through some events and create an ical
events = [{dt: '2019-01-07', desc: 'Event 1'},{dt: '2019-01-08', desc: 'Event 2'}]
events.each {|ev|
  dt = Date.parse(ev[:dt])
  event = Icalendar::Event.new #our new event
  event.dtstart = Icalendar::Values::Date.new(dt)
  event.dtend   = Icalendar::Values::Date.new(dt + 1)
  event.summary = ev[:desc]
  event.transp = 'TRANSPARENT'
  cal.add_event(event)
}

cal_string = cal.to_ical
puts cal_string

Finally! This is how I want it!

And that’s all there is to it – hope this helps!