Creating a Contacts Database in OpenOffice

Last week we learned how to make pages of labels, business cards, and form letters using OpenOffice Writer and Base. The steps for setting up any mail-merge document in OpenOffice are easy; the one potentially gnarly bit is creating your contacts database in the first place. Your contacts list must be in Base, which is a bit of a pain. But the good news is Base can import data from most other databases, spreadsheets, text-delimited files, and email address books. Figure 1 shows the import screen. Yours may have some different options, depending on your Linux distribution.

Base can connect directly to most relational databases, such as MySQL, PostgreSQL, Oracle, Adabas D, or any database that supports the Open Database Connectivity (ODBC) or Java database connectivity (JDBC) drivers. So it also functions as a graphical front-end to other databases, and you can use it to edit and change them. Address books, spreadsheets, and delimited text files are read-only. You can import their data into Base, but whenever you make a change to the source document you have to import the new data; you can’t change it or directly access it from Base.

My contacts are in a KDE address book. Importing this into Base is so easy your cat can do it:

• File – New – Database
• Connect to an existing database
• Yes, register the database for me and open the database for editing
• Click finish, name and save the new file
• You will see something like Figure 2. Double-click on the Address Book table to see your imported data

If Base doesn’t have an option to directly import from your address book, try exporting your addresses into a comma-delimited .csv file. Then in the “Connect to an existing database” dropdown list, choose “Text”. The next screen asks for the path, be sure to check “‘Comma separated value files ‘ (*.csv)”, and then select the correct delimiters. If it is a correctly-formed .csv file Base will have no problems with it. Here is an abbreviated example from my KDE address book export:

"Family Name","Given Name","Honorific Prefixes","Home Phone",
"Home Fax","Home Address Street","Home Address City",
"Home Address State","Home Address Zip Code"
"Schroder","Carla","Ms.","","","5678 EndOfTheLineLane",
"Red Cowtown","Oregon","54321"

Even though there are line breaks for readability, the real line breaks are at the ends of the lines with no commas. You have to have the exact same number of fields in your field definitions line, which is the first line in the file, and in your data lines. In this example there are nine. Any empty fields need “” for a placeholder, and each field is separated by a comma. You can use a comma, single space, tab, colon, or a semi-colon for the delimiter.

You need special drivers to connect to a MySQL or PostgreSQL database, or any other external database. In Debian (and the *buntu family and Mepis and all of its many offspring) you need the unixodbc package for ODBC support, plus the driver specific to your database:

• odbc-postgresql for PostgreSQL
• libmyodbc for MySQL
• libsqliteodbc for sqlite
• mdbtools for MS Access
• tdsodbc for Sybase SQL and MS SQL

Another option for PostgreSQL is the openoffice.org-sdbc-postgresql package, which replaces both the ODBC and JDBC drivers.

You can also use the JDBC drivers:

• openoffice.org-sdbc-postgresql
• libpg-java for PostgreSQL
• libmysql-java for MySQL

Fedora has different package names, and it does not include as many as Debian:

• unixODBC.i386
• postgresql-odbc.i386
• mysql-connector-odbc.i386
• postgresql-jdbc.i386
• mysql-connector-java.i386

Any drivers that you can’t find in your distribution can be downloaded from OpenOffice.org (see Resources), or from the vendor of your database. What’s the difference between ODBC and JDBC drivers? There are some differences in speed and functionality. JDBC drivers are cross-platform, though I’m not sure why that should matter to anyone but developers. If you have problems connecting Base to an external database, try exporting just the data you want to use into a .csv file, and then import that into Base. Base loves correctly-formed delimited text files, and it’s an easy hack for non-database gurus.

Creating a new contacts database from scratch isn’t bad at all- as usual, the tedious part is the data entry. “Open File – New Database – Create a new database- Register the database for me and open the database for editing”. Base has a pretty good wizard; click on the Tables icon and then select Use Wizard to Create Table. This contains a batch of templates for all different types of databases, including Contacts, like in Figure 3. Note that there are up/down arrows for putting your fields in the order you want.

Anytime you want to do a clean do-over, go into Tools – Options – OpenOffice.org Base – Databases to delete any databases you want to get rid of. You can’t just delete them via the usual means, like from a file manager or using the rm command, because databases are registered with Base. So when you remove one it must be un-registered.

Resources

• OpenOffice.org
• Documentation/Database
• Category:Base Documentation
• OpenOffice.org

Carla Schroder is the author of the Linux Cookbook and the newly-released Linux Networking Cookbook, and is a regular contributor to LinuxPlanet.

This article was first published on LinuxPlanet.com.