Palm to Open Source

January 8, 2009 at 2:33 am | Posted in Internet, Online services, Software, Web Apps | 17 Comments

As I’ve outlined recently, I have migrated a Linux Netbook to Windows XP and migrated Eudora mail to Thunderbird.  The next step for me is migrating my Palm data to standard formats for use in Sunbird/Lightning. While I agree that Palm Desktop is a more sophisticated PIM than Lightning, the way I use and need to access the information makes this the best solution for me. Migrating to data standards is a key that will increase flexibility and should ease future migrations considerably. Some of this process will be useful to others migrating to similar standards based or standards-handling software.

[UPDATE: the new Palm products use web apps and no longer support Palm Desktop. You will need to migrate out of Palm Desktop in any case. See Comments for further details.]

The Palm carries a variety of photos, Word and Excel docs, music, and so forth but all originated in desktop versions I have already migrated to the Netbook. A couple of things like my shopping program have desktop versions I can continue to use if I choose to.

The key elements to migrate are 4:
1) Contacts – Palm Contacts to Thunderbird Address book, merging with Eudora’s prior import.
2) Memos – Palm Memos to Thundernote in Thunderbird, with Categories on tabs.
3) Tasks – Palm Tasks to Lightning Tasks – no export standard format.
4) Calendar – Palm Calendar to Lightning, including the many repeaters and no export standard.
I reviewed the above components in the prior Eudora migration article.

The migration process is in several steps. First step is exporting. If it’s a non-standard format, the next step is converting them. Some data massage may be needed. Then the next step is importing them. And finally, reviewing the results and fixing or tuning as required.
(these instructions assume Palm Desktop 4.1.4 and Thunderbird 2 with Lightning .9)

1) Contacts

Export: In Palm Desktop, click Contacts, then choose, File > Export. Navigate to where you want to save the export. At the bottom, select the All radio button, choose the .CSV format from the pick list and give it a name, like ‘Palm Contacts’. Click OK. Click OK on the box asking about fields to export. All should be selected and thus all is exported.

You can then check the output in the free OpenOffice Calc or other spreadsheet program. (careful about Word – it adds junk) Don’t change the format if the program asks.

Archives? If you were prone to Save a copy on delete when using Palm, there will also be Archives by Category. If you want to save any of that before you uninstall Palm, it’s worth reviewing while you have the tool to extract them. File > Open Archive and browse. Anything worth keeping? Repeat the above to export the open archive as required. You can just keep these files for long term record or import what you need by adding the lines to the main export CSV file.

Prep:
a – Backup: First, back up your existing TB address book. The 2 files are abook.mab and history.mab. The default store is in Documents and Settings\[name]\Application Data\Thunderbird\Profiles\[profilename]\  If you’ve added any other mailboxes, there will be other .MAB files – include those.

You could now just use the TB Import tool and do field matching but this is messy and likely to give you more work later. It’s easier and faster to organize the raw data to match in the first place. The more consistently you’ve used the fields for data, the easier this will be.

b – Label Row: Open the CSV export in Calc or Excel. Create a new row above the first record in the csv. (right click the first row, Insert Row or New Row)

c – Naming: Paste the correct TB names for each column along this new row. They are quite out of sequence. For example, Home Address is past the Custom fields. You can open a Palm contact if you are not sure of the field sequence. It roughly follows the form.

The Thunderbird field names to match to:
First Name, Last Name, Display Name, Nickname, Primary Email, Secondary Email, Work Phone, Home Phone, Fax Number, Pager Number, Mobile Number, Home Address, Home Address 2, Home City, Home State, Home ZipCode, Home Country, Work Address, Work Address 2, Work City, Work State, Work ZipCode, Work Country, Job Title, Department, Organization, Web Page 1, Web Page 2, Birth Year, Birth Month, Birth Day, Custom 1, Custom 2, Custom 3, Custom 4, Notes.  (36 total)

