JSP/Servlets: How do I Upload a zip file, unzip it and extract the CSV file

Question

Wondering how can I do the following in JSP/Servlets:

1. Upload a zip file (containing multiple CSV files)

2. Unzip the file to obtian the CSV files

3. Read the CSV files and pump the records into a mySQL database

Note: mySQL table is set up and ready for CSV files inputs.

Thanks in advance.

Solution

1: Upload a zip file (containing multiple CSV files)

Use a multipart/form-data form with input type="file" in HTML/JSP to be able to select a file and upload it. Use Apache Commons FileUpload in the Servlet to be able to parse the request body and obtain the uploaded files. See also: How to upload files in JSP/Servlet?

2: Unzip the file to obtian the CSV files

Use java.util.ZipInputStream to read a zip file and extract the zip entries. See also: Compressing and Decompressing files in Java.

3: Read the CSV files and pump the records into a mySQL database

Two ways:

1. Put the CSV somewhere on the local disk file system where the MySQL has access to and instruct it to import it using a LOAD DATA INFILE query.

2. Use an existing CSV parser or create one to parse a CSV into a useable collection of Java objects, e.g. List<List<String>>. Then learn JDBC and use PreparedStatement to create, populate and execute an INSERT query in batches. See also this mini tutorial on MySQL and JDBC.