[Tutorial] Using csv/text files as editable data source.

[r4zoli]

HSQLDB database engine allow to connect existing csv/text as editable data source.

Details can be found in 6th chapter in HSQLDB documentation:

To use this feature you can not use wizards or table design mode, you must create table with SQL command window.

This type of connection can be created if you follow this steps:
1. Create new odb file, save this file into folder where your csv/text table sits.
2. Create database table structure, which mirror of your csv file. The fields in the Base table is in the same order as the csv/text file. See HSQLDB Guide for the syntax for this.
3. Connect text file to this table structure.

To demonstrate this feature use csv file names.csv:

Code: Select all   Expand viewCollapse view

ID,FirstName,Name,DOB
1,John,White,1960-01-23
2,Elisabeth,Smith,1972-11-10
3,Eric,Long,1987-04-07

First create new odb file with name: names.odb.
Second, go to the menu Tools > SQL... and open SQL command window.
Insert into:

Code: Select all   Expand viewCollapse view

CREATE TEXT TABLE "Names" ("ID" INTEGER PRIMARY KEY, "FirstName" VARCHAR(20), "Name" VARCHAR(20), "DOB" DATE);

Click on Execute button.
Result should say . . . Command successfully executed.

To see newly created table go to the menu: View>Refresh Tables .

In third step insert into Tools > SQL..., and execute command:

Code: Select all   Expand viewCollapse view

SET TABLE "Names" SOURCE "names.csv"

You get error message: 1: bad TEXT table source file - line number: 1, ….(more text)

In csv file we added fields name, now we need to ignore first row, use command:

Code: Select all   Expand viewCollapse view

SET TABLE "Names" SOURCE "names.csv;ignore_first=true";

If you open table now you can reach your data in csv file, and can add new one.

If your data contain accented characters, the encoding must be set, according to your local settings put your encoding instead of UTF-8:

Code: Select all   Expand viewCollapse view

SET TABLE "Names" SOURCE "names.csv;ignore_first=true;encoding=UTF-8";

[Villeroy]

Allow me to mention a few important restrictions:

• The text file has to be in the same directory as the database file.
• All dates need to be ISO dates 1999-12-31
• All decimal figures need the dot as decimal delimter.
• There is no option to treat subsequent delimiters as one as you can do in Calc's csv import.
• HSQLDB does not support text tables with fields of fixed width. Only character separated text tables are supported.

Workaround for the first restriction with US dates 12/31/1999
Import the date column as CHAR(10).
In a query you can can use

Code: Select all   Expand viewCollapse view

SELECT CAST(RIGHT("strDate",4) || '-' || LEFT("strDate",2) || '-' || SUBSTRING("strDate",4,2) AS "DATE") AS "Date" FROM "SomeTable"

The concatenated strings turn '12/31/1999' into '1999-12-31'.
CAST('1999-12-31' AS "DATE") converts the string to a date value which will be displayed as a column named "Date" then.
Like any other calculated value this date won't be editable, but you can still edit the US-date string.
The converted date can be formatted by the OOo number formatter, the date values can be sorted in the right chronological order and the usual date functions do apply (DateDiff, Year, Quarter, Month,...).

[Villeroy]

Example database: viewtopic.php?f=75&p=108195#p108195
Refer to the first two files Table1.csv and CSV_Editor.odb