Processing CSV files from the Web using embedded Java database

Question

Short version: assuming I don't want to keep the data for long, how do I create a database programmaticly in HSQLDB and load some CSV data into it to? My schema will match the files exactly and the files do have adequate column names.

This is an unattended process.

Details:

I need to apply some simple SQL techniques to three CSV files downloaded via the web, then create some DTOs which I can then use with some existing code to process them some more, and save them via REST. I don't really want to mess around with databases but the CSV files are linked by foreign keys, so I was thinking of using an in-memory embedded database to do the work, then throw the whole lot away.

I had in mind a command line app working like this:

1. Create a fresh database in HSQLDB.
2. Start three HTTP GETs in three threads using Apache HttpClient.
3. Import the CSVs into three HSQLDB MEMORY tables.
4. Run some SQL.
5. Parse the results into my existing DTOs.
6. Etc...

I could use pointers to code and utilities helpful for items 1, and 3. Also is there an alternative to HSQLDB I should consider?

Answer 1

Check opencvs. It helps you to parse CSV files.