Execute SQL on CSV files via JDBC

Question

Dear all,

I need to apply an SQL query to CSV files (comma-separated text files). My SQL is predefined from another tool, and is not eligible to change. It may contain embedded selects and table aliases in the FROM part.

For my task I have found two open-source (this is a project requirement) libraries that provide JDBC drivers:

1. CsvJdbc
2. XlSQL
3. JBoss Teiid
4. Create an Apache Derby DB, load all CSVs as tables and execute the query.

These are the problems I encountered:

1. it does not accept the syntax of the SQL (it uses internal selects and table aliases). Furthermore, it has not been maintained since 2004.
2. I could not get it to work, as it has as dependency a SAX Parser that causes exception when parsing other documents. Similarly, no change since 2004.
3. Have not checked if it supports the syntax, but seems like an overhead. It needs several entities defines (Virtual Databases, Bindings). From the mailing list they told me that last release supports runtime creation of required objects. Has anyone used it for such simple task (normally it can connect to several types of data, like CSV, XML or other DBS and create a virtual, unified one)?
4. Can this even be done easily?

From the 4 things I considered/tried, only 3 and 4 seem to me viable. Any advice on these, or any other way in which I can query my CSV files?

Cheers

Answer 1

If your SQL is predefined and cannot be changed your best option is to load your CSV into a database and run queries against it.

Apache Derby is a viable option, so are MySQL, which even has a CSV storage engine or PostgreSQL.

Does your SQL use any proprietary functions / extensions? If so, that may limit your choices.

Answer 2

I'd say embedded db. I'd suggest either Javadb (Derby built into the Java API) or H2 if you don't care about pulling the extra dependency.

Answer 3

I would load the data into HSQL (HypersonicSQL). Pure Java, correct SQL, well-proven. Pretty much anything else has a bigger footprint.

Answer 4

There is a Groovy script, gcsvsql that lets you treat csv files as database tables, including joins. With gcsvsql you can do things like:

gcsvsql "select * from people.csv where age > 40"

gcsvsql "select people.name,children.child from people.csv,children.csv where people.name=children.name"

gcsvsql "select avg(score) from people.csv where age < 40"

You can find this script, which is based on the h2 database engine, at Google code here:

http://code.google.com/p/gcsvsql/

Answer 5

If you are wanting to treat csv files as databases from within a Java program, you should look at the h2 database engine. It has really nice support for reading/writing CSV files and working with in-memory databases. It's a successor to hsql, faster and with added features. You can read about the csv support in the h2 tutorial.

Answer 6

hi, maybe a bit late, sorry for that.

I've been developing the csvjdbc for over a year now and since a few weeks I've got "administrator" rights on that project so I've been able to release the most recent version I had produced. it does all "we" need (we: me and my current my colleagues) need, and I'm adding things as bugs are filed.

have a look at it now and decide again. (the web documentation still needs reviewing, for better insight, check the test cases, which are very extensive).