Read multiple csv file with CsvJdbc

Question

I need to bind a group of csv file in the format "YYYY-MM-DD hh:mm:ss.csv" that are present in the same folder with a unique table that contains all the data present in all the files.

I need to read the data from a Java EE application thus I would like to create a connection pool inside the application server. I found the CsvJdbc driver that allows the reading of multiple files as a single entity. A good starting point was this page in the section with this paragraph:

To read several files (for example, daily log files) as a single table, set the database connection property indexedFiles. The following example demonstrates how to do this.

The example could be fine for me but the problem is that I do not have a header word in the filename string. So the corresponding table becames an empty string that makes obviously impossible to query the table.

How can I tell the driver to map the pattern to a table that hasn't a header part?

P.S. I already tried to use hsqldb as a frontend to the csv files but it does not support multiple files.

score 0 · Answer 1 · answered Jan 03 '16 at 01:37

Setup CsvJdbc to read several files as described in http://csvjdbc.sourceforge.net/doc.html and then use an empty table name in the SQL query because your CSV filenames do not have any header before the fileTailPattern regular expression. For example:

    props.put("fileTailPattern", "(\\d+)-(\\d+)-(\\d+) (\\d+):(\\d+):(\\d+)"); 
    props.put("fileTailParts", "Year,Month,Day,Hour,Minutes,Seconds");

    ...

    ResultSet results = stmt.executeQuery("SELECT * FROM \"\" AS T1");

Read multiple csv file with CsvJdbc

1 Answers1