Export Table Data From Libre Office Base

In this tutorial we are going to export the contents of the table below to a text file in csv (comma seperated values) format.

LibreOffice Base is the free database application that is packaged with LibreOffice, I use it all the time and in my opinion is the only option for quick and dirty database analysis on the Apple Mac platform (which i use).

LibreOffice base is also available on Mac, Pc and Linux.

Yes you can use Filemaker but that is more of a rapid application development tool than a database for crunching data.

Check out the detailed video tutorial below and then read through the summary for your reference.

So we are going to export the content of a table called "tblKeywords" pictured below.

Exporting the contents of the above table could then become part of an automated process to regularly share data with other applications.

First create a "text" table in LibreOffice Base using SQL

A text table is a special type of table that has similar characteristics to a normal table other than it’s purpose is to link to an external text file.

This text "table" can then be linked against existing files, or can in fact create files, these files will have their contents modified when you change the contents of the text table using sql for example.

In order to export the table pictured above to the file system we will first create a corresponding "text" table with the create table query below.

Note that LibreOffice Base uses the HSQLDB database engine, the default state of it’s sql engine is to assume that all object names (fields, tables etc) are uppercase, items that are not uppercase need to be enclosed in quotes.

Then Link Text Table To Output CSV File.

We then need to "link" this file to a text file on your file system, this file if it does not already exist will be created in the "database" folder of your base install if you are using the recommended "split database" format.

The sql above links the table "tblExportKeywords" to a text file called "tblKeywords.csv" with a file encoding of "UTF8"

If "tblKeywords.csv" does not already exist it will be created when the above query is run.

Finally Export Your Data To The Text File

All that remains is to export the base table data to your text file, this is accomplished by running an insert query from your regular table to the text table (in this instance tblExportKeywords).

LibreOffice Base will look after the details of writing this to the text file as a result of executing this process.

Your exported text file (highlighted) has now been created in the database folder of your base split database, you can use a base macro or python code to move it elsewhere as part of a larger application, or just manually move the file yourself for import into another application.

Don’t forget to comment below and let me know if this was helpful.

Also feel free to tell me what topics you would like covered in the future.