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

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

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

Postby r4zoli » 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:
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";
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

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

Postby Villeroy » Sat Oct 17, 2009 8:58 pm

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,...).
User avatar
Villeroy
Volunteer
 
Posts: 22277
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Villeroy » Sun Oct 18, 2009 2:04 am

Example database: viewtopic.php?f=75&p=108195#p108195
Refer to the first two files Table1.csv and CSV_Editor.odb
User avatar
Villeroy
Volunteer
 
Posts: 22277
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests