* 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)
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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL