* How to Load CSV File to Database in Java

Sometimes in a Java application we may want to load a CSV file to the database. Generally databases have the built-in tool to do this. But their special interfaces make the routine hard to be called from Java.

Yet we would be able to do the load conveniently if we had esProc SPL.
Download esProc installation package
HERE.

Let’s see how to get this done with esProc SPL through an example. Task: Load the CSV file (sales.csv) storing sales data to MySQL database.

Below is part of the file data:

ORDERID

CLIENT

SELLERID

AMOUNT

ORDERDATE

1

LIHD

11

9893

2020/12/7

2

NYSD

1

8332

2020/12/7

3

RSDRG

10

4614

2020/12/7

4

LDCH

18

8807

2020/12/7

5

JXJDI

19

743

2020/12/7

The sales table in MySQL database has the following the structure:

Field name

Type

ORDERID

int

CLIENT

varchar(10)

SELLERID

smallint

AMOUNT

double

ORDERDATE

date

1. Start esProc, configure database connection and name it. The parameters should be consistent with those in the JDBC connection configuration.
Do the configurations in the following window (Tool->Datasource Connection->Datasource)

undefined

2. Write SPL script csv2db.dfx in esProc:

Below is the esProc SPL program for handling small amounts of data (The CSV file is relatively small and can be or suitable to be directly load into the memory wholly during the runtime environment):

A

B

1

=file("sales.csv").import@ct()

/ Import sales.csv

2

=connect("mysql")

/ Connect to database

3

>A2.update@i(A1,sales,ORDERID,CLIENT,SELLERID,AMOUNT,ORDERDATE)

/ db.update function does   the data loading from the CSV file to the database

4

>A2.close()

/Disconnect from database

If we already know that data in the CSV file is all new, we can use update@i to generate INSERT statement only. If we do the update, just use update. But this is much slower because the program needs to check if it needs to generate an UPDATE statement. Learn more about db.update() function.

3. Execute the esProc SPL script to load data into the database. Below is the database table sales that load part of data from the CSV file:

undefined

4. Integrate the SPL script into our Java program:

esProc provides JDBC driver to integrate the script in the following way:

public static void testDataServer() {

Connection con = null;

java.sql.PreparedStatement st;

try {

Class.forName("com.esproc.jdbc.InternalDriver");

con = DriverManager.getConnection("jdbc:esproc:local://");

// Call script csv2db.dfx

st = con.prepareCall("call csv2db()");

st.execute();

 

System.out.println("finish");

} catch (Exception e) {

System.out.println(e);

} finally {

// Close database connection

if (con != null) {

try {

con.close();

} catch (Exception e) {

System.out.println(e);

}

}

}

}

Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.

If the CSV file contains a relatively large amount of data and unsuitable or impossible to be loaded into the memory during the runtime environment, we just need to change A1’s import function to cursor function to accomplish the load.