How best to convert iCal 2.0 to CSV, and how to FreeMind?

See also the post “Wie kann ich am besten die Journal-Einträge von korganizer in ein anderes Format konvertieren?”, here in this blog.

The source file is here an iCalendar 2.0 file, created by kaddressbook:

BEGIN:VCALENDAR
PRODID:-//K Desktop Environment//NONSGML KOrganizer 4.2.4//EN
VERSION:2.0

The task is, ultimately, to convert this iCal file to a FreeMind mindmap, using one node per entry, with a special text formatting (task / appointment text prepended with the date and category and the like). This is best done by creating a text file with one task / appointment per line, if you want separated by empty lines, and subtasks indented by two spaces (or four, six etc. for deeper sub-levels). Pasting such a text file by Ctrl+C and Ctrl+V into FreeMind will create a hierarchical node structure.

However, how to create such a file from iCal without too much manual effort?

The following tools come to the mind that could do the conversion job:

• KOrganizer
• ical2sqlite (available via the Ubuntu Jaunty package repositories)
• Evolution
• Mozilla Sunbird
• phpicalendar (available via the Ubuntu Jaunty package repositories)
• ical2html-0.4 (developed by the W3C)
• iCalcreator 2.2 (but needs programming)

KOrganizer: The only export format is a HTML table for appointments and / or tasks. This is useful already, but would require manual or scripted conversion to the text format. Also, the “done on” timestamps of tasks do not appear in the HTML list.

ical2sqlite: Calling “ical2sqlite file.sql” creates a file Calendar.sqlitedb with table structures, but only data in the Event table, no text data at all (check with “sqlite3 Calendar.sqlitedb”, enter “.dump” there).

Evolution: Copy and paste to a text editor does not work at all, as evolution seems to use its own internal format only. There are no export functions for iCal tasks or appointments. You can print to PDF, but that takes extremely (!) long for many tasks. Better, use the print preview. There you can mark and copy the text, and paste it as a text file with one task per line. The problem is however that only the task names are contained then, no categories, “done at” times, due times etc.. To remove tasks from Evolution after you’re done with it, do “rm ~/.evolution/tasks/local/system/tasks.ics”.

Sunbird: You cannot copy tasks from the task list and paste them into a text editor, as Sunbord uses an internal format. You can use “File -> Export Calendar …” or “File -> Export Selection …” and select “Outlook Comma Separated Values (*.csv)” as the format. But this only exports calendar events, or nothing if you select tasks and call “Export Selection”. There is a function in the task’s context menu to convert them to events (which could then be exported to CSV by Sunbird), but this creates message windows for every tasks, and all at once, so that you can crash X by doing that. Also, Sunbird is really slow when dealing with large iCal files (200 entries or more). In all, it is no solution here.

phpicalendar: It is installed to /usr/share/phpicalendar, so the simplest way to make it running is a symbolic link in you localhost web root directory, pointing to that install directory. I could not get it running with my calendar data (I basically did: enabled the admin page with no authentication in default_configuration.php, made the directory world-writable, and tried to upload a calendar using the admin page). And if it would work, it would only be able to display calendar events, not export them. As it does not use a database, one cannot get the data in table format from there either.

The following finally worked:

Open the iCal file in gedit (or another editor that works well with large text files) and by search&replace convert VTODO entries to VEVENT entries. For that, you have to:

• replace “BEGIN:VTODO” with “BEGIN:VEVENT”
• replace “END:VTODO” with “END:VEVENT”
• remove “DUE” lines (assuming you only deal with completed tasks; else replace “DUE;VALUE=DATE:” with “DTEND;VALUE=DATE:” and omit the conversion of “COMPLETED” below)
• replace “COMPLETED:” with “DTEND;VALUE=DATE:”
• remove the time after the original COMPLETED: values, i.e. strings like “T130100Z”
• remove “RELATED-TO:” lines (this breaks the connection to parent tasks, which is not possible for events)
• remove “PERCENT-COMPLETED:” lines
• insert “DTSTART;VALUE=DATE:” lines with the same value as in the DTEND lines; these are needed for VEVENT entries to be correctly parsed, displayed or exported; you can achieve this, e.g. in kate, by a regexp replacement of “DTEND;VALUE=DATE:([0-9]*)” with “DTSTART;VALUE=DATE:1nDTEND;VALUE=DATE:1”; also, it might be enough if events only have a start value, in which case they will be assumed to cover the whole day
• you do not need to remove resulting empty lines within entries

You can also create a new iCal file that only contains the VTODO entries before you do that conversion above. Use a text editor for this; in a file created by korganizer, VTODO entries come first, then VEVENT, then VJOURNAL, so you can easily separate them. And like with the VTODO entries, do with the VJOURNAL entries if you have them.

So now, your whole iCal file should consist of VEVENT entries only. Open this file in Sunbird and use the “File -> Export Calendar …” feature to export it to a CSV file. Open that CSV file in a spreadsheet application like OpenOffice.org Calc (use your local, non-Unicode encoding). This works perfect, even where the fields contain line breaks within themselves.

However, before you open the CSV files in OpenOffice.org, set its “locale scheme” in the settings to “English (USA)”, or else it will irreversibly apply other date formatting to the imported value (and fail on some, generating a bunch of manual effort here). After the import, you can of course format the date values any way you like.

Then, after you have the data in OOo, use OOo functions (adding and deleting columns, adding text separator columns etc.), export again to CSV (using a space as field separator and no text separator) and fine-tune with a text editor that can replace using regular expressions (like kate). You should do these steps separate with files that are made from events, todos and journals, as you will want different formatting in the end.