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,

1. 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=""
/>

1. 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

1. 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).

1. 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.

1. 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.

More Configuration Guides