Convert your PalmOS Calendar to iCal

Modern versions of Palm Desktop keep your data in highly proprietary databases and file formats. In order for it to be of any use to you, you will have to convert the data to something more "industry standard". In this example, we will convert Palm Desktop’s Calendar (PD version 4.1.4) into the iCal format, which is much more popular with other email programs, PIMs, mobile phones and websites.

It should be noted that I have tried to detail my steps as much as possible, but, this procedure is not for the faint of heart. Some computer knowledge is a minimum, including Windows command lines, various applications, and Microsoft Excel/OpenOffice.org Calc. Perhaps most important is good knowledge of Find/Search and Replace functions of your favorite text editor and lots of patience for trial and error. Some other caveats include the fact that repeating events are not handled well (if at all) and these instructions are Windows specific, but I’m sure you can adapt them for Mac and Linux platforms.

So, backup all your data at every step, and follow the instructions below carefully. It worked for me, and if it works for you, then you too can achieve "data portability" - YMMV

Part A - Get the data out of your Palm and Palm Desktop

1. Make sure you have HotSynced your Palm with your Palm Desktop to make sure the data is up to date. If you have difficulty HotSyncing your Palm, make sure you check Fixing HotSync Problems.

2. From the Palm Desktop, click on "Calendar".

3. From the Menu > "File" > "Export".

4. In the "Export As" box, make sure you select "All Records" and "Datebook Archive (.dba)" as your "Export Type". Note that "Calendar Archive" is also a .dba file, but be warned, the file extension may be the same, but the internal file format is very different.

   

Part B - Convert from "Datebook Archive" (DBA) to "Palm CSV" (CSV)

1. Download the datebook_csv.zip package and unzip it to the same folder as your Datebook Archive.

2. Open a Command prompt and navigate to the folder containing the DTBK2CSV.EXE from the datebook_csv.zip package and your Datebook Archive

3. Set your time zone, usually it is a three letter code followed by a modifier from GMT. For example, Singapore is SST-8, while US Eastern is EST+5. To know more about setting your timezone information, you can check out the GNU C Library.

4. Run the conversion program with the following command line:

   DTBK2CSV.EXE -i<your-dba-file.dba> -oOutput.csv -d2

5. You should now have a converted "Palm CSV" file. Below is a screenshot of my own conversion of 1700+ Calendar records over 9 years from 1998 to 2007.

   

Part C - Convert from "Palm CSV" (CSV) to "Outlook CSV" (CSV)

Unfortunately, at this stage, it becomes complicated. The "Palm CSV" does not use a common enough CSV table structure. We will need to convert to the Microsoft Outlook CSV structure, which is more common. It is important to note that the "Palm CSV" has the following table structure:

"Date","Start","End","Hours","Description"

but the "Outlook CSV" has the following more complex table structure:

"Subject","Start Date","Start Time","End Date","End Time","All day event","Reminder on/off","Reminder Date","Reminder Time","Meeting Organizer","Required Attendees","Optional Attendees","Meeting Resources","Billing Information","Categories","Description","Location","Mileage","Priority","Private","Sensitivity","Show time as" 

Clearly some work is required! To convert your "Palm CSV" into an "Outlook CSV", you must open your CSV in MS Excel or OpenOffice.org Calc. For this explanation, I will use Excel as the example. I assume you know enough about Excel/Calc to be able to create columns, Search/Find and Replace, move data around, reorganise table structures and import/export CSVs.

1. Open your CSV in Excel.

   

2. Be careful about "Descriptions" that are more than one line long, try to edit them to keep them all in a single line. Look out also for any "misformatted" lines in Excel during the CSV import. This is usually caused by "Descriptions" that have commas in them and are mistaken by Excel to mean the next column over. Edit them so that they are all placed in the proper columns.

3. Watch out for all dates that are 100 and above. These need to be edited to reflect the year 2000 and above. Year 100 would be 2000, 101 would be 2001, 102 is 2002 and so on.

4. You need to make sure that your "Date" is in the correct format. Click on the "Date" column and choose Menu > "Format" > "Cells". Choose "Custom" and "dd/mm/yyyy" as the "Type" (type in the type if you can’t find it).

5. You need to make sure that your "Start" and "End" time is in the correct format. Click on the "Start" and "End" columns and choose Menu > "Format" > "Cells". Choose "Custom" and "h:mm:ss AM/PM" as the "Type" (type in the type if you can’t find it).

6. Move the "Description" to the first column, and rename it "Subject".

7. Delete the "Hours" and "Client" columns, they are not needed.

8. "Start" becomes "Start Time" and "End" becomes "End Time".

9. "Date" becomes "Start Date"

