Accessing Thunderbird address book from LO 7.5.8.2

English
#1

I am running LO 7.5.8.2 and Thunderbird 115.5.1.

I want to access the addresses held in the Thunderbird Address book. When I File>Wizards>Address Data Source, and select Thunderbird as the address book type I receive the message

The connection to the external data source could not be established. No SDBC driver was found for the URL ‘sdbc:address:thunderbird’.

Following advice found on line for a similar problem I have installed the extension HsqlDriverOOo, but it makes no difference.

I am using Windows 10 versions of both software.

Is there a cure this ill?

#2

No and yes :wink:
.
No, because Thunderbird changed its format for the address-book. Therefore you can not find the okd address-book in a newer Thunderbird (You could try an old version, but nothing to recommend…)
.
Yes, because the new one is a database of SQLite-type. This can be accessed via ODBC-driver with LibreOffice Base.

An example you may find in the following german Thread:

#3

Thanks!

I will investigate this.

Grace.

#4

For some reason, all the data are stored as property values.
The table contains 3 columns:

  1. card (unique identifier)
  2. name (property name such as “LastName”, “PimaryEmail”)
  3. value (the property value)

The following query transforms this layout into a normalized table as far as it covers the existing properties of my tables:

SELECT DISTINCT "card", "AllowRemoteContent"."V" AS "AllowRemoteContent","CellularNumber"."V" AS "CellularNumber","Company"."V" AS "Company","Custom1"."V" AS "Custom1","DisplayName"."V" AS "DisplayName","FaxNumber"."V" AS "FaxNumber","FirstName"."V" AS "FirstName","HomeAddress"."V" AS "HomeAddress","HomeCity"."V" AS "HomeCity","HomeCountry"."V" AS "HomeCountry","HomePhone"."V" AS "HomePhone","HomeState"."V" AS "HomeState","HomeZipCode"."V" AS "HomeZipCode","JobTitle"."V" AS "JobTitle","LastModifiedDate"."V" AS "LastModifiedDate","LastName"."V" AS "LastName","NickName"."V" AS "NickName","Notes"."V" AS "Notes","PhotoType"."V" AS "PhotoType","PhotoURI"."V" AS "PhotoURI","PopularityIndex"."V" AS "PopularityIndex","PreferDisplayName"."V" AS "PreferDisplayName","PreferMailFormat"."V" AS "PreferMailFormat","PrimaryEmail"."V" AS "PrimaryEmail","SecondEmail"."V" AS "SecondEmail","WebPage1"."V" AS "WebPage1","WorkAddress"."V" AS "WorkAddress","WorkCity"."V" AS "WorkCity","WorkCountry"."V" AS "WorkCountry","WorkPhone"."V" AS "WorkPhone","WorkState"."V" AS "WorkState","WorkZipCode"."V" AS "WorkZipCode","_JabberId"."V" AS "_JabberId","_vCard"."V" AS "_vCard" FROM "properties" AS "P"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "AllowRemoteContent") AS "AllowRemoteContent" ON "P"."card" = "AllowRemoteContent"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "CellularNumber") AS "CellularNumber" ON "P"."card" = "CellularNumber"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "Company") AS "Company" ON "P"."card" = "Company"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "Custom1") AS "Custom1" ON "P"."card" = "Custom1"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "DisplayName") AS "DisplayName" ON "P"."card" = "DisplayName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "FaxNumber") AS "FaxNumber" ON "P"."card" = "FaxNumber"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "FirstName") AS "FirstName" ON "P"."card" = "FirstName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeAddress") AS "HomeAddress" ON "P"."card" = "HomeAddress"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeCity") AS "HomeCity" ON "P"."card" = "HomeCity"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeCountry") AS "HomeCountry" ON "P"."card" = "HomeCountry"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomePhone") AS "HomePhone" ON "P"."card" = "HomePhone"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeState") AS "HomeState" ON "P"."card" = "HomeState"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeZipCode") AS "HomeZipCode" ON "P"."card" = "HomeZipCode"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "JobTitle") AS "JobTitle" ON "P"."card" = "JobTitle"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "LastModifiedDate") AS "LastModifiedDate" ON "P"."card" = "LastModifiedDate"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "LastName") AS "LastName" ON "P"."card" = "LastName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "NickName") AS "NickName" ON "P"."card" = "NickName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "Notes") AS "Notes" ON "P"."card" = "Notes"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PhotoType") AS "PhotoType" ON "P"."card" = "PhotoType"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PhotoURI") AS "PhotoURI" ON "P"."card" = "PhotoURI"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PopularityIndex") AS "PopularityIndex" ON "P"."card" = "PopularityIndex"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PreferDisplayName") AS "PreferDisplayName" ON "P"."card" = "PreferDisplayName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PreferMailFormat") AS "PreferMailFormat" ON "P"."card" = "PreferMailFormat"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PrimaryEmail") AS "PrimaryEmail" ON "P"."card" = "PrimaryEmail"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "SecondEmail") AS "SecondEmail" ON "P"."card" = "SecondEmail"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WebPage1") AS "WebPage1" ON "P"."card" = "WebPage1"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkAddress") AS "WorkAddress" ON "P"."card" = "WorkAddress"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkCity") AS "WorkCity" ON "P"."card" = "WorkCity"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkCountry") AS "WorkCountry" ON "P"."card" = "WorkCountry"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkPhone") AS "WorkPhone" ON "P"."card" = "WorkPhone"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkState") AS "WorkState" ON "P"."card" = "WorkState"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkZipCode") AS "WorkZipCode" ON "P"."card" = "WorkZipCode"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "_JabberId") AS "_JabberId" ON "P"."card" = "_JabberId"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "_vCard") AS "_vCard" ON "P"."card" = "_vCard"."C"
#5

