DeepWave

Three new Palm related articles have just been posted. They tell the story of how I managed to move my data out of my Palm Treo 650 into the Mozilla suite of products. As everyone knows, the Palm data formats are highly proprietary, and so moving the data to Mozilla means that at least I can still read/write the data in the future. Plus, since I use Mozilla on the desktop all the time, this means my phone and my desktop can be truly synchronised once and for all. Check out the articles:

• Difficulty HotSyncing your Palm Databases
• Convert your Palm OS Contacts to vCard
• Convert your Palm OS Calendar to iCal

Why did I really need to move my data into Mozilla in the first place? Well, because I recently got a new Samsung SGH-i600 Windows Mobile 5 Smartphone. ActiveSync will only sync your contacts and calendar with MS Outlook and not Outlook Express. Since I don’t use Outlook, I have to get ActiveSync to sync with my Mozilla suite instead. I never thought I’d say this, but moving to a MS Windows platform forced me to use open standards/source formats, data and applications! I’ll try to post some information about my SGH-i600 setup in the future.

I’ve been using the PalmOS platform since the Palm III days. During this time, I’ve bought and upgraded no less than 5 PalmOS powered devices - the Palm III, Palm V, Palm m505, Tungsten T3 and the Treo 650. The latest Treo 680 and Treo 750v simply add nothing new to the equation while I’ve been needing 3G support for global roaming and generally, a roadmap from Palm on the future of this platform to assure me that it will still be around in a year or two’s time. With the Apple iPhone setting the bar very high for 2007/08, Palm has to show something to inspire customers to stay with them and not just rely on blind customer brand/platform loyalty to continue in the future.

So, the sad day arrived, Hell froze over and I have gone over to the dark side. I guess this will be the last Palm related articles I post unless I decide to go back to it one day in the future. The good news is, now that I am in Mozilla, which is open, I can go to any platform, not just Palm, quickly and easily.

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
Step 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.

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

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

Step 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)
Step 1. Download the datebook_csv.zip package and unzip it to the same folder as your Datebook Archive.

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

Step 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.

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

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

Step 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.

Step 1. Open your CSV in Excel.

 
Step 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.

Step 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.

Step 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).

Step 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).

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

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

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

Step 9. "Date" becomes "Start Date"

Step 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.

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

Step 13. 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.

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

Step 15. 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.

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

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

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

 
Step 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".

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

Step 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.

 
Step 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.

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

 
Step 9. Go to the "C:\Documents and Settings\<your-name>\Application Data\Mozilla\Sunbird\Profiles" where <your-name> 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.

Step 10. You can now uninstall Sunbird by deleteing the whole sunbird program folder and the "C:\Documents and Settings\<your-name>\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.

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 Contacts (PD version 4.1.4) into the vCard format, which is much more popular with other email programs, PIMs, mobile phones and websites. Fortunately, by using Palm Desktop’s export capabilities supports vCard, and so you too can achieve "data portability".

Step 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.

Step 2. From the Palm Desktop, click on "Contacts", and from the Menu > "Edit" > "Select All".

Step 3. Then go to the Menu > "File" > "Export vCard".

 
Step 4. This will save all your contacts into a single vCard file.

Step 5. Now you need to edit the vCard file with a text editor. With some good editing and Find/Search and Replace, you can fix some of the missing/incorrectly labeled data in the vCard file. Unfortunately, the "Custom" fields don’t usually get transfered in the vCard. Look for "X-Palm-CustomX:" in your vCard file (where X is some number starting from 1). For one VCARD, each line of custom text will be labeled "X-Palm-Custom1:", "X-Palm-Custom2:", "X-Palm-Custom3:" and so on. You need to combined them all into one single line with the header label "NOTE;QUOTED-PRINTABLE:". Here is an example of some wrong strings:

    X-Palm-Custom1:This is
    X-Palm-Custom2:my long
    X-Palm-Custom3:additional note

Here are the correct strings:

    NOTE;QUOTED-PRINTABLE:This is my long additional note

 
Step 6. Once you have made all the changes you need, don’t forget to save it.

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

The most convenient way to transfer data out of your Palm is to do a HotSync of your Palm back to your Palm Desktop. However, sometimes HotSync fails, or seems to take a very, very long time. In such a situation, you need to make sure that your databases are clean and in good order. This is especially true if you have not HotSynced your Palm for a long time. If you have problems HotSyncing your Palm, try the following approach:

Step 1. Download and install the latest version of Pimlico dbScan (http://www.pimlicosoftware.com/dbscan.zip). I would like to note here that thanks must go to Pimlico for making this excellent and critical piece of software free of charge.

Step 2. dbScan will ask you which database you would like to scan, "Current", "History" or "Neither". Choose "Current".

 
Step 3. Next, choose "Current" > "Start Scan". If dbScan does find any corrupted records, you will probably have to delete it. Now, dbScan can delete it for you automatically, but if it fails or if your Palm resets etc, you will probably have to delete it manually. Take down the details of the record highlighted by dbScan, then go to the app and delete it by hand.

 
Step 4. Now, exit out of dbScan, and launch it again, but this time choose "Neither" database.

Step 5. Go to the Menu > "Options" > "Remove All Deleted Records". Do this once each for PalmOne and PalmSource databases. If you had previously deleted entries in your apps, but have not HotSynced, it will be cleared.

 
Step 6. Try HotSyncing again. If it still fails, then you don’t want to hear the next suggestion, which is to perform a clean reset, and/or uninstall all third party software and try re-syncing the databases again.

I hope this helps!