Create a Java ResultSet using CSV file instead of a SQL statement

Question

I want to use a result set to hold my user data. Just one wrinkle - the data is in a CSV (comma delimited) format, not in a database.

The CSV file contains a header row at the top, then data rows. I would like to dynamically create a result set but not using an SQL query. The input would be the CSV file.

I am expecting (from my Java experience) that there would be methods like rs.newRow, rs.newColumn, rs.updateRow and so on, so I could use the structure of a result set without having any database query first.

Are there such methods on result sets? I read the docs and did not find any way. It would be highly useful to just build a result set from the CSV and then use it like I had done an SQL query. I find it difficult to use a java bean, array list of beans, and such - because I want to read in CSV files with different structures, the equivalent of SELECT * FROM in SQL.

I don't know what the names or numbers of columns will be in advance. So other SO questions like "Manually add data to a Java ResultSet" do not answer my needs. In that case, he already has an SQL created result set, and wants to add rows.

In my case, I want to create a result set from scratch, and add columns as the first row is read in. Now that I am thinking about it, I could create a query from the first row, using a statement like SELECT COL1, COL2, COL3 ... FROM DUMMY. Then use the INSERT ROW statement to read and insert the rest. (Later:) But on trying that, it fails because there is no real table associated.

It's hard to understand what you actually want. Could it be that a CSV file JDBC driver is what you're looking for? — fvu
– fvu, Commented Aug 9, 2018 at 16:41
Neville Kuyt - I want to use a result set to hold my user data. Just one wrinkle - the data is in a CSV (comma delimited) format, not in a database. - and apparently there is no easy way. Do you have an answer? - - - Fvu - yes, that sounds exactly what I am lookin g for. — Baruch Atta
– Baruch Atta, Commented Aug 10, 2018 at 16:58

Mureinik · Accepted Answer · 2018-08-09 16:43:09Z

5

The CsvJdbc open source project should do just that:

// Load the driver.
Class.forName("org.relique.jdbc.csv.CsvDriver");

// Create a connection using the directory containing the file(s)
Connection conn = DriverManager.getConnection("jdbc:relique:csv:/path/to/files");

// Create a Statement object to execute the query with.
Statement stmt = conn.createStatement();

// Query the table. The name of the table is the name of the file without ".csv"
ResultSet results = stmt.executeQuery("SELECT col1, col2 FROM myfile");

answered Aug 9, 2018 at 16:43

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

The Impaler · Accepted Answer · 2018-08-09 17:58:38Z

3

Just another idea to complement @Mureinik's one, that I find really good.

Alternatively, you could use any CSV Reader library (many of them out there), and load the file into an in-memory table using any in-memory database such as H2, HyperSQL, Derby, etc. These ones offer you a full/complete SQL engine, where you can run high end/complex queries.

It requires more work but you get a lot of flexibility to use the data afterwards.

After you try the in-memory solution, switching to a persistent database is really easy (just change the URL). This way you could load the CSV file only once into the database. On the second execution an on, the database would be ready to use; no need to load the CSV again.

answered Aug 9, 2018 at 17:58

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

1 Comment

Baruch Atta Over a year ago

Vlad: thanks for the answer. I could always load into an SQL database. But my application uses the data only once and then discards it. Overkill to load into a DB. No persistence needed.

Collectives™ on Stack Overflow

Create a Java ResultSet using CSV file instead of a SQL statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related