You can export all your contacts (with each contact's phone numbers on the same line) with this SQL query. This works because the contactphonenumbers table has limited phone_type values. We can't do the same left outer join query for the emails because there can be an unlimited count of emails per contacts.

You can export all your contacts (with each contact's phone numbers on the same line) with this SQL query. This works because the contactphonenumbers table has limited phone_type values. We can't do the same left outer join query for the emails because there can be an unlimited count of emails per contacts.

Line 390:

Line 392:

Thanks to [[Import_Sim_Contacts]] for the original Python Script.

Thanks to [[Import_Sim_Contacts]] for the original Python Script.

−

[[Category:Advanced End User]]

+

[[Category:PIM]]

Latest revision as of 18:02, 11 August 2009

To copy your contacts from other devices or desktop systems, you can use the VCF VCard format. This page explains how to export your contacts from various systems and how to import the resulting file on on Openmoko smartphone.

Evolution can export only to VCF version 3.0, so you need to post-process the exported file.

Select Contacts, then File > Save Address Book as VCard and save to your desired destination.

Use the following command to remove evolution's proprietary fields and convert the file to an encoding Om2008.8 will understand (replace old.vcf to the filename you chose for export of your addressbook in evolution):

then there are probably still occurrences of X-EVOLUTION- fields that need to be removed.
Also note that the above script converts your addressbook file to ISO-8859-1 (see last line), as its native UTF-8 apparently isn't recognized by Om2008.8. If necessary, eplace ISO-8859-1 by a encoding that better suits your needs.

To export VCF from Outlook, open Outlook, then open the VB editor by pressing ALT-F11, Insert a module, and paste in the following. Change "Z:\Openmoko\contacts" to wherever you want your vcf files saved (should be an existing, empty directory), and press F5 to run it.

The Qtopia addressbook application supports import of VCF files out of the box. However, since there is a bug, if there are non-ASCII characters in your contacts you cannot use the version 3 format, otherwise you get multibyte UTF-8 characters mangled. Use the version 2.1 to get the encoding stated explicitly and thus characters imported correctly. But because there is another bug, when using version 2.1 vCards, the addressbook eats up all memory (and potentionally crashes the system) on some vCards with multiline entries.

If you're connected via SSH, run the following command first:

export DISPLAY=:0

(So that the addressbook will show on your phone. If you do not that and try to run addressbook locally on your computer display, you won't be able to use the application since the needed buttons will not show.)

Then you can simply run:

addressbook path_to_vCards_file_to_import.vcf

Make sure your contact list has extension .vcf.

You will be asked if you want to import the contacts form addressbook.vcf (the contact name will be taken from the "N:" vCard field).

To import a huge number of contacts, single .vcf files can be merged together using:

cat *.vcf > all.tmp
mv all.tmp all.vcf

then import all.vcf

Tip: If you want to prevent crashing your system if the memory leak occurs, set some limit on memory available to use, so that only the addressbook application dies after reaching the limit. I.e. prior to running the import, do something like:

The Qtopia addressbook doesn't seem to have a delete all function, which means that botching a vcf import is a painful process to undo through the phone GUI.

If you want to delete your entire addressbook or do other mass edits then you'll need access to a command line or GUI SQLite client (SQLite Database Browser seems to be a good, free one, though the SQLite command line client should be fine).

You can export all your contacts (with each contact's phone numbers on the same line) with this SQL query. This works because the contactphonenumbers table has limited phone_type values. We can't do the same left outer join query for the emails because there can be an unlimited count of emails per contacts.

Select distinct nickname, title, firstname, middlename, lastname, suffix,
profession, b_webpage, company, office, department, jobtitle, default_email,
cpn1.phone_number as Home_Phone,
cpn2.phone_number as Home_Mobile,
cpn3.phone_number as Home_VOIP,
cpn4.phone_number as Home_Fax,
cpn5.phone_number as Business_Phone,
cpn6.phone_number as Business_Mobile,
cpn7.phone_number as Business_VOIP,
cpn8.phone_number as Business_Fax,
cpn9.phone_number as Business_Pager,
h_webpage, spouse, gender, birthday, anniversary from contacts
left outer join contactphonenumbers as cpn1
on contacts.recid=cpn1.recid and cpn1.phone_type=1
left outer join contactphonenumbers as cpn2
on contacts.recid=cpn2.recid and cpn2.phone_type=257
left outer join contactphonenumbers as cpn3
on contacts.recid=cpn3.recid and cpn3.phone_type=769
left outer join contactphonenumbers as cpn4
on contacts.recid=cpn4.recid and cpn4.phone_type=513
left outer join contactphonenumbers as cpn5
on contacts.recid=cpn5.recid and cpn5.phone_type=2
left outer join contactphonenumbers as cpn6
on contacts.recid=cpn6.recid and cpn6.phone_type=258
left outer join contactphonenumbers as cpn7
on contacts.recid=cpn7.recid and cpn7.phone_type=770
left outer join contactphonenumbers as cpn8
on contacts.recid=cpn8.recid and cpn8.phone_type=514
left outer join contactphonenumbers as cpn9
on contacts.recid=cpn9.recid and cpn9.phone_type=1026;

I needed the vcf file for other purposes and found a script [1] on SF.net to convert a tab seperated value to a vcf file.
So I used this ugly thing:

sqlite3 ~/Applications/Qtopia/qtopia_db.sqlite $1 'Select distinct firstname, lastname, middlename,
nickname, default_email,title,
cpn5.phone_number as Business_Phone,
cpn1.phone_number as Home_Phone,
cpn8.phone_number as Business_Fax,
cpn9.phone_number as Business_Pager,
cpn2.phone_number as Home_Mobile from contacts
left outer join contactphonenumbers as cpn1
on contacts.recid=cpn1.recid and cpn1.phone_type=1
left outer join contactphonenumbers as cpn2
on contacts.recid=cpn2.recid and cpn2.phone_type=257
left outer join contactphonenumbers as cpn3
on contacts.recid=cpn3.recid and cpn3.phone_type=769
left outer join contactphonenumbers as cpn4
on contacts.recid=cpn4.recid and cpn4.phone_type=513
left outer join contactphonenumbers as cpn5
on contacts.recid=cpn5.recid and cpn5.phone_type=2
left outer join contactphonenumbers as cpn6
on contacts.recid=cpn6.recid and cpn6.phone_type=258
left outer join contactphonenumbers as cpn7
on contacts.recid=cpn7.recid and cpn7.phone_type=770
left outer join contactphonenumbers as cpn8
on contacts.recid=cpn8.recid and cpn8.phone_type=514
left outer join contactphonenumbers as cpn9
on contacts.recid=cpn9.recid and cpn9.phone_type=1026;'| sed 's/|/\t/g' > addressbook.tsv

Make sure you have no illegal chars (utf) in the filename and inside the vcf files. (I had problems with Ü,Ä etc)

The characters seem to work if you convert them from =C3=A4 type of format to proper UTF-8, and the vcards also specify UTF-8 charset. you cannot use other charsets besides UTF-8 since they are delivered via DBus which does not allow others.

Only vcf files should be in your current directory so do a ../ac.

You must call the script in the OM terminal not via Bluetooth or USB. If you do so a dbus error occur

Views

Personal tools

To copy your contacts from other devices or desktop systems, you can use the VCF VCard format. This page explains how to export your contacts from various systems and how to import the resulting file on on Openmoko smartphone.

Exporting old contacts to VCard format

On latest SonyEricsson phones choose to send all contacts (via bluetooth) or make a backup on memory card (tested on Z710i, no problems with unicode when importing to openmoko)

On windows mobile phones you could use active sync to sync with Outlook and then use a Outlook VCF export plugin below

On LG phones the backup function to memory card creates one file with multiple VCards

Evolution

Evolution can export only to VCF version 3.0, so you need to post-process the exported file.

Select Contacts, then File > Save Address Book as VCard and save to your desired destination.

Use the following command to remove evolution's proprietary fields and convert the file to an encoding Om2008.8 will understand (replace old.vcf to the filename you chose for export of your addressbook in evolution):

then there are probably still occurrences of X-EVOLUTION- fields that need to be removed.
Also note that the above script converts your addressbook file to ISO-8859-1 (see last line), as its native UTF-8 apparently isn't recognized by Om2008.8. If necessary, eplace ISO-8859-1 by a encoding that better suits your needs.

Outlook / ActiveSync to VCF

To export VCF from Outlook, open Outlook, then open the VB editor by pressing ALT-F11, Insert a module, and paste in the following. Change "Z:\Openmoko\contacts" to wherever you want your vcf files saved (should be an existing, empty directory), and press F5 to run it.

Import (and export?) on Om 2008.8 and Qtopia

The Qtopia addressbook application supports import of VCF files out of the box. However, since there is a bug, if there are non-ASCII characters in your contacts you cannot use the version 3 format, otherwise you get multibyte UTF-8 characters mangled. Use the version 2.1 to get the encoding stated explicitly and thus characters imported correctly. But because there is another bug, when using version 2.1 vCards, the addressbook eats up all memory (and potentionally crashes the system) on some vCards with multiline entries.

If you're connected via SSH, run the following command first:

export DISPLAY=:0

(So that the addressbook will show on your phone. If you do not that and try to run addressbook locally on your computer display, you won't be able to use the application since the needed buttons will not show.)

Then you can simply run:

addressbook path_to_vCards_file_to_import.vcf

Make sure your contact list has extension .vcf.

You will be asked if you want to import the contacts form addressbook.vcf (the contact name will be taken from the "N:" vCard field).

To import a huge number of contacts, single .vcf files can be merged together using:

cat *.vcf > all.tmp
mv all.tmp all.vcf

then import all.vcf

Tip: If you want to prevent crashing your system if the memory leak occurs, set some limit on memory available to use, so that only the addressbook application dies after reaching the limit. I.e. prior to running the import, do something like:

Mass Delete

The Qtopia addressbook doesn't seem to have a delete all function, which means that botching a vcf import is a painful process to undo through the phone GUI.

If you want to delete your entire addressbook or do other mass edits then you'll need access to a command line or GUI SQLite client (SQLite Database Browser seems to be a good, free one, though the SQLite command line client should be fine).

Qtopia

Shutdown Qtopia

Use the client you have chosen to open up /home/root/Applications/qtopia_sqlite.db.

Important tables: contacts, contactphonenumbers, contactaddresses

Restart Qtopia when you're done and you should have a reset addressbook.

On Om 2008.8

Install sqlite3

Open the database

Delete all data from the contacts tables This will erase all your contacts and contact categories.

You can export all your contacts (with each contact's phone numbers on the same line) with this SQL query. This works because the contactphonenumbers table has limited phone_type values. We can't do the same left outer join query for the emails because there can be an unlimited count of emails per contacts.

Select distinct nickname, title, firstname, middlename, lastname, suffix,
profession, b_webpage, company, office, department, jobtitle, default_email,
cpn1.phone_number as Home_Phone,
cpn2.phone_number as Home_Mobile,
cpn3.phone_number as Home_VOIP,
cpn4.phone_number as Home_Fax,
cpn5.phone_number as Business_Phone,
cpn6.phone_number as Business_Mobile,
cpn7.phone_number as Business_VOIP,
cpn8.phone_number as Business_Fax,
cpn9.phone_number as Business_Pager,
h_webpage, spouse, gender, birthday, anniversary from contacts
left outer join contactphonenumbers as cpn1
on contacts.recid=cpn1.recid and cpn1.phone_type=1
left outer join contactphonenumbers as cpn2
on contacts.recid=cpn2.recid and cpn2.phone_type=257
left outer join contactphonenumbers as cpn3
on contacts.recid=cpn3.recid and cpn3.phone_type=769
left outer join contactphonenumbers as cpn4
on contacts.recid=cpn4.recid and cpn4.phone_type=513
left outer join contactphonenumbers as cpn5
on contacts.recid=cpn5.recid and cpn5.phone_type=2
left outer join contactphonenumbers as cpn6
on contacts.recid=cpn6.recid and cpn6.phone_type=258
left outer join contactphonenumbers as cpn7
on contacts.recid=cpn7.recid and cpn7.phone_type=770
left outer join contactphonenumbers as cpn8
on contacts.recid=cpn8.recid and cpn8.phone_type=514
left outer join contactphonenumbers as cpn9
on contacts.recid=cpn9.recid and cpn9.phone_type=1026;

I needed the vcf file for other purposes and found a script [1] on SF.net to convert a tab seperated value to a vcf file.
So I used this ugly thing:

sqlite3 ~/Applications/Qtopia/qtopia_db.sqlite $1 'Select distinct firstname, lastname, middlename,
nickname, default_email,title,
cpn5.phone_number as Business_Phone,
cpn1.phone_number as Home_Phone,
cpn8.phone_number as Business_Fax,
cpn9.phone_number as Business_Pager,
cpn2.phone_number as Home_Mobile from contacts
left outer join contactphonenumbers as cpn1
on contacts.recid=cpn1.recid and cpn1.phone_type=1
left outer join contactphonenumbers as cpn2
on contacts.recid=cpn2.recid and cpn2.phone_type=257
left outer join contactphonenumbers as cpn3
on contacts.recid=cpn3.recid and cpn3.phone_type=769
left outer join contactphonenumbers as cpn4
on contacts.recid=cpn4.recid and cpn4.phone_type=513
left outer join contactphonenumbers as cpn5
on contacts.recid=cpn5.recid and cpn5.phone_type=2
left outer join contactphonenumbers as cpn6
on contacts.recid=cpn6.recid and cpn6.phone_type=258
left outer join contactphonenumbers as cpn7
on contacts.recid=cpn7.recid and cpn7.phone_type=770
left outer join contactphonenumbers as cpn8
on contacts.recid=cpn8.recid and cpn8.phone_type=514
left outer join contactphonenumbers as cpn9
on contacts.recid=cpn9.recid and cpn9.phone_type=1026;'| sed 's/|/\t/g' > addressbook.tsv

Usage

The script is for the 2007.2 Version (default install on Neo FreeRunner), not for Qtopia

You need Python to use this script so do a

opkg install python-dbus

to install python and the python dbus module

Manage Contacts Script

This script needs all contacts in one big file

You also could run this script on a ssh session!

Copy your big contacts file (contacts.txt) to your Neo

Copy script to your Neo

Connect via ssh to your Neo

Fix special chars in your contacts file with you favorite text editor

To import the file run on the SSH shell:

python manage-contacts.py load < contacts.txt

For a backup your existing contacts on the Neo

python manage-contacts.py dump > contacts.txt

Simple Script

You must run the script in the OM Terminal, not over ssh

The script does a listdir and imports ALL files it found in that dir. (i'm a bad python programmer)

Copy all vcf files to a separate directory on my freerunner

Copy the ac script to the parent directory also on my freerunner

Have the contacts program running (so you have eds running too)

Go to the terminal on the freerunner and type in the vcf directory: "python ../ac" (root@om-gta02:~/vcf# python ../ac)

Note: You must call the script in the OM terminal not via Bluetooth or USB or you get a dbus error. Using "dbus-launch python ../ac" doesn't work either.

Done :D

Troubleshooting

Make sure you have python and python-dbus installed

Make sure you have no illegal chars (utf) in the filename and inside the vcf files. (I had problems with Ü,Ä etc)

The characters seem to work if you convert them from =C3=A4 type of format to proper UTF-8, and the vcards also specify UTF-8 charset. you cannot use other charsets besides UTF-8 since they are delivered via DBus which does not allow others.

Only vcf files should be in your current directory so do a ../ac.

You must call the script in the OM terminal not via Bluetooth or USB. If you do so a dbus error occur