AUTHOR'S MAIN SITE  »  »  »  TABLE OF CONTENTS for Open Office database tutorials.
-d- Bookmark this on Delicious   Recommend to StumbleUpon

Open Office Tutorials
Moving data between Open Office Applications

You may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.

Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.

There's more about ooBase in the main index to this material.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software ®, 3/06-3/10.

Moving Data Between Open Office Applications _______________

Any time you have data in an ooBase table or a query, you can, incredibly easily, and flexibly, have that same data in...

And the mechanism for all of these transfers is the same! Learn one simple trick, and you have a tool with many uses! With just the basic trick, or one other similar one, you can move data between many of the Open Office applications. You can always get something from "A" to "C". Admittedly, occasionally you have to go via "B".

How It Is Done _______________

The simplest option is to go via an ooCalc worksheet. You "paste" (as in "copy and paste") to an ooCalc worksheet. If you want to do this from an ooBase table, it "just happens", troubling you with no choices. You right-click on the entry for the table (or query) in the ooBase project management window, click "copy", move to an ooCalc page and "paste". (You can fine tune the results after the data is in the worksheet.)

Data from ooWriter to ooCalc: If your data is in a table in ooWriter, you simply select the columns and rows you want, press ctrl-C (to copy), move to an ooCalc sheet, click on the cell which will be the upper left corner of the table in the ooCalc sheet, and then press ctrl-V (to paste).

Data from ooBase to ooCalc: If your data is in an ooBase table or query, just go to the ooBase main project manager window, right click on the name of the table or query (don't try this for a form, it won't work, or for a report unless you want to explore things I haven't explored!), click on "copy", and then proceed as before: Go to an ooCalc sheet, click on the cell which will be the upper left corner of the table in the ooCalc sheet, and then press ctrl-V.

Data from ooBase to ooWriter: If you are copying an ooBase table or query to an ooWriter document, you can paste the data as a table, or as fields, or as text. "As a table" will be the answer in many cases. (I must remember to come back and explore the "Fields" option, though... I think it will be great for "clever" things!) The "as text" option allows you to "assemble" a small template, and "rubber stamp" things into the ooWriter document with layout and headings pre-determined (by you). The "stamp" can be used again and again, once for each record, with "bits" filled in from the database. Again... another time, I'll try to come back to this for you!)

If copying an ooBase table or query "doesn't work", be sure you are "only" trying to use copy/ paste on a table or a query. You can't copy/ paste forms. (When you try, there's just no result. I doubt you can copy/ paste reports, either.)

Data from ooBase to ooImpress: If you are copying the table or query to an ooImpress presentation or ooDraw document, you can "only" paste the data as a table. Before you complain... write the code to make the office suite to even that. Do the users of Micro$oft's office suite transfer data between apps this easily? (And, oh by the way, remember that for many users, the database "for MS Office", is Access, which costs even more, on top of the purchase of the other applications.) (While I'm beating up on Micro$oft, let me mention that I've learned from someone who knows that current versions of Excel no longer come with built in map charting functions. Now, Open Office doesn't have built in map charting, either, but there's a good plug-in for about $10. I bet the Excel third party add-ons cost more than that. Of course, they'll probably do "everything"... but do you want to pay for things you'll rarely use?)

Doing it... keystroke by keystroke ______________

By now you probably suspect that you can also put a copy of the table or query into a different database, too. The copying trick works very slightly differently if you want to copy from a database to a database, but the differences are so trivial that I won't insult your intelligence by writing them up.

That may be all that you need. However, even more needs can be met when you know that any data in an ooCalc spreadsheet can be saved to a variety of formats (duh!)... but remember (or learn!) the advantages of the CSV format.

Either open an existing database, or put together something simple as a little test. You only need to have one table and one query extracting data from that table.

Don't shut the database, but do close any windows showing what's in the table or query. (You don't have to do this, but I want you focused on the essentials here.

The ooBase main project manager window should still be visible. (I.e. The three pane window with a "Tasks" pane in the upper right.)

Click on "Tables" in the "Database" window along the left edge of the ooBase main project manager window.

The lower right pane should now be "Tables", and in it you should see the name of at least one table. Right click on one of those names, and then click "Copy" from the menu that comes up.

Now start a new spreadsheet.

Right click in one of it's cells.

Click on "Paste". Boom! The table you decided to copy appears in the ooCalc page. Each record a row, each field a column. And the new stuff in the worksheet even appears under headings created from your database table's field names!

Don't close ooCalc just yet. We'll want the worksheet we just created when we return to the topic of CSV files.

Start a new (or open an existing!) ooWriter document.

Right click somewhere on the "page".

Do ctrl-V (or right-click, and click on "Paste").

A dialog will come up. For now, choose to insert the material from the database as a table.

There will be two panels on the dialog. The names of the fields of your database table or query will be in those windows. The fields in the left hand window are available. The ones in the right hand window are fields which will be in the table- of the ooWriter document- which you are creating. (I'll call that "the wp table" from now on, to distinguish it from the table (or query) in the database which is the source of the paste we are doing.)

To move fields between the two windows, use the button(s) between the two panels on the dialog to move fields between the "available" and "to go to the wp table" lists.

Don't worry about the other choices at the bottom of the dialog. Just click "OK", and you should find that your ooWriter document has gained a table, filled with data from your database. It's just an ordinary ooWriter table, and you can tweak it to your heart's content. Don't, however, expect it to be "magic". Changes to the data in the database which are made after you've copy/ pasted the data across to the ooWriter document will not appear in the ooWriter document. (If you want that sort of "magic", I suspect you can get it if you master the "Insert data as fields" mechanism. You'll probably, when needed, have to do something to trigger re-population of your ooWriter document with freshly collected data.)

If you try to open a CSV file from ooWriter, ooDraw or ooImpress, you will get the dialog box we spoke of previously, but the CSV file opens in ooCalc. You then select the portion of the sheet that you want, do copy, move back to the main application, and do paste... and find you've inserted an object into what you were working on. I'm not very clever about working with objects, so can't tell you a lot about that... except that I think "too much" is "going on". I don't want to work with objects! So here's my "solution":

Select as much of the table as you want.

Right click.

Click on "copy"

Go into ooBase... use a "scratchpad" database, or remember to clean up after yourself.

As we did so long ago at the start of this....

In the ooBase main project manager window, click on "Tables" in the "Database" pane, and then right click in the "Tables" pane at the lower right.

Click on "paste".

Answer the questions, thus creating a new table.

You don't even have to open it.

Once it is created, right-click on it, click "copy".

Go back to ooWriter, ooImpress or ooDraw. Right-click and click "Paste". Presto! Your table is inserted as a table, not as a nasty(?) old object!

Just in closing: a quick tip for ooWriter. If you are having trouble deleting a table, call up the "Navigator" from the edit menu (or press F5). Drill down to the document's tables. Right click on the table you want to delete. Click on "table" in the sub-menu, and click the "Delete" that becomes available!

Where now? _______________

This is one of at least four pages discussing CSV files in my ooBase tutorials. In this page, you were shown about moving things between OpenOffice applications.

Other pages....

... and of course there's also the site's main menu!

Editorial Philosophy

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.

Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE

If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.

Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at

One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .