CODEWITHSUNDEEP
csvhsqldb
user149408
user149408

Reputation: 5881

Import CSV files into HSQLDB

I am trying to convert a set of CSV files into a HSQLDB database. My first attempt was to fire up DatabaseManagerSwing and execute the following code:

* *DSV_COL_SPLITTER = ;
\mq /home/michael/workspaces/rds-surveyor/lt/it/NAMES.DAT
commit;

Which gets rejected with the error message:

java.sql.SQLSyntaxErrorException: unexpected token: *

In order to get at least some response from HSQLDB, I tried removing the first line, but this gives just a slightly different error:

java.sql.SQLSyntaxErrorException: unexpected token:

I then came across sqltool, and after overcoming its various pitfalls (you need the sqltool JAR, as well as the hsqldb JAR of the same version in the same path or somewhere in your classpath) I ran the full code here. The first line got processed as expected, but the \mq command fails with a similar error:

SEVERE  Cause: SQLSyntaxErrorException: unknown token:

The file I am trying to import looks like this (first few lines shown):

CID;LID;NID;NAME;NCOMMENT
25;1;165;Europa;
25;1;167;Italia;
25;1;169;Abruzzo;
25;1;171;Chieti;
25;1;173;Passo Di Lanciano;
25;1;175;Valico Castiglione Messer Marino;
25;1;177;Valico Della Forchetta;

What's going wrong here?

Upvotes: 2

Views: 1987

Answers (2)

user149408
user149408

Reputation: 5881

I have now abandoned the import path for other reasons and am instead doing the CSV import in my application.

While working with the CREATE TABLE statements, which I built from the first row of the CSV file copied in, I got the same error message for my SQL code. Closer analysis of the SQL file with a hex editor revealed a byte-order marker (BOM) at the beginning of the pasted column name. After eliminating the BOM, the SQL code would run without any further nagging.

I remember that some of the files I am trying to import start with a BOM (which has given me quite a headache earlier) – therefore I assume the BOM was the "unknown token" HSQLDB was complaining about all the time. Since the BOM is a nonprinting character, it explains why no token was shown in the error message.

Lesson learned: An "invalid token" error with no character shown in the message is likely due to a BOM, control character or other nonprinting stuff in the offending input. A hex editor will reveal that.

Upvotes: 0

fredt
fredt

Reputation: 24352

The command you are trying to execute belongs to SqlTool, which is a separate command line client for HSQLDB and is in a separate jar in the zip package. The guide is here: http://hsqldb.org/doc/2.0/util-guide/sqltool-chapt.html

In DatabaseManagerSwing, you can use a different method of creating TEXT tables for CSV files. http://hsqldb.org/doc/2.0/guide/texttables-chapt.html

Upvotes: 1

Related Questions