How to upload, extract, store .csv data to server and transfer to mysql.

Question

In short: how to put a zipful of csv data into mysql?

Long: I have to create a functionality for an admin to upload bootstrap data for a java web application. The administrator should be able to upload a single zip file of csvs on the admin page. The code needs to unzip csvs and the data must be transferred saved in a mysql DB.

I know I can prepare sql scripts with 'load infile csv' to transfer the data from csv(saved in a specific directory in my web app) to mysql. For egs using: LOAD DATA INFILE "/home/mysql/data/my_table.csv" INTO TABLE my_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

I also think I can accept and execute .sql files I create containing above sql. I plan to use http://pastebin.com/f10584951 : modified version of the com.ibatis.common.jdbc.ScriptRunner class .

But I don't know the first step of how to unzip an uploaded zip and store the files(.CSVs) in a specific directory that my sql statements can access.

It would be great if you can tell me how to do the above with stripes, as it is the framework i have adopted, but normal servlet/jsp 2 etc will be helpful too.

Thanks a lot :)

Answer 1

You can use java.util.zip for unzipping the CSV file. See an example here.

For uploading the file I recommend Apache Commons FileUpload