Using Google Calendar as a timetracker for your customers/ convert-import-export iCal /ics/google calendar to text/csv file

• IcalToCSVPipe.zip (108,58 KB)

Since I use a single shared Google calendar for every customer I have, so that all the customers can see when I worked on their projects, I needed an easy way to import each calendar into my invoice program.

I did it a few times manually in the beginning, but since this was too much of a hassle, I decided to write a small program for it.

The program is very simple and reads files from the stdinput and outputs to stdout. I use the DDay.iCal library for this.

This is the source for the program (pretty straightforward):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ic = DDay.iCal;
namespace ICALToCSVPipe
{
    class Program
    {
        static void Main(string[] args)
        {
            var c = ic.iCalendar.LoadFromStream(Console.OpenStandardInput());
            foreach (ic.Components.Event occ in c.Events)
                Console.WriteLine("{0:yyyy/MM/dd  HH:mm} - {1:HH:mm} : {2:0.00} : {3}"
                    ,occ.DTStart.Local 
                    ,occ.DTEnd.Local  
                    ,occ.Duration.Value.TotalHours 
                    ,occ.Summary);
        }
    }
} 

You can find the program in the attached zip.

In order to convert a calender, I use the following steps:

1. Export the binaries to a folder on your hard drive, i.e. "c:\tools" for example
2. Login to Google Calendar
3. Choose Settings/Calendars
4. Click on "Export", and extract the zip files to the same folder as the program, i.e. "c:\tools"
5. Open a command window ( click Start/Run - type "cmd" +<Enter>)
6. Go to the program folder, i.e. type "cd c:\tools" + <Enter>
7. Type the following: ICALToCSVPipe.exe < xxx@group.calendar.google.com.ics |sort> calendar.txt. You do not need to type the full filenames, just type the first few letters of each filename and press the <Tab> until you hit the right filename
8. Now type Notepad "calendar.txt" in order to view the file.

The resulting output should look like this :

2009/09/01  10:30 - 11:15 : 0,75 : probleem ouwe hoeve - server traag
2009/09/01  11:45 - 12:00 : 0,25 : 
2009/09/01  12:45 - 13:15 : 0,50 : Versie 1.3.3461 versturen
2009/09/02  09:30 - 09:45 : 0,25 : tel jose protocol
2009/09/02  13:30 - 15:00 : 1,50 : Eigen/Gratis
2009/09/04  11:00 - 12:30 : 1,50 : Verwerken nieuwe settings van server
2009/09/04  13:15 - 13:45 : 0,50 : 
2009/09/04  14:00 - 16:00 : 2,00 : SharedTables/settings
2009/09/07  13:30 - 18:15 : 4,75 : v1.3.3462
2009/09/08  11:00 - 11:45 : 0,75 : Tel Jose todo
2009/09/08  13:00 - 18:00 : 5,00 : v1.3.3463 : Aanpassen van XML parse 
				routines en implementeren van getreceiptinfo cache
2009/09/10  11:30 - 12:00 : 0,50 : overlopen volgende punten
2009/09/10  14:00 - 16:45 : 2,75 : 
2009/09/15  17:00 - 17:30 : 0,50 : Testdata ouwe hoeve
2009/09/21  09:00 - 12:00 : 3,00 : v1.3.3464
2009/09/23  09:00 - 12:00 : 3,00 : debug packets
2009/09/23  12:30 - 13:30 : 1,00 : v1.3.3465
2009/09/23  15:30 - 18:30 : 3,00 : tel jose getreceiptinfo
2009/09/25  10:00 - 12:00 : 2,00 : 1.3.3465
2009/09/25  14:00 - 15:00 : 1,00 : 1.3.3465
2009/09/25  16:00 - 17:30 : 1,50 : 1.3.3465
2009/09/25  20:00 - 22:00 : 2,00 : 1.3.3465
2009/09/29  15:00 - 17:30 : 2,50 : bakwijzen aangepast...
2009/10/01  09:15 - 10:45 : 1,50 : 
2009/10/01  11:00 - 12:00 : 1,00 : 
2009/10/01  12:30 - 16:30 : 4,00 : 
2009/10/01  17:00 - 17:45 : 0,75 : Oplevering 1.3466

That's all folks!!! Enjoy!!