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)

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:

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.