Copy and Paste this list into a text file. Cut and paste these into your form, then the ones left over are added in step f.

d – Clearing: Columns w/o content you can delete. Select the column and scroll down to check. Move orphaned entries – for example, I only had 2 Palm “Other” addresses, not a TB option. Make sure you only have multi-line data in the Notes field so it doesn’t get lost. Use Notes for overflow stuff and misc. Delete the ‘0’ and Category columns – they’re not used.
(If Categories are important to you, you can sort the data by Category and do separate imports into separate address books. Name the Spreadsheets what you want the books called. But organize the columns first, then move the data sets to separate files with the same assigned column names. It may be easier to drag and drop the records into new address books after the import unless you have a very large address book.)

Some fields are not present in the Palm data, like Nickname. Some fields are used differently, like Palm has a multi-line Address field, so no ‘address 2’ fields like TB. Palm has 7 variably named fields for phone and email. TB has 2 email and 4 phone. TB has no 3rd “Other Address” set. Palm stores the birthday in one date field, TB 3. Palm has 9 custom fields, TB has 4 + a Notes field. And so on. This may take a little planning to best place the data as you’ve been using it.

TB has a more fixed use than Palms more flexible labels. From a data standpoint, TB’s technique is better. This will improve the quality of your data and its interoperability with things like on-line services. A fax fields is always a fax field.

e – Sorting: After the clearing and labeling series I ended up with columns in this sequence:
Last Name, First Name, Custom 4, Organization, Work Phone, Home Phone, Fax Number, Mobile Number, Primary Email, Work Address, Work City, Work State, Work ZipCode, Work Country, Web Page 1, Custom 1, Custom 2, Custom 3, Notes, Home Address, Home City, Home State, Home ZipCode, Home Country.

These now just need to be sorted into the same sequence as TB’s in c above for easy import.

f – Insert: Insert empty columns in the TB sequence and name them as above so you have a direct map to the Import tool. While you can deselect fields and move them in the import tool, this is much more reliable. It’s also easier to insert after they’re sorted and will ensure you are not putting data in a randomly named existing field.

g – Display: TB uses the Display Name to show entries in the address book. With this blank, the address list won’t be much use. Select the data in the 2 name fields, copy them to another sheet, merge them, then paste this column into Display Name. (the more geeky may want to use a SQL script) For entries that are just companies, copy the company name over from the “Organization” column. Usually you’ll have a contact name but if you have lots that don’t, add the organization name to the merge list above and merge the 3 columns. When it’s ready, there should be no blank Display Names.

Import:
In Thunderbird, choose Tools > Import, choose Address Book, then Text File (.csv, etc.). Navigate to where you stored the updated export, change the Type pick list to ‘Comma Separated’, select the file and click Open. It will show you the first data row where you made the labels in step b & c. The “first record contains field names” checkbox is checked by default.

If everything is peachy, the 2 lists will be just the same. You can use the Next button to scroll through some records to confirm. If you’re happy, click OK. Importing will begin.

You will now have a new address book named after the file you imported.
If this is messed up, just delete it, fix the CSV file and reimport.

Merge:
The final bit is merging records. You can drag and drop the Imported entries into your existing address books. Use them like Palm Categories. Make new address books from File > New. You may find the Duplicate Contact Manager Add-on useful here. It adds a new menu item and will show you duplicates, letting you choose the one to keep and move data over to. Thus, duplicates can be quickly massaged together or deleted. You can disable the Add-on afterward, until you need it again.

And now, we have one address book! For the first time in a decade…

Another Add-in you may find useful: Google Contacts – sync Google (Gmail, etc) contacts with TB Address book. [Update: Google Contacts is one way – it downloads your Gmail contacts, then syncs them thereafter. It does not upload TB contacts. Gmail contacts are much more basic.]

2) Memos

You can Export memos from Palm as CSV or Text. Because I plan to just paste them into Thundernotes, lets use Text.

You may wish to review your Memo archives too, as in Contacts.

