Retrieving Data From CSV Files With HSQLDB

To retrieve data from CSV (comma-separated values) files using the text tables, feature of HSQLDB, configure datasources like this:

1. Use a simple csv file (named sample.csv and located in D:\hsql folder) with the following content.

sep=,
1,name1,
2,name2,
3,my name,

2. Configure the HSQLDB Data Source. For more details refer to JNDI datasource configuration page.

<Resource name="TestDB"
          auth="Container" 
          type="javax.sql.DataSource"
          driverClassName="org.hsqldb.jdbcDriver"
          url="jdbc:hsqldb://D:/hsql/sample;create=true;"
          username="sa"
          password=""
/>

3. Create a script inside D:\hsql folder, named sample.script for mapping the csv file to a hsqldb text table.

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE TEXT TABLE PUBLIC.SAMPLE_TBL(ID BIGINT,NAME VARCHAR)
SET TABLE PUBLIC.SAMPLE_TBL SOURCE "sample.csv;ignore_first=true"
CREATE USER SA PASSWORD ""
GRANT DBA TO SA

4. The sample script creates user and grants access for connecting to the hsql schema, it creates the corresponding hsql text table for the csv file and sets the csv file source for populating the table (in our example located in the same folder as the script).

Note the ignore_first=true option in the SET TABLE SOURCE statement from the script. It tells HSQLDB to ignore the first line in a file.

This option is used when the first line of the file contains column headings, as it is in our case. If you remove the column headings from the csv file, you also have to remove this option from the script.

5. Configure your database information field with a query like SELECT id, name from PUBLIC.SAMPLE_TBL (or any other table name that you want to set in the sample.script). Note that you can also use dynamic queries.

6. Use your database custom fields with values populated from the csv files.

Other Methods of Loading CSV Data

Modern databases have the ability of mounting a CSV file as an individual table, that can be queried afterwards. Please refer to your database documentation or ask your DBA.

CSV files data

Retrieving Data From CSV Files With HSQLDB

Other Methods of Loading CSV Data

See also