10. Copy "Start Date" and paste it as "End Date" between "Start Time" and "End Time". If your event crosses the 11 midnight boundary, you will have to check/edit the "End Date" and "End Time" by hand to make it correct.

11. You can then create the remaining columns, from "All day event" to "Show time as".

12. For the data in the new fields, "All day event", "Reminder on/off" and "Private" can be set to "FALSE" while it should be safe to leave the rest blank. Alternatively, you can edit as needed.

    

13. Next, go to Menu > "File" > "Save As" and choose "CSV (Comma delimited) (*.csv)" as the file type and save the file.

14. Now, here comes the really difficult part! You must open the CSV file in a text editor and make sure there are quotes before and after each field string, including the header line. If you do not do this, you will not be able to proceed to the next step. Here is an example of some wrong strings:

    Subject,Start Date,Start Time,End Date,End Time,All day event,Reminder on/off,Reminder Date,Reminder Time,Meeting Organizer,Required Attendees,Optional Attendees,Meeting Resources,Billing Information,Categories,Description,Location,Mileage,Priority,Private,Sensitivity,Show time as
    Attend IT Course,11/11/2006,9:00:00 AM,11/11/1999,5:00:00 PM,FALSE,FALSE,,,,,,,,,,,,,FALSE,,
    Sales Meeting,06/12/1999,12:00:00 AM,06/12/1999,12:00:00 AM,FALSE,FALSE,,,,,,,,,,,,,FALSE,,

    Here are the correct strings:

    "Subject","Start Date","Start Time","End Date","End Time","All day event","Reminder on/off","Reminder Date","Reminder Time","Meeting Organizer","Required Attendees","Optional Attendees","Meeting Resources","Billing Information","Categories","Description","Location","Mileage","Priority","Private","Sensitivity","Show time as"
    "Attend IT Course","11/11/1999","9:00:00 AM","11/11/1999","5:00:00 PM","FALSE","FALSE","","","","","","","","","","","","","FALSE","",""
    "Sales Meeting","06/12/1999","12:00:00 AM","06/12/1999","12:00:00 AM","FALSE","FALSE","","","","","","","","","","","","","FALSE","",""

    

Clearly, you need to have a strong grasp of Search/Find and Replace and some good typing skills. If you know how, you can use Excel to add extra columns with the quotes and do a lot more search and replace in the text editor at this stage. Check carefully and clearly. Wrongly formatted lines will fail to pass the next step!

Part D - Convert from "Outlook CSV" (CSV) to iCal Format

This last step requires you to download a program called Sunbird. You should use Version 0.2 of Sunbird (Yes, I know about Sunbird 0.3+ and Lightning, etc, but if you want iCal, you better stick to 0.2). You can download Sunbird 0.2 from Mozilla’s website.

1. Unzip Sunbird 0.2 into a folder on your PC.

2. Go to the Sunbird folder, and double-click on "sunbird.exe" and Sunbird will run.

3. From the Menu > "File" > "Import", make sure you select "Outlook Comma Separated" as the file type and choose your "Outlook CSV" file.

   

4. Sunbird will ask you about the field mapping, make sure that the fields are mapped correctly - if you had done all the text editing correctly, it should be fine to just hit "OK".

   

5. There may be some complaints about date formats, but it should be fine to just hit "OK".

6. You should now see a box saying that Sunbird will import "XXX new event(s)". If this number does not match your CSV file, this means that one or more lines of data is not formatted properly. Hit cancel and edit the "Outlook CSV" file and make sure the quotes, commas and lines are all correctly formatted. Below is my import of 1700+ events.

   

7. After "Import All" completes, you should check to see all your events are there. Note that if you left "Reminder on/off" as blank instead of "FALSE" in the earlier steps, you will have a lot of alarms trigger right after the import is complete. The first time I did this, I had 1700+ alarms trigger in one go. Luckily, Sunbird has a "Dismiss All" button.

8. If everything is OK you can exit Sunbird. Edit as needed if it is not.

   

9. Go to the "C:\Documents and Settings\\Application Data\Mozilla\Sunbird\Profiles" where is your user name. In this folder, you will see a folder with some strange numbers and letters, like "55xb6j40.default". Browse into it, followed by the "Calendar" folder. You should now be able to see a file called "CalendarDataFile.ics". Copy this file somewhere safe.

10. You can now uninstall Sunbird by deleteing the whole sunbird program folder and the "C:\Documents and Settings\\Application Data\Mozilla\Sunbird" folder.

Congratulations, you have now converted your proprietary Palm Calendar into a more common iCal file which is more commonly supported by other software and systems.

Share this article:   f g t l