a) In Palm Desktop, select Memos. File > Export. Select All, choose Text type and give it a name and where you want to save it. Export.
b) Open Thundernotes in Thunderbird. Create the categories (tabs) you like.
c) Go to where you saved the export file and open it.
d) Under the correct tab, Add a new Thundernote, select the text for the next memo from the export and paste it in. Close the note, it auto-saves.

3) Tasks

Tasks export to a proprietary format which is of little use here. But I discovered a simple trick.
In Palm Desktop, simply select all of the tasks on your list (Ctrl-A), copy, and paste them into a text file.
If there are fields you use not displayed, right click the column titles and add them.

Don’t forget to review your archives.

Open the Task window in Lightning/Sunbird. If the Tasks are simple, just paste them into the “Click Here to Add a New task” box at the top and click Enter.  Ctrl-D will bring up a New Task window for more complex settings.

While this approach is hardly sophisticated, I mostly use tasks to remind me of non-current bits I’m not working on, a kind of reminder list. For quick current things, I found it was faster just to write it down. For bigger stuff, I organize projects with more sophisticated software. If you have a lot of Tasks, you may need to try something else, like the Task export tool in Calender below. The data migration attempts I read all needed 3rd party software to do it and the results were inconsistent. The tool below, for example, assigned today’s date to all tasks without a date – in the Palm Desktop too.

hmm – so far it’s getting easier…

4) Calendar

The Calendar, like Tasks, can only be exported in a proprietary format. But events are probably the most complex of the data to migrate. They have details, times, dates, alarms, and so on. One of the big tricks is repeat events like annual birthdays or a 5 day project or weekly meeting. If it can’t get that right, it’s a mess.

Basically, you need to get the data into a standard format that Lightning can import. Lightning supports 2 import choices –  ICS or CSV formats. Because CSV is simple data and needs to be correctly sequenced (as with Contacts above) and in the correct format, it’s not a good choice. It also has data field size limits. Thus, we’re looking for a way to get the Palm data into ICS (iCal).

Archives? Unless you used your calendar in an unusual way, you are unlikely to want your deleted events archive. For myself, I mark canceled or unattended events rather than deleting them so that data remains searchable. But you’ll know better.

