Write a CSV File to Database (2)

Updated: Feb 21, 2022

View on GitHub

This example shows you how to write the contents of a CSV file to a database table in Java using the CSVReader and JdbcWriter classes.

This example can be easily modified to show how to read from a database. Also refer write a CSV to database (1) which shows a different way of writing a CSV file to a database.

Input CSV file

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating
101,Reeves,Keanu,9315.45,10000.00,1/17/1998,A
312,Butler,Gerard,90.00,1000.00,8/6/2003,B
868,Hewitt,Jennifer Love,0,17000.00,5/25/1985,B
761,Pinkett-Smith,Jada,49654.87,100000.00,12/5/2006,A
317,Murray,Bill,789.65,5000.00,2/5/2007,C

Java Code listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;
import java.sql.Connection;
import java.sql.Driver;
import java.util.Properties;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.core.Record;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.jdbc.JdbcWriter;

public class WriteACsvFileToDatabase2 {
    
    public static void main(String[] args) throws Throwable {
        // connect to the database
        Driver driver = (Driver) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
        Properties properties = new Properties();
        properties.put("user", "scott");
        properties.put("password", "tiger");
        Connection connection = driver.connect("jdbc:odbc:dp-cookbook", properties);

        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-01.csv"))
            .setFieldNamesInFirstRow(true);
        
        DataWriter writer = new  JdbcWriter(connection, "dp_credit_balance")
            .setAutoCloseConnection(true)
            .setBatchSize(100);

        reader.open();
        writer.open();
        try {
            Record record;
            while ((record = reader.read()) != null) {
                writer.write(record);
            }
        } finally {
            reader.close();
            writer.close();
        }
    }

}

Code Walkthrough

1. A JDBC Driver instance and a Connection object is obtained in order to connect to the database, this is standard JDBC code.
2. A CSVReader is created corresponding to the input CSV file credit-balance-01.csv.
3. A JdbcWriter is created using the Connection object and the output database table name dp_credit_balance.
4. The reader.open and writer.open methods are invoked to open the reader and writer respectively.
5. A while loop iterates through the data in the CSV.
6. Each record in the CSV is read as a Record object via the reader.read method and written to the database via the writer.write method.
7. After the while loop completes, the reader and writer are closed via reader.close and writer.close respectively in a finally block.

CSVReader

CSVReader is an input reader which can be used to read CSV files. It is a sub-class of TextReader and inherits the open and close among other methods. The CSVReader.setFieldNamesInFirstRow(true) method causes the CSVReader to use the names specified in the first row of the input data as field names. If this method is not invoked, the fields would be named as A1, A2, etc. similar to MS Excel. If those fields names need to be changed, a rename transformation can be added on top of CSVReader or any other type (Refer Rename a field for example).

JdbcWriter

A JdbcWriter is an output writer used to write data to a database. It is created using a Connection object and a database table name. It is a sub-class of DataWriter and overloads the open, close and write among other methods for JDBC access. An important method is the setAutoCloseConnection which when invoked with a true value instructs the JdbcWriter to close the Connection when its close method is invoked.

Database Output

The output of this program would be records inserted into the dp_credit_balance database table.