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:|
1. Create your MySQL table
To begin, create a MySQL table using a query like below.
Note that the MySQL table columns must be identical to the columns in the CSV file.
2. Add the appropriate jar files
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!