zeroflux.org

• Home
• Blog
• Twitter
• GitHub
• Projects
• Guitar Tabs

#!/usr/bin/python
import io, sys
import sqlite3

# get headers from CSV template
#   <http://theregoesdave.com/wp-content/uploads/2008/10/gmail.csv>
hdrs = io.open('gmail.csv', 'r').readline().strip().split(',')
print '"' + '","'.join(hdrs) + '"'

conn = sqlite3.connect('AddressBook.sqlitedb')
conn.row_factory = sqlite3.Row

cContact = conn.cursor();
cContact.execute("SELECT * FROM ABPerson ORDER BY Last,First")

# From the ABMultiValueLabel table
Labels = ("", "Mobile", "Work", "Home", "Other")

for contact in cContact:
	# get phone numbers
	cPhone = conn.cursor();
	cPhone.execute("SELECT value,label FROM ABMultiValue WHERE record_id=? AND property=3 ORDER BY label", (contact["ROWID"],))
	phones = {}
	for p in cPhone: phones[Labels[p["label"]]] = p["value"]
	if(len(phones) == 0): continue # skip people without phone numbers

	# get email addresses
	cEmail = conn.cursor();
	cEmail.execute("SELECT value,label FROM ABMultiValue WHERE record_id=? AND property=4 ORDER BY label", (contact["ROWID"],))
	emails = {}
	for p in cEmail: emails[Labels[p["label"]]] = p["value"]

	# Use "Section 1" for home/mobile
	# Use "Section 2" for work
	rec = {
		'Name': "%s %s" % (contact["First"], contact["Last"]),
		'E-mail': emails["Home"] if emails.has_key("Home") else emails["Work"] if emails.has_key("Work") else "",

		'Section 1 - Description': 'Home',
		'Section 1 - Email': emails["Home"] if emails.has_key("Home") else "",
		'Section 1 - Phone': phones["Home"] if phones.has_key("Home") else "",
		'Section 1 - Mobile': phones["Mobile"] if phones.has_key("Mobile") else "",

		'Section 2 - Description': 'Work',
		'Section 2 - Email': emails["Work"] if emails.has_key("Work") else "",
		'Section 2 - Phone': phones["Work"] if phones.has_key("Work") else "",
		'Section 2 - Company': contact["Organization"] if contact["Organization"] else ""
	}

	# prune empty fields and section descriptions
	def prune(dict, key):
		for k in dict.keys():
			if len(dict[k]) == 0: del dict[k]
		ct = 0
		for k in dict:
			if k.startswith(key): ct = ct + 1
		if ct == 1: del dict[key+' - Description']

	prune(rec, "Section 1")
	prune(rec, "Section 2")

	# write out as CSV
	row = []
	for h in hdrs:
		row.append(rec[h] if rec.has_key(h) else "")
	print '"' + '","'.join(row) + '"'

15 Responses

1. JD Says:
   June 11, 2009 at 5:53 pm -0700
   
   As far as your 6th note about Android, you can organize your apps into folders. Long-press on one of the home screens , select Folders->New Folder, drop some apps from the app area thingy to the home screen and them drop the app into/onto the folder you just created.
   
   Of course, you can have multiple folders for multiple things or use one of the 3 desktops for its own thing.
2. Judd Says:
   June 11, 2009 at 7:45 pm -0700
   
   Hi JD,
   
   Ah, very useful, thanks. I still tend to forget about trying a long-press!
   
   - J
3. Dan McGee Says:
   June 11, 2009 at 9:25 pm -0700
   
   I've yet to use Android that much, so good to hear more about it. I'm an iPhone user as well, and I wish I could do a bit more hacking on it than what I can do through the jailbreak, but I guess that is what I signed up for.
   
   I had to do a little sqlite hacking as well on the iPhone- don't know if you saw my post about hacking the SMS database (http://www.toofishes.net/blog/iphone-sms-database-hacking/), but Python proved to be very useful for that.
4. Ugur Says:
   June 27, 2009 at 6:25 am -0700
   
   Hi there,
   
   Thanks for sharing this code, but it gives me an error:
   
   " File "csv.python", line 37
   'E-mail': emails["Home"] if emails.has_key("Home") else emails["Work"] if emails.has_key("Work") else "",
   ^
   SyntaxError: invalid syntax"
5. Judd Says:
   June 29, 2009 at 6:20 am -0700
   
   Hi Ugur,
   
   What version of Python are you using? That line uses two nested ternary conditionals, which might not be supported in earlier versions.
   
   You can always expand those ternaries into a normal if/then/else structure -- that should work with any version of Python.
   
   - J
   
