Java code example to import data from Excel to database

Importing data from Excel to database is a common task of a software program. So in this post, I will share with you a sample Java program that reads data from an Excel file and inserts that data to a MySQL database.

Suppose that we have an Excel file that stores information about students enrolled in courses like this:

As you can see, this Excel file has only one sheet and the student information is stored in 3 columns: Student Name (text), Enrolled (date time) and Progress (number). So let’s create a corresponding table in the database with the following structure:

You can run the following MySQL script to create this table:

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `enrolled` timestamp NOT NULL,
  `progress` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

Note that the data type of the enrolled column is timestamp so it can hold both date and time values.

To read the Excel file from Java, we can use the Apache POI library. Suppose that your project uses Maven, so add the following dependency to the pom.xml file:

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.0</version>
</dependency>

And to insert data to MySQL database from Java, we need to use a dependency for MySQL JDBC driver:

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.46</version>
	<scope>runtime</scope>
</dependency>

And below is the full code of the sample program that reads data from the Excel file and insert that data to MySQL database:

package net.codejava;

import java.io.*;
import java.sql.*;
import java.util.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

/**
 * Sample Java program that imports data from an Excel file to MySQL database.
 * 
 * @author Nam Ha Minh - https://www.codejava.net
 * 
 */
public class Excel2DatabaseTest {

	public static void main(String[] args) {
		String jdbcURL = "jdbc:mysql://localhost:3306/sales";
		String username = "user";
		String password = "password";

		String excelFilePath = "Students.xlsx";

		int batchSize = 20;

		Connection connection = null;

		try {
			long start = System.currentTimeMillis();
			
			FileInputStream inputStream = new FileInputStream(excelFilePath);

			Workbook workbook = new XSSFWorkbook(inputStream);

			Sheet firstSheet = workbook.getSheetAt(0);
			Iterator<Row> rowIterator = firstSheet.iterator();

            connection = DriverManager.getConnection(jdbcURL, username, password);
            connection.setAutoCommit(false);
 
            String sql = "INSERT INTO students (name, enrolled, progress) VALUES (?, ?, ?)";
            PreparedStatement statement = connection.prepareStatement(sql);		
			
            int count = 0;
            
            rowIterator.next(); // skip the header row
            
			while (rowIterator.hasNext()) {
				Row nextRow = rowIterator.next();
				Iterator<Cell> cellIterator = nextRow.cellIterator();

				while (cellIterator.hasNext()) {
					Cell nextCell = cellIterator.next();

					int columnIndex = nextCell.getColumnIndex();

					switch (columnIndex) {
					case 0:
						String name = nextCell.getStringCellValue();
						statement.setString(1, name);
						break;
					case 1:
						Date enrollDate = nextCell.getDateCellValue();
						statement.setTimestamp(2, new Timestamp(enrollDate.getTime()));
					case 2:
						int progress = (int) nextCell.getNumericCellValue();
						statement.setInt(3, progress);
					}

				}
				
                statement.addBatch();
                
                if (++count % batchSize == 0) {
                    statement.executeBatch();
                }				

			}

			workbook.close();
			
            // execute the remaining queries
            statement.executeBatch();
 
            connection.commit();
            connection.close();	
            
            long end = System.currentTimeMillis();
            System.out.printf("Import done in %d ms\n", (end - start));
            
		} catch (IOException ex1) {
			System.out.println("Error reading file");
			ex1.printStackTrace();
		} catch (SQLException ex2) {
			System.out.println("Database error");
			ex2.printStackTrace();
		}

	}
}

As you can see, we use JDBC batch update feature to efficiently insert a large number of rows into the database, with batch size of 20 – which means it will group maximum 20 SQL statements in each batch sent to the server for processing.

We also use JDBC transaction to make sure that either all rows inserted or none. Finally, we measure the time it takes to complete the reading and inserting of data.

Run this program and you would see the output somehow like this:

Import done in 3462 ms

To verify, run SQL Select statement in MySQL Workbench and you would see:

That’s how to write Java code that imports data from an Excel file and insert into database. For your reference, I attached the sample project in the Attachments section below. You can also get the sample code on GitHub here.

Related Tutorials:

• How to read Excel file in Java
• How to execute batch update/insert with JDBC

Other Java Coding Tutorials:

• Java code example to insert data from CSV to database
• How to display images from database in JSP page with Java Servlet
• How to implement remember password (remember me) for Java web application
• How to code login and logout with Java Servlet, JSP and MySQL
• How to Code Hit Counter for Java web application
• 10 Common Mistakes Every Beginner Java Programmer Makes
• 10 Java Core Best Practices Every Java Programmer Should Know
• How to become a good programmer? 13 tasks you should practice now
• How to calculate MD5 and SHA hash values in Java
• Java File Encryption and Decryption Example

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:

Excel2Database.zip

[Java Import Excel to Database Example]

22 kB