| Excerpt | ||
|---|---|---|
| ||
CSV files can also be used as a datasource with the help of a HSQLDB database. |
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:
Use a simple csv file (named sample.csv and located in D:\hsql folder) with the following content.
| Code Block |
|---|
sep=,
1,name1,
2,name2,
3,my name, |
Configure the HSQLDB Data Source. For more details refer to
JNDI datasource configuration page.
| Code Block |
|---|
<Resource name="TestDB"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.hsqldb.jdbcDriver"
url="jdbc:hsqldb://D:/hsql/sample;create=true;"
username="sa"
password=""
/> |
Create a script inside D:\hsql folder, named sample.script for mapping the csv file to a hsqldb text table.
| Code Block |
|---|
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 |
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).
| Info |
|---|
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. |
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.
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
| Filter by label (Content by label) | ||||||
|---|---|---|---|---|---|---|
|
SQL Configuration Guides
...
| Include Page | ||||
|---|---|---|---|---|
|