One Way to Export Data From OpenOffice Base to a Spreadsheet

In OpenOffice.org 2.2 you can now export data in a reasonable manner. Here's one way in 2.4.

Here's another way you might try. It's almost identical to the (albeit invisible) import method.

1. Open your database .odb file.
2. Click the Tables icon.
3. Right-click on the name of the table to export. Choose Copy.
4. Go to a spreadsheet. Paste.

The data will appear.

---

Comments

[Keet Bugrows]

When I try this my column headers all end up in alphabetical order but the columns stay in the same place, effectively screwing up the spreadsheet.

Keet

[Dave]

This system works well with smaller tables, but when trying to get a nasty imported mdb into a spreadsheet to then export into mysql, it seems to have frozen completely. There are 18 thousand records, so its probably best to use with discretion.

[iain goldmann]

this has been fantastically useful.

One other question: is there a way to get a query result data out of open office base, either through making a table from a query or otherwise?

[Solveig]

Hi Iain,

It should work; just click the Queries icon at the left side of the window, then copy the query you want instead of a table.

Solveig

[Jared Starkey]

HAH! This is to simple and elegant - I love Open Office!

[Jason]

I agree with keet...this method is totally broken for large tables or queries...my query had ~6000 records, pasting into calc took a long time (I thought it might crash but it made it after about 10 minutes)...but even after it finished, round about record # 4500, the data failed to copy...after that the only record that was pasted correctly was the last one. Weird.
The same thing happened in 2.0 and 2.4.1

The better solution is to register you DB in OO, create a query, then save and close base. Then open a new Calc spreadsheet, and in the view menu click "Show Data Sources".
Select your query on the left, the data will appear on the right. Click on the upper left corner of the data to select all and then drag over the spreadsheet below. Be patient and all your data will appear...with column headers! This works almost flawlessly in 2.4.1, and I cant be happier...I've been looking for a way to export from Base (one that works) for years...hoorah!

Anyway, I hope that helps somebody. Enjoy :)

[Gary Winnick]

Nice post.

[Igor Razbornik]

Yes, this late information in the most reliable way. In OpenOffice 3.0 you can click on the top of the DataSources pane icon "Data into text" and your data will appear in the spreadsheet. Most elegant way.

Thank you for this tip,
Igor

[mcahornsirup]

Nice... exactly the trick I was looking for!

[Emily]

The same thing works for queries. Thanks so much! This information was surprisingly hard to find!