Frequently asked questions - Base

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Documentation Home
• Official Publications
• Other Resources
• FAQ
• HowTo
• Macros
• Docs Development

• FAQ Home
• General
• Writer
• Calc
• Impress
• Draw
• Math
• Base

How to link (attach) text format tables in a HSQL database ?

There is a text file (.Txt, .Csv) that you want to link to a LibreOffice database (HSQL default format). This file can be modified outside of LibreOffice. This may be for example a file generated by an application. We want, not import it into a database (which produce a non-dynamic result), but link, attach it to be able to reflect the changes.

The principle is to create a table in text format defining the structure, then link to the source file. The interface does not allow to execute "graphically" this type of feature. We have to use the Tools ▸ SQL menu. Apart from this, the table will be used as another table. It may particularly be used in joins with standard HSQL tables.

In this example we create a table with an ID field (INTEGER) auto-value, a text field (VARCHAR) , a date field (DATE) and a numeric field (NUMERIC) .

The corresponding set of data (test.csv) will be:

1;Product one;2008-07-01;23.10
2;Product two;2008-07-02;15.50
3;Product three;2008-07-03;11
4;Product four;2008-07-04;13.50

How to create the table

• Open the HSQL database
• Execute Tools ▸ SQL
• Type the following SQL in the Command to execute zone then click Execute

-- Only with Tools> SQL
CREATE TEXT TABLE  "NomTableLiaison" ("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY, "NomProduit" VARCHAR(50), "DateDebut" Date, "Nombre" NUMERIC)
SET TABLE "NomTableLiaison" SOURCE "test.csv;fs=\semi"

Note

• The "test.csv" file is stored in the same folder as the database.
• The list of tables is not updated automatically, run the command View ▸ Refresh tables (only enabled when the "Tables" objects are displayed) .

If you want to change something when the database is opened it is possible to disconnect the table from the source file using the command:

-- Only with Tools> SQL
SET TABLE "NomTableLiaison" SOURCE OFF

Reconnection will occur with the command:

-- Only with Tools> SQL
SET TABLE "NomTableLiaison" SOURCE ON

• Download a database example (create the test.csv file in the same folder as the database with the above sample dataset).