Printing Mailing Labels

From Your Thunderbird Address Book

What could be easier, right? Print a bunch of labels from my address book in Thunderbird.

My email files in Thunderbird go back to 1994 (72623.1675@compuserve.com) but have been keeping plain text label files all that time

What's a label?

What's a label?

Simply put, we want a plain text file, with individual addresses spaced on six-line increments.

On a standard printer, the default line spacing is ⅙" (six lines per inch) and the default pitch is 10cpi (“Pica”).

Mail file format

Formerly, Mozilla Thunderbird stored:

  • Address Book Data (.mab files) and
  • Mail Folder Summaries (.msf files)

in a textual database format called "Mork" designed by David McCusker <davidmc@netscape.com>

Mork?

Mork, unfortunately, is not a friendly format... (2014) — https://wiki.mozilla.org/Address_Book 

// <!-- <mdb:mork:z v="1.4"/> -->
< <(a=c)> // (f=iso-8859-1)
  (B8=Custom4)(B9=Notes)(BA=LastModifiedDate)(BB=RecordKey)
  (BC=AddrCharSet)(BD=LastRecordKey)(BE=ns:addrbk:db:table:kind:pab)

<(86=1)(87=Wayne)(88=Miller)(89=Wayne R. Miller)
  (95=wayne@foamliteconcrete.com)(32B…

As of Thunderbird v78 circa 2021, however, ordinary SQLite database files replace the "Mork" format ones.

The new database files are:

  • abook.sqlite – Personal Address Book
  • history.sqlite – Collected Address Book

If you have address books in the old file, you can create a new address book and, as of Thunderbird 102, easily import your old file into the new one:


This script works with those files, automatically finding the default Thunderbird profile

Although the data is in an ordinary relational database, its organization is as

  • Record GUID
  • Key
  • Value

…so we have to reconnect all the key/value pairs for a given record.

Also, the keys themselves can be highly variable.

And, sometimes all the data we want is embedded into a VCARD (RFC 6350) instead of in the usual key/value pairs!

Here's the database layout: 
sqlite> .schema
CREATE TABLE properties (card TEXT, name TEXT, value TEXT);
CREATE TABLE lists (uid TEXT PRIMARY KEY, localId INTEGER, name TEXT,
                    nickName TEXT, description TEXT);
CREATE TABLE list_cards (list TEXT, card TEXT, PRIMARY KEY(list, card));
CREATE INDEX properties_card ON properties(card);
CREATE INDEX properties_name ON properties(name);
And some sample data: 
sqlite> select * from properties limit 10;
942d11a9-1249-4260-a56a-b3dd4536a0e4|DisplayName|The View Camera Store
942d11a9-1249-4260-a56a-b3dd4536a0e4|PreferMailFormat|0
942d11a9-1249-4260-a56a-b3dd4536a0e4|AllowRemoteContent|0
942d11a9-1249-4260-a56a-b3dd4536a0e4|PopularityIndex|22
942d11a9-1249-4260-a56a-b3dd4536a0e4|PrimaryEmail|info@viewcamerastore.com
942d11a9-1249-4260-a56a-b3dd4536a0e4|FirstName|The View Camera
942d11a9-1249-4260-a56a-b3dd4536a0e4|LastName|Store
942d11a9-1249-4260-a56a-b3dd4536a0e4|LastModifiedDate|1308358577
8d61dcf9-2da3-4972-adea-7d5b29d76e11|PreferMailFormat|0
8d61dcf9-2da3-4972-adea-7d5b29d76e11|PopularityIndex|0

And here's what we want:

Andrew Wood
 1987-743 Kitakaruizawa
Naganohara-machi
Agatsuma-gun, Gunma,  
Japan 377-1412

Ernie Workman
1743 W HIGHLAND AVE
PHOENIX, AZ 85015



Sean Bryant
322 N. Elm Street
Argonia , KS  67004

The script so far can output

  • CSV
  • JSON
  • 6-line address labels

Address book names

Each address book also has a name, and we have to parse prefs.js in the Thunderbird directory to find them

Ideally we would more fully parse that file to find the address book files themselves but I just use a wildcard

Database connection

Perl's CPAN has thousands of modules

There's an excellent Mojo::SQLite to handle database queries

But Thunderbird, when it is running, locks the db files, rendering them un-openable even for read-only access

Yay, shell-out commands

With SQLite now it is easy to output useable JSON

sqlite3 file:$abook?immutable=1 \
"SELECT card, \
'{' || group_concat(json_quote(name) || ':' \
|| json_quote(value) ) || '}' \
FROM properties group by card;"

Then we have to process fields like HomeAddress versus HomeAddress2 and on to WorkAddress and so on

Normalizing vCard and Thunderbird

  • FirstName -vs- given_names()
  • LastName -vs- family_names()
  • JobTitle -vs- titles()

As well as PrimaryEmail and SecondEmail

This also handles vCard's versus Thunderbird's HomePhone, WorkPhone, CellularNumber and FaxNumber

…even if we don't need that just for simple address labels…

Can you see the layers of contact concepts peeled back through decades?