Converting data from excel to MySQL using Groovy

September 20th, 2014

There are various way to convert data from Excel to MySQL, for example, using “Export Data to MySQL” tool in Microsoft Excel. In this article, I will introduce a simple programmaticall approach that using groovy to incorporate JDBC and jexcelapi libraries to extract data from Excel then insert it into MySQL. Because JDBC can work not only with MySQL, but also many Relational Database such that Sqlite, SQL Server, PgSQL, Oracle,… so this program can apply to these Databases.

You can download example source code at here.

Preparation for example

Sample Excel data file

The example presented here uses a sample bookstore data file. Although it was edited in Libre Calc (a Linux based spreadsheet software similar to Microsoft Excel), but it should be saved in Microsoft Excel XP/2003 format (with .xls extension in file name).

Figure 2. Sample excel data file

Create a new database

Let’s create a new database for storing data and an user account to create a table and insert data into the table. To create a new user, you must be logged in under a database administrator account, in MySQL, the default administrator account is root. You can use mysql command line client or phpmyadmin web GUI tool to create the user account and database. The created database has following connection parameters:

Username: bookstore
Password: n0passw0rd
Database name: bookstore (the same value as username)

In situation you use phpmyadmin, let’s log in with root account and click the Add a new user link near the bottom of the Privileges page. Enter username, password into textboxes, check “Create database with same name and grant all privileges” option and click Go button to finish.

Figure 1. Create username and database 'bookstore'

For mysql command line client, you must log in with root account, and execute the follows:

CREATE USER 'bookstore'@'localhost' IDENTIFIED BY 'n0passw0rd';

GRANT USAGE ON *.* TO 'bookstore'@'localhost' IDENTIFIED BY 'n0passw0rd' 
	WITH 
		MAX_QUERIES_PER_HOUR 0 
		MAX_CONNECTIONS_PER_HOUR 0 
		MAX_UPDATES_PER_HOUR 0 
		MAX_USER_CONNECTIONS 0;

CREATE DATABASE IF NOT EXISTS `bookstore`;

GRANT ALL PRIVILEGES ON  `bookstore` . * TO  'bookstore'@'localhost';

Write the program

This example has a short “flat” program (without function and object definitions). It consist of 5 sections:

Dependencies declarations

@Grapes([
    @GrabConfig(systemClassLoader=true),
    @Grab(group='net.sourceforge.jexcelapi', module='jxl', version='2.6.12'),
    @Grab(group='mysql', module='mysql-connector-java', version='5.1.13')
])

import groovy.sql.Sql
import java.util.UUID
import jxl.*
import jxl.write.*

def logEnabled = true;

Database connection initialization

def db_driverClassName = "com.mysql.jdbc.Driver"
def db_url = "jdbc:mysql://127.0.0.1:3306/bookstore?characterEncoding=utf-8"
def db_username = "bookstore"
def db_password = "n0passw0rd"

def sql = Sql.newInstance(db_url, db_username, db_password, db_driverClassName)

sql.execute("DROP TABLE IF EXISTS `books`;");
sql.execute("CREATE TABLE `books` (" +
    "`f_id` VARCHAR( 36 ) NOT NULL ," +
    "`f_title` VARCHAR( 255 ) NOT NULL ," +
    "`f_isbn` VARCHAR( 16 ) NULL ," +
    "`f_author` VARCHAR( 128 ) NULL ," +
    "`f_publisher` VARCHAR( 64 ) NULL ," +
    "`f_pages` INT NULL ," +
    "`f_price` VARCHAR( 10 ) NULL ," +
    "`f_date` VARCHAR( 10 ) NULL ," +
    "PRIMARY KEY (  `f_id` )" +
    ") ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;");

Workbook initialization

// CHANGE PATH TO YOUR EXCEL DATA FILE
def excelFile = "/absolute/path/to/your/excel/data/dir/bookstore.xls";

def maxRows = 156;
def maxCols = 10;

def startRow = 3;
def startCol = 0;

def wb = Workbook.getWorkbook(new File(excelFile));
def sheet = wb.getSheet(0);

Transfering Data

for(int j=0; j<maxRows; j++) {
    logEnabled ? print("=@${j + 1}|") : null
    
    def RV = [];
    for(int l=0; l<maxCols; l++) {
        RV.add(sheet.getCell(startCol + l, startRow + j).getContents());
        logEnabled ? print("${RV[startCol + l]}|") : null
    }
    
    sqlString = "INSERT INTO books " + 
        "(f_id, f_title, f_isbn, f_author, f_publisher, f_pages, f_price, f_date)" + 
        " VALUES ('${UUID.randomUUID().toString()}', '${RV[1]}', '${RV[6]}', " +
            "'${RV[2]}', '${RV[5]}', ${RV[3]?RV[3]:null}, '${RV[7]}', '${RV[8]}')";
    
    sql.execute(sqlString);
    
    logEnabled ? println() : null;
}

Don’t forget to close connections

logEnabled ? println("=@ Closing workbook") : null
wb.close();

logEnabled ? println("=@ Closing database") : null
sql.close();

Executing your program

There are two options to execute your program: Groovy console or Groovy command line. To execute your program with Groovy console, you should run groovyConsole program, open file migrateDataFromExcelToMySQL.groovy and click to “Execute Groovy Script” button.

Figure 3. Execute program with Groovy Console

Result of running program

After program executed successfully, you can use phpmyadmin to browse the transferred data:

Figure 4. Result of running program

pnhung177

Keep moving forward