Hi,

Can you give me a link to this.

I have installed three extensions, ODBC, SQLite, and I’m still getting the message “No SDBC driver was found for the URL sdbc:address:thunderbird”.

It feels as if something somewhere is hard coded, and cannot be changed.

Grace

#6

Your database tries to use the old built-in driver which does not exist anymore.
Create an ODBC connection on system level.
Create a new database, connect to existing database of type “ODBC” and specify the name of your ODBC connection.

For JDBC, go to Tools>Options>Advanced > [Class Path …] and point to the JDBC driver, some file like sqlite-odbc.jar.
Then connect a new database document to a database of type “JDBC” and specify the database path with sqlite: as protocol like this: sqlite:///path/thunderbird/pofile/abook.sqlite.

The JDBC driver class is: org.sqlite.JDBC

#7

With win10 all I installed is the ODBC-driver from the following site (pay attention to 32/64 bit you need the same as your LibreOffice, today mostly 64 bit):
http://www.ch-werner.de/sqliteodbc/

Install the driver first. Then use the Windows Start-Menu and find the ODBC software (usually I only type ODBC and wait, what pops up). Here you create a odbc-connection of type sqlite3 (usually last in the list) to the address-file of thunderbird abook.sqlite (collected mail addresses in history.sqlite). You may name it NewTB.
.
Now LibreOffice can connect to this. Create a new .odb and connect to an existing database, type is now ODBC and your NewTB will be offered to you. In this .odb-file you can now see the tables in the new address database. If you register this datasource (.odb-file) in LibreOffice you can access it from all modules of LO, where it may be useful.
.
As @Villeroy already noted: This tables are not easily accessed by human beings, but you can use above suggestion to join everything in an easier table…

#8

Well, thanks for all your help but I’m really not any further forward.

I have followed your instructions but I am confused by why I see. I don’t see the names and addresses just strings of all the data. I am able to progress further by exporting the address book to a .csv and using that.

Something else I didn’t think of before I started this, was that my main address book is a remote one, so does not have a .sqlite file in the folder.

So I guess I will just export it to a .csv whenever I need too, and access the data from that.

Thanks again for all your help though.

#9

In wich folder did you look? Installation of Thunderbird or your profile below hidden %APPDATA% ?
.
But if you see segments like
12345 Name Wanderer then you actually found the address-book and the next step would be to use Villeroys query to get a usual table.

#10

I just tested this on a Windows box.
Installed the 64-bit driver for Windows from SQLite ODBC Driver
Called the Windows ODBC manager and added a new ODBC data source named “Thunderbird” based on the SQLite3 driver and pointing at database %APPDATA%\Roaming\Thunderbird\rbu9i52j.default\abook.sqlite where rbu9i52j.default contains my personal user data. The file abook.sqlite is Thunderbirds “Personal Addressbook”. There may be alternative databases abook-1.sqlite, abook-2.sqlite etc. if you created more address books in Thunderbird. history.sqlite contains the address book with “collected mail addresses”.
Connect a new Base document to an existing database of type “ODBC” and enter the ODBC name (“Thunderbird” in my case).
Create a new query in SQL view, paste my SQL statement, save the query and the database.
Use the query for your serial letters and for labels.
In order to access different Thunderbird address books (personal, collected or your own ones), you can add more ODBC sources and more Base documents or you can call the Windows ODBC manager and point the existing ODBC connection to another file history.sqlite or abook-1.sqlite. The database document will show the other database then.

#11

I then created the Base document, and the table “Properties” looked like this. All other tables are blank. As a newby, I can only insert one item.

3Z0kZKEvZpKCwLza.png
3Z0kZKEvZpKCwLza.png1193×221 40.8 KB

#12

Copy my SELECT statement.
Go to the queries section and create a new query in SQL view.
Paste the statement into the query.
Save the query.
Save the database document.
Use the query as source of your mail merge, labels etc.

From that query you may derive another query with a little help from the query designer with columns and rows you are interested in, leaving out blank columns and incomplete addresses.

#13

Thank you so much!

I hadn’t realised that I would have to go back to the topic to see the select statement.

Again, many thanks,

2 Likes
#14

As Villeroy pointed out, it is possible to use jdbcDriverOOo (java 11 is required) to share the Thunderbird address book.
It requires a little adjustment to allow sharing. For more information see SQLite in LibreOffice / OpenOffice Base on the OpenOffice forum.

#15

Thunderbird_JDBC.odb (75.5 KB) can be used as a “template” together with jdbcDriverOOo | The new pure Java JDBC type 4 to UNO SBDC driver
HOWEVER, you must not use it to edit the address book database by any means. I made all forms read-only, but you may be able to edit tables directly, since I could not find any way to establish a read-only connection to the database.
No macros, no bullshit. Just the pure connection based on the extension driver which is more easy to use. I added the above mentioned query, some potentially useful forms and reports. All you need to add, is the path-name of your database file abook.sqlite in the profile folder of your Thunderbird application. I tried my best to write detailed instructions in the README form of that document.

1 Like
#16

Apart from the Java version, there is another glitch: You need to remove any references to other sqlite*.jar in the LibreOffice class path settings.

#17

Great Thunderbird_JDBC.odb file…
I have just published jdbcDriverOOo version 1.1.5 and it allows you to edit views in SQL mode once the view has been created. I was able to copy the contents of the _MAIN query into an SQLite view and it took 4 times less time to open in Base…
Very interesting…

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.