TechTarget Article: Getting Data Back Out of OpenOffice.org Base Databases

I've written an article for TechTarget.com on how to export data from a Base database to a spreadsheet or text file.

http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1226457,00.html

See this blog for how to import data from a spreadsheet into a Base database. (This creates a true Base database, as opposed to a database file that points to a spreadsheet.)

---------
Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export
---------

Exporting data from an OpenOffice.org Base database

Solveig Haugland
10.30.2006

Digg This!     StumbleUpon     Del.icio.us

I think somebody on the OpenOffice.org 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.

The best mail merge tool isn't on any of the menus...you 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 OpenOffice.org 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 OpenOffice.org 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.

---

Comments

[Tim Copeland]

Hi,
I'm just a random traveler at your site but I like it very much. I have used OO for several years both at home and at work. Nobody at work knows I use it because I save as doc at work. Does all I need it to which is to write. Have used OO spreadsheets a little but found them a bit buggy in the past, especially when printing. My appearance here is caused by a desire to use Base (OO 2.0.4) to track my billing in my personal business. I have set up a DB and created a report and several forms, none of which please me.
I want my form to display a single record for entry and to use drop down arrows for some fields and to appear blank for others. Here are the problems I encounter:
My billing has a date field. When I click in the form box, I want nothing to appear, then I want an autocomplete that tries to guess the month and year. Right now, the box comes up with the current days date and I have to erase it.
The start time box: It starts with a time matching the last record which has to be erased.
The remarks box: Blank but I wish it to guess the last record's entry.
I have, as I always do, searched for tutorials but found none that give me the tools to do these things. The closest I came was sheepdogguides.com but it gave me a datasheet view and only gave me the means to make a list box.
Is there a book you can suggest, if not an online resource? Thank you.
tim

[Adrian Dunevein]

Had to click through 3 links to get to the article and then it says I need to be a member to see it.

Post the damn thing here and dont make your visitors jump through hoops to get it

[Solveig]

Hi Adrian,

I've pasted it in. The membership item is something I wasn't aware of. Also please note that I object to people swearing, either at me or generally, in comments on my blog.