[Tutorial] Using csv/text files as editable data source.
Posted: Mon Oct 05, 2009 11:30 am
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:
First create new odb file with name: names.odb.
Second, go to the menu Tools > SQL... and open SQL command window.
Insert into:
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:
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:
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:
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
ID,FirstName,Name,DOB
1,John,White,1960-01-23
2,Elisabeth,Smith,1972-11-10
3,Eric,Long,1987-04-07
Second, go to the menu Tools > SQL... and open SQL command window.
Insert into:
Code: Select all
CREATE TEXT TABLE "Names" ("ID" INTEGER PRIMARY KEY, "FirstName" VARCHAR(20), "Name" VARCHAR(20), "DOB" DATE);
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
SET TABLE "Names" SOURCE "names.csv"
In csv file we added fields name, now we need to ignore first row, use command:
Code: Select all
SET TABLE "Names" SOURCE "names.csv;ignore_first=true";
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
SET TABLE "Names" SOURCE "names.csv;ignore_first=true;encoding=UTF-8";