#!/usr/bin/gawk -f # # Author: Martin Fick # Copyright: GNU GPL v2 or above # Date: 2009/04/10 # Ver: 1.0 # # Use this script to convert a palm address database as output by: # # pilot-addresses -a -w add.palm # # to a sql script which can be used with sqlite3 to load the addresses # into an android contacts db. This is useful if you do not want # to use the google contact service to transfer your old contacts to # your new phone! # # Since there is not a perfect match between palm fields and android # fields, your results may vary. The script makes a best attempt to # match things logcically, it even tries to combine entries with the # same name (in case you have multiple addresses for a person). If # you have non US addresses, you should probably edit the country list # in the BEGIN{} section. # # Once you have converted your palm db to sql, you may want to check # the transformation visually before loading it to your phone. # # HowTo: # ------ # # 1) Before loading your palm db onto your phone, it makes sense to make # a backup of your droid contacts to your PC like this: # # adb pull /data/data/com.android.providers.contacts/databases/contacts.db . # # 2) Output your palm db to a file with the linux pilot-xfer tool (note the # important -a switch): # # pilot-addresses -a -w add.palm # # 3) Convert the add.palm created above like this: # # palm2droid < add.palm > droid.sql # # 4) Load the sql onto your phone like this: # # adb push droid.sql /cache/droid.sql # # 5) Load the sql into your contacts db like this: # # adb shell # su # sqlite3 /data/data/com.android.providers.contacts/databases/contacts.db < /cache/sroid.sql # # That's it! # # # # If you do not like your results, restore your contacts db like this: # # adb push contacts.db /data/data/com.android.providers.contacts/databases # # Good luck, I hope this helps! # # Similar to an awk split, but it will not split text # inside of quotes. Takes an optional max parameter, # along with a list of quotes in which quote escaping # (\") is permitted. Use space " " to allow quote # escaping outside of quotes. # V1.0 function splitnq(src, dst, re, qs, es, max, sep, _i, _n, _q, _re, _l, _dst) { if(src=="") return 0; for(_i=1; _i<=length(qs); _i++) { _q = substr(qs,_i,1); if(index(es, _q)) _re = _re "|" _q "([^" _q "\\\\]|\\\\.)*" _q ""; else _re = _re "|" _q "[^" _q "]*" _q ""; } if(index(es, " ")) _re = "^(([^\\\\" qs "]|\\\\." _re ")*)" re ".*$"; else _re = "^(([^" qs "]" _re ")*)" re ".*$"; while(1) { _l = gensub(_re, "\\1", 1, src); _dst[++_n] = substr(src, length(_l) +1); _sep[_n] = gensub("^(" re ").*$", "\\1", 1, _dst[_n]); _dst[_n] = substr(_dst[_n], length(_sep[_n]) +1); if(_l == src) { _dst[_n]= _l; break; } src = _l; } if(max=="") max=_n; for(_i=_n ; _i>_n-max ; _i--) { dst[_n-_i+1] = _dst[_i]; sep[_n-_i] = _sep[_i]; } delete sep[_n-_i+1]; for(; _i>0 ; _i--) dst[max]= dst[max] _sep[_i] _dst[_i]; if(max != -1 && max < _n) return max; return _n; } # Strips outer quotes only. Can deal with nested quotes. # ~ Needs a mechanism to not strip a particular quoting # type while still recongnizing the type as quotes for # quoting purposes (likely for back ticks). Would also # be useful to strip escapes (\) except inside ('). # Probably ignores escaping in general. # V1.0 function stripq(str, qs, m, l, q) { while(m = match(str, "[" qs "]")) { l = l substr(str, 1, m-1); q = substr(str, m, 1); str = substr(str, m+1); m = index(str, q); if(!m) break; l = l substr(str, 1, m-1); str = substr(str, m+1); } return l str; } BEGIN { QD="\""; # Palm Field Strings as output by Address program PFstr = "Last First Title Company Phone1 Phone2 Phone3 Phone4 Phone5 Address City State Zip Country Custom1 Custom2 Custom3 Custom4 Note Private Category"; # Additional Palm Fields from the "extra" output switch PFLstr = "Cat Default Last"; # Palm Field Strings which will exist after processing PalmFStr= "Cat Default Last First Title Company Phone1Type Phone1 Phone2Type Phone2 Phone3Type Phone3 Phone4Type Phone4 Phone5Type Phone5 Address City State Zip Country Custom1 Custom2 Custom3 Custom4 Note Private Category"; # Split these fields and put them into an array NPF = split(PFstr, PF, " "); NPFL = split(PFLstr, PFL, " "); NPalmF = split(PalmFStr, PalmF, " "); # Countries Covered, add more, these were the only ones # in my data NAT["B"] = "Belgium"; NAT["D"] = "Germany"; }; # --------- Double Hash Functions ----------- # Functions to simulate Storing & Retrieving a hash # from another hash function put(hash, key, val, sep, _k, _ks) { del(hash, key, sep); set(hash, key, val, sep); } function set(hash, key, val, sep, _k, _ks) { if(sep == "") sep =":"; for(_k in val) { hash[key "." _k] = val[_k]; _ks = sappend(_ks, _k, sep); } hash[key "." sep] = _ks; } function get(hash, key, val, sep, _n, _ks) { if(sep == "") sep =":"; _n = split(hash[key "." sep], _ks, sep); for(; _n > 0; _n--) val[_ks[_n]] = hash[key "." _ks[_n]]; } function del(hash, key, sep, _n, _ks) { if(sep == "") sep =":"; _n = split(hash[key "." sep], _ks, sep); for(; _n > 0; _n--) delete hash[key "." _ks[_n]]; } # --------- Simple Utility Functions ----------- # Escape double quotes before printing hash function esc(s) { gsub(/"/, "\\\"", s); return s; } function expand(s, _n) { _n = sprintf("\n"); gsub(/\\n/, _n,s); return s; } # Quote a string for sql use (not hacker safe) function qu(str) { return QD expand(esc(str)) QD; } # Append to a string with a separator if needed function sappend(str, app, sep) { if(str!="" && app!="") str = str sep; return str app; } function del_begin_with(s, begs, _k) { for(_k in begs) { if (substr(s, 1, length(begs[_k])) == begs[_k]) return ""; } return s; } # --------- Duplicate Entry Merge Management Functions ----------- # This function will to use the dup entry for a person as # a place to store all subsequent uniq entries of the same # field. function isUniq(dp, f, v, _i) { if(v == "") return 0; if(dp[f] == v) return 0; for(_i=1; dp[f _i] != ""; _i++) { if(dp[f _i] == v) return 0; } dp[f _i] = v; return 1; } function catDups(dp, f, sep, _i, _s) { _s = dp[f]; for(_i=1; dp[f _i] != ""; _i++) { if(_s != "") _s = _s sep; _s = _s dp[f _i]; } return _s; } # --------- Debugging Functions ----------- function print_phone(p, f, cf, _s, _sf) { if(2 != splitnq(cf, _sf, ";", QD, QD)) { print "PERSON_" f "["p"]=\"" esc(stripq(cf, QD)) "\";" return; } print "PERSON_" f "Type" "["p"]=\"" esc(stripq(_sf[1], QD)) "\";" print "PERSON_" f "["p"]=\"" esc(stripq(_sf[2], QD)) "\";" } function print_palm(palm, id, _pf, _f) { for(_pf=1; _pf <= NPalmF; _pf++) { _f = PalmF[_pf]; if(_f ~ /^Phone[1-5]Type$/ || _f ~ /Default/ || _f ~ /Last/) { if(palm[_f] == "") continue; } print "PERSON_" _f "["id"]=\"" esc(palm[_f]) "\";" } } function print_semi(p, cf, nsf, sfs, _s, _n, _sf) { _n = splitnq(cf, _sf, ";", QD, QD); for(_s=1; _s <= nsf && _s <= _n; _s++) { print "PERSON_" sfs[_s] "["p"]=\"" esc(stripq(_sf[_s], QD)) "\";" } } # --------- Palm Parsing Functions ----------- # (the lastname and phone fields need to further be split) function parse_last(palm, cf, nsf, sfs, _s, _n, _sf) { _n = splitnq(cf, _sf, ";", QD, QD); for(_s=1; _s <= nsf && _s <= _n; _s++) { palm[sfs[_s]] = stripq(_sf[_s], QD); } } function parse_phone(palm, f, cf, _s, _sf) { if(2 != splitnq(cf, _sf, ";", QD, QD)) { palm[f] = stripq(cf, QD); return; } palm[f"Type"] = stripq(_sf[1], QD); palm[f] = stripq(_sf[2], QD); } # --------- SQL Generating Functions ----------- function insert_droid_person(pe) { print "INSERT INTO people (name, notes) VALUES(" qu(pe["name"])\ ", " qu(pe["notes"]) ");" } function update_droid_person_notes(pe, n) { print "UPDATE people SET notes = " qu(n) \ " WHERE people.name = " qu(pe["name"]) ";" } function insert_droid_phone(pe, ph, _t, _l, _lv) { _t = ph["type"]; if(ph["label"]!="") { _t = 0; _l = ", label"; _lv = ", " qu(ph["label"] "(" droidphone2txt(ph["type"]) ")"); } print "INSERT INTO phones (person, type, number, number_key"\ _l " )\n\ SELECT people._id , " qu(_t) ", " qu(ph["number"])\ ", " qu(ph["number_key"]) _lv "\n\ FROM people \n\ WHERE people.name = " qu(pe["name"]) ";" } function insert_droid_group(g) { print "INSERT INTO groups (name, notes, system_id)\n\ VALUES(" qu(g["name"]) ", " qu(g["notes"]) ", "\ qu(g["system_id"]) ");" } function insert_droid_contact(pe, c) { print "INSERT INTO contact_methods (person, kind, data, \ aux_data, type, isprimary) \n\ SELECT people._id , " qu(c["kind"]) ", " qu(c["data"]) \ ", " qu(c["aux_data"]) ", " qu(c["type"])\ ", " qu(c["isprimary"]) "\n\ FROM people \n\ WHERE people.name = " qu(pe["name"]) ";" } function insert_droid_org(pe, o) { print "INSERT INTO organizations (company, title, isprimary,\ type, person) \n\ SELECT " qu(o["company"]) ", " qu(o["title"]) ", "\ qu(o["isprimary"]) ", " qu(o["type"]) \ ", people._id \n\ FROM people \n\ WHERE people.name = " qu(pe["name"]) ";" } function update_droid_person2phone(pe, ph) { print "UPDATE people set primary_phone = \n\ ( SELECT _id FROM phones WHERE person = \n\ ( SELECT _id FROM people \n\ WHERE name = " qu(pe["name"]) ") \n\ AND number = " qu(ph["number"]) "\n\ )\ WHERE people.name = " qu(pe["name"]) ";" } function insert_droid_person2group(pe, g) { print "INSERT INTO groupmembership (person, group_id)\n\ SELECT people._id , groups._id \n\ FROM people JOIN groups \n\ WHERE people.name = " qu(pe["name"]) "\n\ AND groups.name = " qu(g["name"]) ";" } # --------- Droid Data Support Functions ----------- function add_US(palm, _a, _y, _s, _z, _ysz) { _a = palm["Address"]; _y = palm["City"]; _s = palm["State"]; _z = palm["Zip"]; if(_y != "" && _s != "" && _z != ""){ _ysz = _y ", " _s " " _z; } else if(_y != "" && _s != "") { _ysz = _y ", " _s; } else if(_s != "" && _z != "") { _ysz = _s " " _z; } else if(_y != "" && _z != "") { _ysz = _y ", " _z; } else if(_y _s _z != "") { _ysz = _y _s _z; } if(_a != "") { if(_ysz != "") return _a "\n" _ysz; return _a; } return _ysz; } function add_euro(palm, abr, _a, _y, _z, _c, _ysz) { _a = palm["Address"]; _y = palm["City"]; _z = palm["Zip"]; _c = palm["Country"]; # These 2 lines are small fixes for my bad data # They should not hurt normal to use normally though if(_z != "" && _z != _z +0 && _y == "") { _y = _z; _z =""; } if(_z != "" && _z != _z +0) { _y = _z " " _y; _z =""; } if(_y != "" && _z != ""){ _ysz = abr "-" _z " " _y "\n" _c; } else if(_y != "") { _ysz = abr "- " _y "\n" _c; } else if(_z != "") { _ysz = abr "-" _z "\n" _c; } if(_a != "") { if(_ysz != "") return _a "\n" _ysz; return _a; } return _ysz; } function add2dcontact(add, con, primary) { con["kind"] = 2; con["data"] = add; con["type"] = 1; # Home if(primary) con["isprimary"] = 1; } function droidphone2txt(ph) { if(ph == 1) return "Home" if(ph == 2) return "Mobile" if(ph == 3) return "Work" if(ph == 4) return "FaxWork" if(ph == 5) return "FaxHome" if(ph == 6) return "Pager" if(ph == 7) return "Other" } function dphone2demail(ph, con, primary) { con["kind"] = 1; con["data"] = ph["number"]; con["type"] = 1; # Home if(primary) con["isprimary"] = 1; } # --------- Palm 2 Droid Conversion Functions ----------- function palm2dname(palm, _n, _f, _l, _t) { _f = palm["First"]; _l = palm["Last"]; _t = palm["Title"] if(_l == "") _n = _f; else if(_f == "") _n = _l; else _n= _f " "_l; if(_t != "") { if(_n == "") _n= _t; else _n = _n " ("_t")" ; } return _n; } function palm2dnotes(palm, _n, _c1, _c2, _c3, _c4) { _n = palm["Custom1"]; _n = sappend(_n, palm["Custom2"], "\n"); _n = sappend(_n, palm["Custom3"], "\n"); _n = sappend(_n, palm["Custom4"], "\n"); return sappend(_n, palm["Note"], "\n"); } function palm2dperson(palm, pe) { pe["name"] = palm2dname(palm); pe["notes"] = palm2dnotes(palm); # I often have company entries without a person#s name. if(pe["name"] == "" && palm["Company"] != "") { pe["name"] = palm["Company"]; } } function palm2dgroup(str, g, gids, _gid) { g["name"]= str; g["notes"]= str; g["system_id"]= str; for(_gid in gids) { if(gids[_gid] == str) return _gid; } return -1; # not in gids yet } function palm2dphonet(t) { if(t == "Work") return 3; if(t == "Main") return 1; # Home if(t == "E-mail") return -1; if(t == "Home") return 1; if(t == "Mobile") return 2; if(t == "Other") return 7; if(t == "Pager") return 6; if(t == "Fax") return 4; # fax_work, 5 for fax_home return 1; # Home ~ add a switch to change this default } function palm2dphone(palm, ph, n, _nk, _i) { ph["number"] = palm["Phone"n]; _nk = ph["number"]; gsub(/[^0-9]/, "", _nk); for(_i=1; _i<=length(_nk); _i++) ph["number_key"] = ph["number_key"] substr(_nk,length(_nk) - _i +1,1); ph["type"] = palm2dphonet(palm["Phone"n"Type"]); if(match(ph["number"], /@.*\../)) { ph["type"]= -1; ph["contact_method"]= "email"; return } if(match(ph["number"], ":// ") || match(ph["number"], "www")){ ph["type"]= -2; ph["contact_method"]= "URL"; return } if(match(ph["number"], /[^-()0-9. ]/)) { _nk = ph["number"]; sub(/^[-()0-9. ]*/, "", _nk); #print "Phone # meta-data: " _nk > "/dev/stderr" phone["label"] = _nk; } } function palm2daddress(palm, _cntry, _abr) { _cntry = palm["Country"]; if(_cntry == "") return add_US(palm); if(_cntry == "US") return add_US(palm); if(_cntry == "USA") return add_US(palm); for(_abr in NAT) { if(_cntry == _abr || _cntry == NAT[_abr]) return add_euro(palm, _abr); } print "Unknown Country: " palm["Country"] ", sorry skipping!" > "/dev/stderr" next } function palm2dorg(palm, o) { o["company"] = palm["Company"]; o["type"] = 1; # Work } # --------- Main Loop ----------- { # --------- Parse Each Palm Entry ----------- delete cfs; // comma fields splitnq($0, cfs, ",", QD, QD); delete palm; pid++; for(pf=1; pf <= NPF; pf++) { if(PF[pf] == "Last") { parse_last(palm, cfs[pf], NPFL, PFL); } else if(PF[pf] ~ /^Phone/) { parse_phone(palm, PF[pf], cfs[pf]); } else { palm[PF[pf]]= stripq(cfs[pf], QD); } } # print_palm(palm, pid); if(palm["Cat"] != palm["Category"]) { print "Categories do not match: "palm["Cat"] " != " palm["Category"] > "/dev/stderr" #print_palm(palm, pid); } # --------- Convert to Droid Objects and print SQL ----------- sql=1; # -- people -- # delete pers; palm2dperson(palm, pers); dpid = PEOPLE[pers["name"]]; if(!dpid) { PEOPLE[pers["name"]] = pid; if(sql) insert_droid_person(pers); } else { # To merge people with the same name together # we simply need to add all new info as the # same person (ensuring we never add duplicate # info) #print "Duplicate Person(" dpid "): (" pers["name"] ")! Current pid: " pid ". Merging person info" > "/dev/stderr" delete dpalm; delete dpers; get(PALM_PHASH, dpid, dpalm); get(PERS_HASH, dpid, dpers); #print_palm(dpalm, dpid); #print_palm(palm, pid); if(isUniq(dpers, "notes", pers["notes"])) { if(sql) update_droid_person_notes(dpers, catDups(dpers, "notes", "\n")); } } #-- groups --# delete grp; gid = palm2dgroup(palm["Cat"], grp, gids); if(gid == -1) { if(sql) insert_droid_group(grp); gids[++ngid] = grp["name"]; } if(!dpid || isUniq(dpalm, "Cat", cat)) { if(sql) insert_droid_person2group(pers, grp); } #-- phones --# primary =0; default=0; for(i=1; i <= 5; i++) if(palm["Phone"i] != "") { delete phone; palm2dphone(palm, phone, i); if(phone["type"] == -2) { # Treat it like a note url = "URL: " phone["number"]; if(!dpid) { if(isUniq(pers, "notes", url)) { if(sql) update_droid_person_notes(pers, catDups(pers, "notes", "\n")); } } else if(isUniq(dpers, "notes", url)) { if(sql) update_droid_person_notes(dpers, catDups(dpers, "notes", "\n")); } } else if(phone["type"] == -1) { # Email delete con; dphone2demail(phone, con); if(!dpid || isUniq(dpalm, "Phone", palm["Phone" i])) { if(sql) insert_droid_contact(pers, con); } } else { # print pers["name"] ":" palm["Phone" i] if(!dpid || isUniq(dpalm, "Phone", palm["Phone" i])) { if(sql) insert_droid_phone(pers, phone); if(palm["Default"] == palm["Phone" i "Type"]) { if(palm["Default"] == "" || primary) continue; primary =1; if(sql) update_droid_person2phone(pers, phone); } else if(!primary && !default) { default =1; if(sql) update_droid_person2phone(pers, phone); } } } } #-- Addresses --# add = palm2daddress(palm); if (add) { palm["add"] = add; delete con; add2dcontact(add, con, 1); if(!dpid || isUniq(dpalm, "add", add)) { if(sql) insert_droid_contact(pers, con); # print add "\n" } } #-- Company --# if (palm["Company"] != "" && palm["Company"] != pers["name"]) { delete org; palm2dorg(palm, org); if(!dpid || isUniq(dpalm, "Company", palm["Company"])) { if(sql) insert_droid_org(pers, org); } } # --------- Manage Duplicates so we can Merge them ----------- if(dpid) { put(PALM_HASH, dpid, dpalm); put(PERS_HASH, dpid, dpers); } put(PALM_HASH, pid, palm); put(PERS_HASH, pid, pers); }