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