OpenOffice Base: for-your-eyes-only query result

I'm familiar with various kinds of user interface, I've been learning to use the electronic equipment in '80s and using command line as well as complex GUIs like ProTools. It's really difficult to confuse me with an user interface.

I wanted to do a simple thing today: make a query in OpenOffice Base and move the resulting table to OpenOffice Calc for further processing. I created a query, executed it and saw a nice table. Great. Now I want to move it to Calc. How? I tried few usual ways of completing this kind of task.

  • Select the table and copy it. I looked for the select all option in the Edit menu. It's not there. I try CTRL+A, no effect. I try to select one column by clicking on the column header. It works, the whole column is highlighted. Now I need only to select the remaning columns, but it's impossible.
  • Investigate a context menu of the query. First item is "copy". Great, I clicked it, opened Calc and pressed CTRL+V. The message popped up and said that it was impossible to paste the clipboard contents. Next options are delete, rename, edit, open, create as view, form wizard, report wizard. No save as or export. I try to open, edit, I get to the point where I can't select the whole table.
  • Use a report wizard. Maybe it's a problem of my OpenOffice installation, but it just opens OO Writer with some empty heading and there's no wizard.
  • Use a form wizard. The wizard pops up this time. I select all the fields, choose the table view, open the form. I see a nice table and I want to copy it, but again, it's impossible to select the whole table. I look at the menu bar in search for select all or other kind of function that would allow me to use the query result.

I really wish that OpenOffice was a good tool, I use it and I recommend it to people. But this was all frustration and a waste of time.

UPDATE: There is something called a Data Pilot that should import a table or a query result to OO Calc. I tried to use but but had no luck. I just wanted to import the table exactly as it appears in the query, but Data Pilot showed a confusing user interface and I ended up with something that didn't even resemble the original table.

UPDATE: As an anonymous reader suggested, I created a query, saved it, right-clicked it, selected COPY, opened a new spreadsheet and choosed Paste… Voila! Thanks!


Author: automatthias

You won't believe what a skeptic I am.

8 thoughts on “OpenOffice Base: for-your-eyes-only query result”

  1. Try this.

    Save your query in base as say query1.Right click on query 1 in the query pane and left click on copy.

    Go to an open spreadsheetin calc,right click and then left click on paste and your query appears as a table in calc.

  2. There should be also another solution, on I don’t know, nevertheless, if it is a Linux or Win solution… with the Mac, it’s not a solution.
    It is based not on exporting from OOoBase, but in viewing from OOoCalc. For Mac users, that have not the F4 key, they must click on View -> Data Sources -> then select the database and the Query (if they have registered it in the initial steps of creating a database). Then you see the data on top of you Calc page but… … if you Select All … you select the Calc page, not the Query result 😦 … If you use the Shift+cursor key solution for select all the rows, when you paste, you obtain only the last cell… (nor Paste special improve the result… it do nothing). If you move a column with drag and drop, Calc understand it must make a FORM!! If you right-click and Edit the database file, you return to Base 😦

    I too, I’ve never seen, in twenty years of use of computers, a more complicated and time-wasting interface…

  3. Very helpful. Experiencing same problem.

    However, all columns pasted have format ‘number’ – so leading zeroes of text columns (as defined in database) disappear. Also alignment is varied.

    Any suggestions?


  4. Export a query to OOcalc:
    In calc click on button “data sources”.
    Expand your database and select query.
    Select the rows you want to copy.
    Click on the button “Data to text” (5 black lines and one green horizontal line)

  5. I’ve found a way, and it’s not too painful. Like the author, I was struggling to get data out, with the additional problem that I can’t save my query! I can only test it, pending clarification from paranoid and non-tech-literate Higher Powers.

    When running a query in OpenOffice Base (I’m using 2.4 on Windows, can’t vouch for other versions/systems), get a blank Calc spreadsheet ready. When the query is run, and you have the first few rows displayed, **click and drag the blank square in the very top-left corner of the results set**. Drag that into Calc and drop it on an empty cell. It might take a minute or two on large results set, but your whole query result set should appear in Calc!

    Took me ages to figure that out 😉 Posting on the off-chance someone (like me a few days ago) comes across this post by a search

  6. Hello
    I m french (my english is bad…)
    i use Openoffice 3 (french version) . It’s possible with a button names “données dans le texte” (translation : data in the text?”) . You can modify this import with the menu “Data” > definir la plage (“define the zone, the space” ? )
    You can refresh the different zone with “Data” > actualiser la plage” (sorry you’ll have to the translation: actualise?refresh? It is the last entry in the menu “data”).
    I hope it will help someone…

    En français : dans Openoffice 3 – > dans la zone “source de données” il existe un bouton “Données dans le texte” qui permet l’import d’une requête dans calc ( à la manière de msquery interfacé avec excel) .on peut aussi aussi, après sélection de la requête, faire un glisser-déposer de la vue vers la cellule du tableur en sélectionnant la cellule vide dans le coin tout en haut à gauche dans la vue des données (ce qui va griser toutes les données). On peut modifier cette importation via le menu (barre d’outils du haut) “Données> Définir la plage” (là plusieurs options pour redéfinir l’import – Chaque import à un nom – par défaut import1, import2 etc.) On peut actualiser ces résultats de requêtes dans calc via “Données > actualiser la plage” .
    Je précise que j’ai testé et cela fonctionne (j’ai modifié ma requête pour voir si l’actualisation se faisait dans la plage et c’était ok).

    Bonne journée.


Comments are closed.