Pilot software - datebook to csv conversion

I wrote this utility, dtbk2csv, to simplify the weekly ritual of filling out my timesheet. I had recorded all my appointments and activities in the Pilot datebook, but when I went to export the data it was clear that there was no way to accomplish it with the Pilot Desktop program.

I had been in the habit of recording my activities in Microsoft's Schedule+, exporting them each week into a comma delimited file, importing them into Excel, sorting and subtotaling them, then using the data to record my hours in my client's system and fill out a timesheet for my company.

I wrote this application to extract the important information from the datebook.dat file on the PC. It produces a file with date, start time, end time, client, and description. The client field is not supported in the Pilot datebook, so I just put in the client code plus a ':' in the description. If it exists it is extracted as a separate field.

Operation

Run dtbk2csv in the same directory as your datebook.dat file. It will produce a csv file on standard out which can be redirected to a file. This can be directly imported into Excel as a comma delimited file. For example: set TZ=EST5EDT cd \pilot\username\datebook dtbk2csv > datebook.csv Currently it is problematic running it from a Program Manager icon as the redirection is lost. You have to create a ".bat" file to run it: @echo off set TZ=EST5EDT cd \pilot\username\datebook \pilot\dtbk2csv > datebook.csv Then run the batch file from a Program Manager icon. Setting the "run minimized" flag makes things a bit nicer.

Planned for next release:
sort into date/time order
time adjustment

Changes in version 0.5:
fixed bug in handling large files
changed to localtime fixes most timesync problems

Changes in version 0.4:
fixed problem with files larger than 64K

Latest options in version 0.3:
-a - dump all fields
-o - output file (stdout is default)
-i - input file (datebook.dat is default)
-d[012] - date format (0=mm/dd/yy (default), 1=yy/mm/dd, 2=dd/mm/yy)

I doubt that this system is without bugs. Some areas to watch for:

Repeating appointments are not handled correctly: they only appear for the day they were entered and any days they were modified for the current appointment only. You can change the time for the current appointment, then move it back to ensure that it appears. Changing the current appointment separates the entry from the rest of the entries.
The program dumps the entire file rather than a specific date range
Descriptions with carriage returns are exported as several lines
There have been reports of problems with times being off by an hour. Apparently this is a problem with the Pilot Desktop program and Windows. The details are at USRobotics. There is another note about a similar problem with the ToDo application.
The current version (0.5) uses local time which should work well on 95/NT if you have set the TZ variable. This set in the Control Panel:System:Environment panel. For Eastern time TZ would be set to EST5EDT.
Be sure to save your file first if you use the Pilot Desktop: it doesn't write to the disk immediately.

I'm sure there are more.

Here are some likely improvements:

Generate a timesheet report directly.
Select or report on a specific client.
Select a particular date range.
Select a particular week.
Select a specific list of fields.
Custom field separator.
Use a file of rates to generate a bill.
Handle recurring appointments better.
Remove carriage returns from the description.
Sort the records into date/time sequence.

If you need to write data from another PIM into your Pilot datebook you should first contact the PIM manufacturer: most of them have written conduits to synchronize the desktop with the Pilot. Otherwise you might try Consult US who sells libraries for reading and writing Pilot databases. I cannot tell you much more than that as I have not used their products.

There are also two other tools I know of. The first is a CSV-to-Datebook or ToDo conversion which is possibly the best tool:

ConvDB

Here's some Java tools:

PilotBean

Please send me any suggestions or comments.

A lot of people have asked me for the source code and the description of the file format. I have decided to keep the source code to myself for the time being. Here is a link the file description I started from. It is excellent:Nico's Homepage

dtbk2csv.exe is Freeware.

Created: Saturday, March 22, 1997, 09:50 Last Updated: Saturday, May 17, 1997 - 18:24:35