6. Ugur Says:
   June 30, 2009 at 6:11 am -0700
   
   I've upgraded to Python 3.0, but now I'm getting a different error:
   
   "File "csv.python", line 8
   print '"' + '","'.join(hdrs) + '"'
   ^
   SyntaxError: invalid syntax"
7. Digital Pioneer Says:
   April 03, 2010 at 5:21 pm -0700
   
   Multitouch on Android was a slow starter, I agree. You can get it in the browser by using modded software (I highly recommend CyanogenMod, it's very nice), though it comes stock in Android 2.1. And the slowness is a pain on the older phones (like Magic and Dream) but the Nexus One does not suffer from performance issues. :P Good phone, that. I highly recommend you give it a shot before giving up on Android based on slowness; the early Android phones weren't made to be fast.
   
   As to development, I'm not a fan of Java either, but Objective-C is a nightmare. No contest on that front. Also, I've developed for both platforms, and I find Android to be on the whole far more pleasurable to deal with. That could be because I don't have to use a mac and I can test on real hardware without selling my soul or jailbreaking the OS, but I really think Android development is just more intuitive.
   
   Just my 2 cents. :)
8. Iddo Says:
   August 21, 2010 at 10:37 am -0700
   
   Just wanted to thank you for your script which saved my contacts on my iphone since it managed to somehow ruin the data.
   Your script solved 90% of my problems so I had to modify it a little. Here's the complete script which should produce a CSV that Google should handle.
   
   
   #!/usr/bin/python
   import io, sys, codecs, locale, time
   import sqlite3
   
   sys.stdout = codecs.getwriter("UTF-8")(sys.stdout)
   
   conn = sqlite3.connect('AddressBook.sqlitedb')
   conn.row_factory = sqlite3.Row
   
   # Extract phone and email labels from ABMultiValueLabel table
   cLabel = conn.cursor();
   cLabel.execute("SELECT rowid,value FROM ABMultiValueLabel")
   Labels = {}
   for label in cLabel:
   Labels[label["rowid"]] = label["value"].replace("_$!<",'').replace(">!$_","")
   
   cContact = conn.cursor();
   cContact.execute("SELECT * FROM ABPerson ORDER BY Last,First")
   
   uhdr=set()
   hdr=[]
   rows=[]
   
   for contact in cContact:
   # initialize row
   rec = {}
   
   def add(to,tprop,dict,dprop,hdr):
   if tprop not in uhdr:
   uhdr.add(tprop)
   hdr.append(tprop)
   #print tprop
   #print dict.keys()
   if dprop in dict.keys() and dict[dprop] is not None:
   if dprop == "Birthday" and len(dict[dprop]) > 0:
   #print time.gmtime(float(dict[dprop])) + (0,0,0,0,1,0)
   t = time.gmtime(float(dict[dprop]))
   #to[tprop] = '%d/%m/%Y',time.gmtime(float(dict[dprop])))
   to[tprop] = str(t[0]) + "-" + str(t[1]) + "-" + str(t[2])
   #print to[tprop]
   else:
   to[tprop] = dict[dprop]
   
   
   add(rec,"Given Name",contact,'First',hdr)
   add(rec,"Additional Name",contact,"Middle",hdr)
   add(rec,"Family Name",contact,"Last",hdr)
   add(rec,"Name Suffix",contact,"Suffix",hdr)
   add(rec,"Name Prefix",contact,"Prefix",hdr)
   add(rec,"Nickname",contact,"Nickname",hdr)
   add(rec,"Short Name",contact,"DisplayName",hdr)
   add(rec,"Birthday",contact,"Birthday",hdr)
   add(rec,"Notes",contact,"Note",hdr)
   
   # get email addresses
   cEmail = conn.cursor();
   cEmail.execute("SELECT value,label FROM ABMultiValue WHERE record_id=? AND property=4 ORDER BY label", (contact["ROWID"],))
   i = 1
   for p in cEmail:
   add(rec,"E-Mail %d - Type" % i,Labels,p["label"],hdr)
   add(rec,"E-Mail %d - Value" % i,p,"value",hdr)
   i = i + 1
   
   # get phone numbers
   cPhone = conn.cursor();
   cPhone.execute("SELECT value,label FROM ABMultiValue WHERE record_id=? AND property=3 ORDER BY label", (contact["ROWID"],))
   i = 1
   for p in cPhone:
   add(rec,"Phone %d - Type" % i,Labels,p["label"],hdr)
   add(rec,"Phone %d - Value" % i,p,"value",hdr)
   i = i + 1
   
   # get addresse
   cAddress = conn.cursor();
   cAddress.execute("SELECT UID,value,label FROM ABMultiValue WHERE record_id=? AND property=5 ORDER BY label", (contact["ROWID"],))
   i = 1
   for p in cAddress:
   # 1"Street" 2"State" 3"ZIP" 4"City" 5"CountryCode" 6"Country"
   cValues = conn.cursor()
   cValues.execute("SELECT key,value FROM ABMultiValueEntry WHERE parent_id=? ORDER BY key", (p["UID"],))
   addr = {}
   for v in cValues:
   addr[v["key"]] = v["value"]
   add(rec,"Address %d - Type" % i,Labels,p["label"],hdr)
   add(rec,"Address %d - Street" % i,addr,"1",hdr)
   add(rec,"Address %d - City" % i,addr,"4",hdr)
   add(rec,"Address %d - Postal Code" % i,addr,"3",hdr)
   add(rec,"Address %d - Region" % i,addr,"2",hdr)
   add(rec,"Address %d - Country" % i,addr,"6",hdr)
   i = i + 1
   
   add(rec,"Organization 1 - Name",contact,"Organization",hdr)
   add(rec,"Organization 1 - Department",contact,"Department",hdr)
   add(rec,"Organization 1 - Title",contact,"JobTitle",hdr)
   
   # store record
   rows.append(rec)
   
   
   #print header
   for h in hdr:
   sys.stdout.write(h)
   sys.stdout.write(",")
   
   print ""
   
   # write out as rows
   for r in rows:
   for h in hdr:
   if h in r:
   sys.stdout.write('"' + r[h].replace('"', '""') + '"')
   #print r[h]
   sys.stdout.write(",")
   # end row
   print ""
   
