Import CSV to MySQL Table

Import CSV to MySQL Table

This tutorial demonstrates how to import a CSV file directly into a MySQL database.

Below is a screenshot of the CSV file we would like to import to MySQL.

In spreadsheet: Comma separated:
excel comma

1. Create your MySQL table

To begin, create a MySQL table using a query like below.

query

Note that the MySQL table columns must be identical to the columns in the CSV file.

2. Add the appropriate jar files

Upload the latest versions of the necessary jar files and add these to your classpath. The files are the commons-fileupload.jar, the mysql-connector-java.jar, the opencsv.jar and the commons-io.jar.

3. Create your HTML form

Next you need to create an HTML form through which the CSV will be uploaded. Place this code in the body of your html form.

4. Upload and Save the file

Save the file temporarily to a specified location. In this example, I use a servlet called SaveCSV.java to save the file. Generate a servlet and copy the code below. Read comments in the code to understand what each line does. Remember to change the file path to your desired one.

5. Connect to Database

You need to ensure you have a connection to the database established. Add the class below to your src folder to connect to the database. Remember to change the value of the database, username and password.

6. Read from path to MySQL

Import the file from the temporal store to the MySQL database table. Copy the code below. Read the comments to understand what each line does. You can further delete the saved CSV file using Java’s delete() method.

Feel free to use code from this post. Happy coding!

Seda
Seda Kunda is a web designer and developer with a degree in Computer Science and a great passion for code. Besides code, she enjoys pepperoni pizza, watching the bachelor and sleeping in on Saturdays.
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

One thought on “Import CSV to MySQL Table

Comments are closed.