Import CSV file to Oracle using JDBC (in 5 lines!)

Here is a simple 5 line program that imports CSV file to Oracle as a table using JDBC.
Here we will be using the concept of external tables in Oracle. We will just be executing that command from our JDBC program.

C:\java\sample.csv

sno,sname,age
101,"smith",20
102,"scott",25

csv3.java

import java.sql.*;
class csv3
{
    public static void main(String args[]) throws Exception
    {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        System.out.println("driver loaded");

        Connection c=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","scott","tiger");
        System.out.println("connection established");

        Statement s=c.createStatement();

        s.executeUpdate("create or replace directory my_dir as 'C:\\java\\'");

        System.out.println("dir created");

        s.executeUpdate("create table mytab(sno number(4),sname varchar2(40), age number(4)) organization external(type ORACLE_LOADER default directory my_dir access parameters(records delimited by newline fields terminated by \",\") location('sample.csv')) reject limit unlimited");
        System.out.println("table imported");

    }
}

Note: You will need Oracle Type 4 driver in your system classpath for this program to work. So make sure that you copy the path of required jar files (classes12.jar, ojdbc14.jar) to your classpath in system variables.
Those jars can be found in the installation directory of oracle. I'm not sure about the path, but just hit search for those files. You will find both jar files exist in the same directory. If you don't find those files, better download them here. For Oracle 11g you can search them or download ojdbc5.jar here (I think you can even download this for Oracle 10g instead of downloading those two files).

No comments: