Problem solve Get help with specific problems with your technologies, process and projects.

Exporting data from an Base database

There is no clear-cut method for exporting data from OpenOffice 2.0's Base database, but you can learn how and get some handy shortcuts in this tip. In a snap, you'll get table data into a Writer document and turn it into a spreadsheet or delimited text file.

I think somebody on the database development team has a mystery fetish. He or she read too many...

murder mysteries or hardboiled detective novels early in life or worked for too many years as the White House Easter Egg hunt director. Whatever the reason, this apparent fetish shows up in the Base module.

More OpenOffice 2.0 Base tips:
OpenOffice 2.0 Base: Getting the right views

How to use OpenOffice 2.0's database tool

The best mail merge tool isn't on any of the have to hunt it down and add it. You can easily get data into Base...but you won't find it by looking for a File > Import command. And if you want to get data out of Base....well, it's possible, but certainly not under anything as logical as File > Export.

This keeps me busy writing articles to bring all the mysteries into the clear light of day. This article is all about how to actually export data from your Base database.

Why export data in the first place?

You don't necessarily need to export data from Base. If all you do is enter data and pop it out in reports, you're fine. But let's say you need to submit your data to, oh, the IRS or another government agency. Or suppose you work with another organization that uses a different database. In this case, you'll need to provide your data to that group in a form they can read. That format is usually a spreadsheet or a delimited text file. This article will walk you through how to do it.

The technique is a hack, of course. (The way it's supposed to work....doesn't work.) But as hacks go, it's not horrible. You take the data from your table, view or query; bring it into a Writer document; then massage it to become a spreadsheet or delimited text file.

Step 1: Prepare the data

If you want to export all the data in a table, you can skip this. But if you need to export only certain records or certain fields, you need a query or view to restrict the data appropriately. I'm not going into detail about that here; see this article to create one.

Once you've got a query or view, complete this step to update and make sure your query or view contains the latest data.

1. Open the database file (File > Open and select the .odb file).

2. Click the Tables icon at the left to view views; click the Queries icon at the left to view queries.

3. Double-click the view or query.

4. When it opens, click the Refresh button.

5. Close the view or query and database, saving changes.

Step 2: Bring the data into a table in a text document

1. Create a new Writer text document (File > New > Text Document).

2. Choose View > Data Sources.

3. Expand the database and view the table, view, or query to get the data from.

4. Click the blank square in the upper left corner to select all.

5. Click and hold down on the blank square; keep your mouse held down.

6. Drag your mouse into the text document and release your mouse.

7. The Insert Database Columns window will appear.

8. In the window, leave Table selected, and click the >> button to move all fields to the right side of the window.

9. Click OK.

A table will appear containing all the data.

Step 3: Creating a spreadsheet or delimited text file from the data

We're on the home stretch now. If you want the data in a Writer table, you're done. But if you want it in a spreadsheet or a text file, follow these steps.

1. Select the entire table, including the headings.

2. Copy.

3. Create a new spreadsheet (File > New > Spreadsheet).

4. Click in the upper left corner.

5. Paste. The data will appear.

6. Resize the column widths as necessary.

7. Save the spreadsheet.

8. If you need to create a delimited text file, choose File > Save As and select Text CSV as the file format. Specify the name you want for the text file and click Save.

How it's supposed to work (but doesn't)

I'm including this because this might work sometime soon, and because the online help tells you to do it this way. I would still, of course, prefer that the next rev of Base include something simpler, like a File > Export function.

1. Open up your database file, then open your table.

2. Click the upper left blank square to select all, or Shift+Click to select certain rows.

3. Copy.

4. Paste into a spreadsheet.

But, as I said, this doesn't currently work in 2.03 or previous versions. (I haven't tested it on 2.0.4 yet, since it crashes each time I start it on both my computers.)

Another mystery revealed

Digging the hidden treasures out of is actually kind of fun. I enjoy donning my virtual trenchcoat and dark glasses. (I'm already planning who will play me in the movie, tentatively titled All Suarez-Potts Programmers.) Of course, it's not that fun for frustrated users; I hope that the annoyance and frustration among users is at least somewhat offset by my revealing the mysteries of the Base module here.

Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 15 years, for employers including Microsoft Great Plains, Sun Microsystems,and BEA. Currently, Solveig is a StarOffice and instructor, author, and freelance technical writer. She is also co-author, with Floyd Jones, of three books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published by Prentice Hall PTR. Her fourth book, on 2.0, is coming this summer. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.

Did you find this tip useful? Email us and let us know.

Dig Deeper on Linux servers