9. Judd Says:
   August 23, 2010 at 7:46 am -0700
   
   Thanks Iddo!
   
   I'll update this post to point at your improved version. Welcome to Android. :)
   
   
10. roadt Says:
    December 25, 2010 at 10:09 pm -0800
    
    pity without the feature export to UIM of iphone. close ugh?
    But i find "copy contact to UIM" in my cheap huawei c8500 phone. strange?
    
    welcome to android. nice post :)
11. sg Says:
    April 26, 2011 at 9:29 am -0700
    
    sg:~ sg$ python2.7 export_iphone_contacts.py
    ""
    Traceback (most recent call last):
    File "export_iphone_contacts.py", line 24, in
    for p in cPhone: phones[Labels[p["label"]]] = p["value"]
    IndexError: tuple index out of range
    sg:~ sg$
    
12. steelneck Says:
    January 01, 2012 at 6:19 am -0800
    
    4K of Android phones is probably a cheap price for "buying" a number of developer harts, but of course also enabling google to have some little peak in what these important users are currently are doing. Remember, Google is a company, not a charity organization. To give away 4K phones is looked upon as an investment, exactly in what we can only spculate about, or rather guess how they thought when taking that decision.
    
    Look at your self, you are a person connected to the development af the Arch distro, currently ranked 6th at distrowatch. That makes you "somebody". Now connect that with how google just hooked you up in yet another way. If i use the Arch distro, will that somehow affect me or all the other users of that distro in the future?
13. rtt Says:
    May 12, 2012 at 3:36 pm -0700
    
    I'm getting the same error as another poster on Centos 5
    python-2.4.3-27.el5
    ./script.py
    File "./script.py", line 37
    'E-mail': emails["Home"] if emails.has_key("Home") else emails["Work"] if emails.has_key("Work") else "",
    ^
    SyntaxError: invalid syntax
    
14. Luke McCarthy Says:
    August 13, 2012 at 5:37 pm -0700
    
    Every Android phone I've seen has a standard headphone jack. I guess you just chose the wrong phone...
15. Judd Says:
    August 14, 2012 at 5:29 pm -0700
    
    When did you start seeing Android phones? If it was after 2009, then you probably missed the HTC Magic, which was the phone handed out at IO that year.
    
    https://en.wikipedia.org/wiki/HTC_Magic
    
    Thankfully, they all have headphone jacks these days.
    

Add Your Comment

 

* Your Name:

 

* Your Email:

 

Your Website:

 

 

 

* Comment:

* Enter the letters/numbers you see in this image: