Insert CSV Data to Oracle - Java JDBC Example Program

CSV to Oracle - Introduction

In this tutorial, we will discuss how to insert CSV file data to a table in Oracle, by using Java and OpenCSV library. If you look for a non Java based solution, there are powerful options for this, but when you use Java you can do transformations to the input before inserting it to a table if required. Our approach would be to parse the input CSV file using openCSV in Java, and then use JDBC to insert one row at a time / bulk insert into Oracle. We will cover both these approaches in this tutorial, as we move on.

Input CSV Data and Oracle Table

To work with the example in this tutorial, we need a sample CSV file and Oracle table. Use the scripts below to create a table in Oracle and data to create a CSV file. You can use your own CSV file, but make sure you alter the table suitably.

CREATE TABLE CSV_2_ORACLE
(
USER_ID VARCHAR2(20),
USER_AGE NUMBER
)

A sample CSV file that you can use for this tutorial is provided below:

A,40
B,34
C,50
D,30

Save this file as inputdata.csv. Download openCSV and have the JAR file opencsv-2.3.jar in your classpath. We are ready to go.

CSV to Oracle - Java Program Example - Open CSV - JDBC

The full Java program that does a row by row processing / insert of CSV file data to Oracle is provided below. You should be using this if the data in your CSV file is very less. For large CSV files, you should consider doing a bulk processing of CSV data.

import java.io.*;
import au.com.bytecode.opencsv.CSVReader;
import java.util.*;
import java.sql.*; 
public class csvToOracle {  
        public static void main(String[] args) throws Exception{                
                /* Create Connection objects */
                Class.forName ("oracle.jdbc.OracleDriver"); 
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe", "hr", "hr");
                PreparedStatement sql_statement = null;
                /* Create the insert statement */
                String jdbc_insert_sql = "INSERT INTO CSV_2_ORACLE"
                                + "(USER_ID, USER_AGE) VALUES"
                                + "(?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                /* Read CSV file in OpenCSV */
                String inputCSVFile = "inputdata.csv";
                CSVReader reader = new CSVReader(new FileReader(inputCSVFile));
                /* Variables to loop through the CSV File */
                String [] nextLine; /* for every line in the file */            
                int lnNum = 0; /* line number */
                while ((nextLine = reader.readNext()) != null) {
                        lnNum++;
                        /* Bind CSV file input to table columns */
                        sql_statement.setString(1, nextLine[0]);
                        /* Bind Age as double */
                        /* Need to convert string to double here */
                        sql_statement.setDouble(2,Double.parseDouble(nextLine[1]));
                        /* execute the insert statement */
                        sql_statement.executeUpdate();
                }               
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

This program successfully inserted the CSV data into Oracle table, row by row. After insert, the output as seen from the oracle table is provided below:

Insert CSV Data to Oracle Table - Java Example Program - Open CSV - JDBC - Output

In the next tutorial, we will discuss how to perform bulk insert of CSV file data to Oracle using one of the popular JDBC bulk insert approaches.