So they sent you an Excel sheet with a bunch of contacts and two days later your call history looks like you are taking part in a code decipher challenge? Maybe you should have converted them to contacts in your phone? Ok, so you tried but that didn’t work: Apple Addressbook fails silently, you cannot trust the web services with customer data and after paying for some apps on the internet you discover that some of them cannot even open Excel sheets without failing. And of course no one will type hundreds of contacts into their phone manually.

But there is another way: just automate it yourself. It’s remarkably simple using Python. In this article we demonstrate how to read an address list from an Excel sheet in xlsx format (Excel Standard since 2007) and output vCard 3.0 files one can import to their phone or EMail/PIM app of choice.

Prerequisites

We use Python 3.11, vobject to write vCards and openpyxl to read Excel sheets. Both are unfortunately lacking in documentation. Install both libraries via pip. You can use a virtualenv. Other Python 3 versions should work as well. vobject only supports vCard 3.0 instead of the newer 4.0 but that is not an issue for our use case. The Wikipedia entry on vCards has a comparison of the supported parameters.

We use a Excel xlsx file with the following structure as source:

Given Name Surname Street Zip Code City Country Mobile EMail
John Doe 11 Park Way 88399 Springfield United States 834 543 789 john.doe@example.com
Example Excel File Structure

For the sake of brevity we assume all cells are filled.

Read Contact Information from Excel Sheet in Python

We open the Excel file using openpyxl and import vobject for later use.

from openpyxl import load_workbook
wb = load_workbook(filename = 'address-list.xlsx')
import vobject

If we execute above commands in a python shell we now can explore the file a bit: get the list of sheet names and select by name. Or just take a specific one.

# Show a list of sheet names
wb.sheetnames
['Addresses']
# Select a sheet by name
address_ws = wb['Addresses']
# Select a specific index
ws = wb.worksheets[0]

We iterate over the data rows and extract the address information into a dictionary. While the dictionary is not necessary, the keys make things more descriptive. The loop will iterate over all data rows starting with the second row until there is no more row with data in the sheet. Enumeration starts with 1 instead of the usual 0 because Excel is Microsoft.

for row in ws.iter_rows(min_row=2):
  vcf_data = dict(
        given_name=row[1].value,
        surname=row[2].value,
        street=row[3].value,
        code=row[4].value,
        city=row[5].value,
        country=row[8].value,
        mobile=row[9].value,
        email=row[10].value,
        display_name=f'{row[1].value} {row[2].value}'
    )

Create a vCard in Python

In the same loop as above we create a vCard for every contact in the list. We already imported vobject so we can immediately create a card:

j = vobject.vCard()

vCards are just text files where every line contains a parameter. The vobject interface resembles this line by line approach: we have to add each line before filling it with information.

We first add the display name. The parameter name is the same as in the RFC 2426: In this case fn. Then we use the information we collect earlier.

j.add('fn')
j.fn.value = vcf_data['display_name']

We use the same procedure to add name, email and a note:

# Name, both given and surname
j.add('n')
j.n.value = vobject.vcard.Name(family=vcf_data['surname'], given=vcf_data['given_name'])
# A simple text note
j.add('note')
j.note.value = 'Imported from Excel'
# E-Mail
j.add('email')
j.email.value = vcf_data['email']

Some parameters can have a type field. The allowed values are listed in the RFC 2426.

# Mobile phone number
j.add('tel')
j.tel.value=vcf_data['mobile']
j.tel.type_param = 'voice,cell'

# Home address
j.add('adr')
j.adr.value = vobject.vcard.Address(street=vcf_data['street'], code=vcf_data['code']), city=vcf_data['city'])
j.adr.type_param = 'home'

Save Individual vCard Files Per Contact

We write the new card to a cards/ subdirectory. The directory should already exist.

with open(f'cards/{vcf_data['display_name']}.vcf', 'w') as f:
  f.write(j.serialize())

And we are done. Now we can import the vCards to most PIM or phone apps.

Save One vCard Files For All Contacts

Multiple vCards in one text file should be supported. So if you don’t want to end up with hundreds of vCard files, use the same filename, like cards/all_contacts.vcf and open it with the a flag, for append. Keep the rest the same as in the section above.

Conclusion

While arguably not as quick as a conversion to vCards via a capable preexisting app, the DIY conversion via Python works well. It also opens up the possibility to post-process or sanitize the contact data before exporting it as vCards. Also: it’s free.