NewsForge

December 29, 2004

Reading external data sources with OpenOffice.org

Wednesday December 29, 2004 (08:00 AM GMT)
By: Daniel Rubio


The OpenOffice.org suite has positioned itself as a competitive alternative to proprietary products such as Microsoft Office and Corel's WordPerfect Office. One of OpenOffice.org's more advanced features is its ability to access external data sources from within its applications.

Information repositories abound in both users' desktops and in a company's IT infrastructure. OpenOffice.org offers access to a whole gamut of data sources, ranging from standard OBDC/JDBC connection capabilities to localized access into text files, spreadsheets, and address books.

The real highlight in being able to connect from OOo into a data source lies in its robust GUI for consulting and performing comprehensive searches. Users can make SQL-based queries or simple sort/filter searches and use the retrieved information within text documents or spreadsheets.

You configure OOo to access external data via the Tools > Data Sources... menu option. When you select this, a pop-up window with two panes appears. The left side contains a list of access points, while the right pane contains the parameters for configuring the name, type, and location of each data source.

OOo Data Source Administration

Let's see how this works by performing a search on data that is common to most users' desktops: the address book of a mailing application. Since OpenOffice.org is an open source suite, if you use it, chances are good you are a free software buff, so we'll work with the Mozilla Thunderbird email client. OOo can also access Outlook or LDAP address books.

From the drop down list appearing in the Data Sources... pop-up window, select the Address book option, followed by the Mozilla Address Book appearing under the Data source URL menu. The final URL should show sdbc:address:mozilla. Once you have the definition in place, select Apply and proceed to selecting the tab named Tables, which should show two items, Collected Addresses and Personal Address Book, both of which represent Mozilla's underlying address book structures.

By observing this information on the Tables tab, OOo is effectively connecting to the data source. This same tab would be populated with corresponding source data structures if you were connecting to a relational database or other repository.

Now can proceed by selecting the Queries tab and the New Query(Design View) icon, which brings up a separate window for defining our search terms.

The bottom half of this last window will contain a series of cells, each one used for defining a queries characteristics on the data source. Each row defined on the first line -- declared as Field -- will have a drop-down menu containing the fields on the selected data source table. In our case, it will contain First Name, Last Name, Email, City, State, and all the other fields as defined in the Mozilla Thunderbird environment.

The fields defined on the first row will be used for representing a top-level filter on our query, while the lower rows are employed for providing more granularity to the results, such as its sort order or a specific filter function. With the query defined, you can execute it from the top Edit menu, or save it for later reuse. Depending on your search terms, you should get a result set containing a list of organized names or terms extracted directly from your address book.

Daniel Rubio is the principal consultant at Osmosis Latina, a firm specializing in enterprise software development, training, and consulting based in Mexico.