Import CSV file to MySQL table Java Example

In this example,we shall see what are the different ways we can use to insert data in the MySQL Database from a CSV File.

A CSV File is a de-limiter separated file with a comma as a de-limiter. The programme that we shall write, can work for other types of de-limiters as well with minor modifications.

The data is read line-by-line by our program. The field separator is comma(',') while the record separator is the new line character(\n).

The data read, is not pushed on each record basis. Rather, we create a batch with some threshold number of records and then we push the records onto the database at once to save some network traffic.(i.e.acquire and release connection everytime.)

Instead of using the BufferedReader and other Java API directly, we use the open-csv library. The library reads the CSV File and provides us with a com.opencsv.CSVReader Object. The Reader.readNext() method in this class returns a String array for each row. This row can be iterated to extract field values and set them in the java.sql.PreparedStatement.

A sample program below extract data from a CSV File and inserts them in a table using the logic described above.

ImportCsv.java:

package com.javacodegeeks.examples;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import com.opencsv.CSVReader;

public class ImportCsv
{
	public static void main(String[] args)
	{
			readCsv();
			readCsvUsingLoad();
	}

	private static void readCsv()
	{

		try (CSVReader reader = new CSVReader(new FileReader("upload.csv"), ','); 
                     Connection connection = DBConnection.getConnection();)
		{
				String insertQuery = "Insert into txn_tbl (txn_id,txn_amount, card_number, terminal_id) values (null,?,?,?)";
				PreparedStatement pstmt = connection.prepareStatement(insertQuery);
				String[] rowData = null;
				int i = 0;
				while((rowData = reader.readNext()) != null)
				{
					for (String data : rowData)
					{
							pstmt.setString((i % 3) + 1, data);

							if (++i % 3 == 0)
									pstmt.addBatch();// add batch

							if (i % 30 == 0)// insert when the batch size is 10
									pstmt.executeBatch();
					}
				}
				System.out.println("Data Successfully Uploaded");
		}
		catch (Exception e)
		{
				e.printStackTrace();
		}

	}

	private static void readCsvUsingLoad()
	{
		try (Connection connection = DBConnection.getConnection())
		{

				String loadQuery = "LOAD DATA LOCAL INFILE '" + "C:\\upload.csv" + "' INTO TABLE txn_tbl FIELDS TERMINATED BY ','" + " LINES TERMINATED BY '\n' (txn_amount, card_number, terminal_id) ";
				System.out.println(loadQuery);
				Statement stmt = connection.createStatement();
				stmt.execute(loadQuery);
		}
		catch (Exception e)
		{
				e.printStackTrace();
		}
	}

}

Sample CSV File:

254.23,123456789,12345
2854.00,987654321,87924
8724.03,598767812,56568

Create txn_tbl SQL :

CREATE TABLE `txn_tbl` (
`txn_id`  int(11) NOT NULL AUTO_INCREMENT ,
`txn_amount`  double NOT NULL ,
`card_number`  bigint(20) NOT NULL ,
`terminal_id`  bigint(20) NULL DEFAULT NULL ,
PRIMARY KEY (`txn_id`)
)

The readCsvUsingLoad() method provides yet another and even cleaner way to insert records in a mysql table. The Load Data command in mysql accepts the CSV/DSV file and inserts the records into the table.

The Load data is command is quite flexible as in, it accepts any delimiter specified using the FIELDS TERMINATED BY clause and LINES TERMINATED BY to mark the line termination character.

We used the ARM Blocks to avoid some boilerplate code like closing connections and other resources.

Summary :

Here, we tried to understand the different ways to insert records from a DSV/CSV file to the MySql database.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.