Convert ICS to CSV

A calendar application is built for looking forward, and it is poor at looking back in aggregate. How many hours went to one client last quarter. Which recurring meeting has quietly grown to fourteen people. How much of a week is booked before anybody schedules anything. Whether a room was used on the days it was blocked. None of those are a view in a calendar and all of them are one pivot table.

So the export exists to be counted. That framing matters because it decides which of the things this conversion cannot do are serious: losing an event’s colour is nothing, and losing the fact that an event repeated is everything. The next two sections are about that.

This is the part to read before trusting any total. In the calendar format a recurring series is stored as one event carrying a recurrence rule — the daily stand-up for the past year is one `VEVENT` with an `RRULE` saying weekly on weekdays until a date, plus perhaps a handful of `RDATE` additions and `EXDATE` exceptions for the days it was cancelled.

The columns produced here are the event’s own properties: summary, start, end, location, description, organiser, status, identifier and attendees. `RRULE`, `RDATE` and `EXDATE` are not among them, so the series contributes exactly one row, showing the first occurrence’s times. A quarter’s worth of stand-ups is one thirty-minute row. Anything that sums the duration column will be wrong by however much the recurring events actually consumed, which in most working calendars is the majority of the time.

The fix is upstream rather than downstream, and it is easier than it sounds. Both Google Calendar and Outlook, when asked to export or print a date range rather than a whole calendar, emit the individual occurrences instead of the rule — so exporting "this quarter" gives you a file where every stand-up is its own `VEVENT` and every one becomes a row.

Where that is not available, expansion is a scripted job rather than a spreadsheet one: Python’s `icalendar` with `dateutil.rrule`, or the `ical-expander` package for Node, will walk a series between two dates and produce the occurrences. Either way, do the expansion while the file is still an ICS. Once it is a table the rule is gone and nothing can reconstruct it, because the table never carried it.

In order: `summary`, `start`, `end`, `location`, `description`, `organizer`, `status`, `uid`, `attendees`. Every event produces a row with all nine, empty where the event had no such property, so the table is rectangular regardless of how uneven the source is.

Two of them repay attention. `status` distinguishes confirmed, tentative and cancelled events, and cancelled ones still produce rows — filter them out before counting, or a cancelled all-day workshop will be counted as time spent. And `uid` is a stable identifier the calendar assigned, which is the only reliable way to deduplicate when two exports overlap; sorting by it and removing duplicates is far safer than matching on summary and start.

Timestamps are rewritten from the compact calendar form into ISO 8601, so `20260301T080000Z` becomes `2026-03-01T08:00:00Z`, which every spreadsheet recognises. What is not done is any shifting between zones.

A value written in UTC keeps its trailing Z. A value qualified by a `TZID` parameter — `DTSTART;TZID=Europe/Berlin:20260301T080000` — is local to that zone, and it is written through as `2026-03-01T08:00:00` with no Z and no offset. Converting it would require a time-zone database, and assuming UTC would move every appointment by an hour or more. The result is a column where some cells declare their zone and some do not, and the table does not say which zone the others were in. If you are totalling durations that is harmless, since start and end share a zone. If you are comparing times across a distributed team it is not, and the zone has to come from the source file.

An all-day event is written with date values rather than timestamps, and it converts to a plain `2026-03-01` in the start and end columns with no time part. That is unambiguous and it sorts correctly, which is more than can be said for most date handling.

The catch is in the format rather than the conversion: for a date value the end is exclusive. A one-day event on the 3rd is stored as starting on the 3rd and ending on the 4th, and a three-day workshop from the 3rd ends on the 6th. Subtract a day before reporting a date range to a person, and be careful with any formula computing duration — the arithmetic is right for the format and wrong for the sentence somebody will write from it.

The calendar format allows an event to state a start and a duration rather than a start and an end, and some producers use that form for everything. Only the end property is mapped to a column, so those events arrive with a populated start and an empty end.

It is quiet rather than damaging, and it is very easy to miss in a large table because empty cells look like optional data. If the end column is sparse, that is the reason — sort by it and see whether the blanks cluster around one source. Recovering the value means going back to the ICS and reading the duration property, which is a job for a script rather than a spreadsheet.

An invitation stores an attendee as an address in the value and a display name in a parameter. The readable half is the one kept, so the cell holds "Anna Weber" where the invitation carried a common name and `[email protected]` where it did not. Several attendees are joined with a semicolon and a space into a single cell. The organiser is treated the same way, in its own column.

That is a deliberate choice over inventing `attendee1` through `attendee9`, which would truncate the tenth and leave nine mostly-empty columns for everybody else. Counting attendees is then a formula on the cell rather than a count of columns — in a spreadsheet, the length of the cell minus the length with semicolons removed, plus one. Splitting the cell into rows is a Power Query step or a two-line script if you need one row per person.

The calendar format carries more than appointments. Tasks are `VTODO`, journal entries are `VJOURNAL`, free-and-busy blocks are `VFREEBUSY`, and alarms sit inside events as `VALARM`. Only `VEVENT` is read here.

A file containing nothing but tasks therefore stops with "No calendar entries were found in this file", which is accurate rather than a failure — an export from a reminders application is not a calendar of events even though it shares the extension. Alarms, attachments and per-attendee response status are dropped from the events that are read, for the same reason recurrence is: a table has no shape for them and inventing columns would be worse than omitting them.

Import rather than open. In Excel, Data then From Text/CSV, and set the start and end columns to Text on the way in unless you want Excel to reinterpret them — its date parsing will happily strip the offset, reformat according to the machine’s locale, and turn an ISO string into something that no longer sorts as text. The identifier column should be Text as well.

Descriptions are the other thing to watch. Meeting descriptions routinely contain commas, quotation marks and real line breaks — a pasted agenda, a dial-in block — and all three are handled by wrapping the value in quotation marks with internal quotes doubled, which is the RFC 4180 convention every competent importer reads. A naive split on commas in a shell pipeline will not, so use a real CSV reader or convert to TSV instead.