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▸ 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 ▸
- 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"
- The "test.csv" file is stored in the same folder as the database.
- The list of tables is not updated automatically, run the command ▸ (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).