Update Database Table According to an External CSV File

In the following database table sales that stores sales records, ORDERID is the primary key:

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

sales.csv stores sales data, as shown below:

ORDERID

CLIENT

SELLERID

AMOUNT

ORDERDATE

3

RSDRG

10

9999

2020/12/7

6

NYSD

1

8332

2020/12/7

7

RSDRG

10

4614

2020/12/7

8

LDCH

18

8807

2020/12/7

9

JXJDI

19

743

2020/12/7

We want to update the database table using data in sales.csv. The expected sales table after being updated is as follows:

ORDERID

CLIENT

SELLERID

AMOUNT

ORDERDATE

1

LIHD

11

9893

2020/12/7

2

NYSD

1

8332

2020/12/7

3

RSDRG

10

9999

2020/12/7

4

LDCH

18

8807

2020/12/7

5

JXJDI

19

743

2020/12/7

6

NYSD

1

8332

2020/12/7

7

RSDRG

10

4614

2020/12/7

8

LDCH

18

8807

2020/12/7

9

JXJDI

19

743

2020/12/7

It’s convenient to get this done with esProc SPL.
Download esProc installation package
HERE.

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 program for handling small amounts of data (The CSV file is relatively small and can be or suitable to be directly loaded 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(A1,sales,ORDERID,CLIENT,SELLERID,AMOUNT,ORDERDATE)

/ db.update function loads   CSV data to the database table sales

4

>A2.close()

/ Disconnect from database

If we already know that data in the CSV file is all new, we can use @i option with update()function (update@i) to generate INSERT statement only. If we do the update only, just use update() function. 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 update the database table. Below is sales table after the update is finished:

undefined

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.

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