Execute SQL on CSV files via JDBC
- by Markos Fragkakis
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:
CsvJdbc
XlSQL
JBoss Teiid
Create an Apache Derby DB, load all CSVs as tables and execute the query.
These are the problems I encountered:
it does not accept the syntax of the SQL (it uses internal selects and table aliases). Furthermore, it has not been maintained since 2004.
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.
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)?
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