There`s a few ways possible to get to ICS:

Patrick provides excellent documentation for a laborious manual process: Export to DBA, DBA to CSV, fixing the table structure (in 15 steps), and finally CSV to iCAL (.ICS).  However, several people report unsolvable errors, repeat events are converted to singles, and some all day events are moved a day ahead. Not as simple to check such data going back 10 years.

[UPDATE: Tim Adye has built an export tool – see the link in Comments]

You could use an intermediary. If you have a Linux box, you can evidently import to the Linux PIM KPilot or Evolution. This does the conversion and migrates to the open .ICS format. That can then be exported for Lightning.

Or you could use software that converts. I tried a couple of tools that directly convert the data to ICS but they messed up Repeaters badly. One filled 24 hrs. with every “No Time” event, like a birthday reminder.

The best solution it turns out was a blend – an intermediary and a conversion tool. Google Calendar is a free product that will export ICS. Then you just need a program that will sync the Palm Desktop with Google. The solution I found was Companionlink for Google. It worked very well, though I did have to fiddle with the default settings a bit to get all the calendar loaded.

Export:
1) Create a free Google Calendar: http://www.google.com/calendar/
If you have a Gmail account, you just need to log in. Otherwise, you need to create a free account.
Note that the calendar is private by default.
2) Download and install the Companionlink for Google program.
3) Run the CompanionLink Setup program (configuration). You enter your Calendar login, then ‘Palm Desktop’ to sync with.
4) Under the Palm choice on the Sync Target page, click Configure. This opens an altered view of the Palm Hotsync config.
– On the Date Range tab, it has 90 days preset for “Days Back”. For the first sync, you have to cover it all – make that 5000 if you want to go back over 10 years. Or set as appropriate for your needs.  (apparently, if the number of records exceeds this setting, you missed some. It shows the record count as it syncs)
– On the Applications tab, deselect Tasks or Contacts depending on what you want posted. Contacts are placed in Gmail, Tasks become events with a ‘Task:’ prefix. If you have any undated Tasks, these will all be placed on Today’s date. If this is not desirable, deselect Tasks. You can set dates and sync them later.
Once you’re clear you have the settings right, its time to migrate.
Click OK, Next, and Finish.
5) It returns you to the main program. Click Synchronize. It begins to upload.

When done, you can log into Google Calendar and you should see all of your events. The program handles repeaters very well. It copied over 4,300 events for me and they all appeared correctly. It even backed up the Palm data to it’s own User/AppData folder.

If you plan to stick with the Palm Desktop and would like to use it with Google Calendar, Companionlink is a great Sync tool. You can try it free for about 2 weeks. Be sure to set the ‘Date Range’ tab back to about 90 days. This will speed future syncs.

Default colours in Google are set beside the calendar name on the left so you can tell multiple ones apart.

Export 2:
Once all the data is in and confirmed on Google, we’re ready to export to ICS.
1) Log into Google Calendar, click on Settings in the upper right. Click the Calendars tab. Click ‘Export My Calendars’. Choose where and Save the ZIP file.
2) Unpack the Zip file to ICS for import. Simple.

Import:
1) This is a good time to back up TB data in:
c:\Documents and Settings\[name]\Application Data\Thunderbird\Profiles\[profilename]\
2) In Thunderbird, select Calendar view, then File > Import Calendar and choose the Google ICS export file above.
3) If it asks, choose the correct calendar. The default is Home.

After it loaded, I briefly saw the calendar, then just got blank pages when I moved through the months. I needed to close TB and reopen for it to display. It’s much slower than Palm to display older dates. Top left calendar – use the year and month drop downs for much faster navigation.

Categories: In Tools > Options > Lightning > Categories tab you can set event colours, such as for Birthdays and Anniversaries. But note that Google uses this for its linking if you plan to keep the Calendars linked. In other words, set a colour and that data won’t sync.

[Updated] You have the choice of keeping Google to sync remotely with mobile devices and access your calendar on the road OR deleting the Google Calendar and using a local sync as I did. You may want to review options on “Sharing” before deciding. If you don’t want the on-line calendar to remain, in Google calendar, click on Settings in the upper right. Click The Calendars tab. Then click the trash can on the far right after the name of your calendar. This will clear it after a warning.

5) Backup:

Mail is in c:\Documents and Settings\[name]\Application Data\Thunderbird\Profiles\[profilename]\Mail
This also includes your account settings.

Just above that in the [Profile] folder is:
– Extensions subfolder.
– Address books: The address book starts with 2 files: abook.mab and history.mab. With new Address books, other MAB files are created.
– Thundernotes data is saved to a set of thundernotes xml files in the Thunderbird profile folder as above.
– Tasks are stored with the Calendar.
– Lightning stores its Calendar files in the database file ‘storage.sdb’ in the TB profile folder also.

Update:
the Profiles.ini (location of profile) is in the root folder, above the profile:
c:\Documents and Settings\[name]\Application Data\Thunderbird\
It remains there, even if you move the profile location as below.

Thus, if you back up c:\Documents and Settings\[name]\Application Data\Thunderbird\, you’ll get it all. If you move the data, be sure to get the root folder above, along with your new profile location.

More Profiles:
If you like to keep your Data on a separate data drive, etc., here is instructions on moving your data set. It’s safer to do a data share then too.  In my case, I backed up the Profile first, so I did delete the files with the profile. For just moving the profile, you can stop before Step 5. But if you have different people using the same computer, you can use this to create multiple profiles with different data sets. Even have work and home versions of TB with different data – choose which when booting TB.

Next, I’ll be setting up a sync routine with the netbook and reviewing other options.  The first concept didn’t work well.
David