Update Database Table According to an External CSV File

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

sales.csv stores sales data, as shown below:

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

It’s convenient to get this done with esProc.
Download esProc installation package and free license file 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)

2. Write 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):

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 script to update the database table. Below is sales table after the update is finished:

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.