Faq/Base/143

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 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

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 (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:

Reconnection will occur with the command:


 * [[Media:FR.FAQ Base 143 LierTableTexte.odb|Download a database example]] (create the test.csv file in the same folder as the database with the above sample dataset).