sql

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.

TIP:
For other databases too, such utility are available. For example, the Oracle provides the SQLLOADER utility for loading data directly from files into database tables

Summary :

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

Download
You can download the source code of this example here: ReadCSVFile.zip

Chandan Singh

Chandan holds a degree in Computer Engineering and is a passionate software programmer. He has good experience in Java/J2EE Web-Application development for Banking and E-Commerce Domains.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
lilly
lilly
5 years ago

Hi Chandan,Thanks for the code.I added the library open-cv .
but it says CSVReader is deprecated,and so file is not read.
i tried CSVReaderBuilder ,also CSVParser going thru from stackoverflow posts,but no help.
any thoughts??..

Divya Gupta
Divya Gupta
5 years ago

Hi Anand,
I am writing the same code as yours but in my eclipse one error is showing up:
DBConnection cannot be resolved.

Do you know the possible reason for this error.

saurav
saurav
5 years ago

Thanks bro…

Rama
Rama
4 years ago

The readCsvUsingLoad() is not working on windows 10 with mysql 8.0.

Eventhough the data files are in the the "uploads" directory as specified by the secure_file_priv parameter in the my.ini of the mysql server, its still throwing the error : “The mysql server is running with the parameter –secure_file_privoption so it cannot execute this statement.”

Back to top button