CODEWITHSUNDEEP
javamysqloracle-databasecsvsql-loader
Subho
Subho

Reputation: 923

How to a csv file in oracle using sql loader in java

I want to load data from a csv file to oracle database. Here is my code-

void importData(Connection conn) {
    Statement stmt;
    String query;
    String filename = "C:/CSVData/Student.csv";

    try {
        stmt = conn.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        query = "LOAD DATA INFILE '" + filename + "' INTO TABLE Student FIELDS terminated by ',' ;";
        System.out.println(query);
        stmt.executeQuery(query);

    } catch (Exception e) {
        e.printStackTrace();
        stmt = null;
    }
}

This code runs perfectly and load data in mysql. But now I want to load data in oracle. what change do i have to make in query. Please help me. Thank you in advance...

Upvotes: 0

Views: 6080

Answers (3)

VikiYang
VikiYang

Reputation: 233

First, you need to write a control file.

Control file example FYI:

Load data                   
infile "D:/Viki/test.CSV"                                       --the input file(s) you need to import
truncate                                                                        --the option you need do. (truncate, append, insert, replace. insert by default)
into table vk_recon_China_201409_i                  --table need insert to
fields terminated by ","                                        --
trailing nullcols
(
    col_a         filler
  , col_b          "Trim(:col_b)"
  , col_c          "To_Date(:col_c,'yyyy/mm/dd hh24:mi:ss')"
  , seqno                sequence(Max,1)
)

Then, call sqlldr command by Runtime.exec or ProcessImpl.start, 

public  void startUp() {
    StringBuffer sb = new StringBuffer();
    String path = "sqlldr user/password@sid readsize=10485760 bindsize=10485760 rows=1000 control=controlFileName.ctl log=controlFileName.log direct=true \n pause";   
    try {
        Process pro = Runtime.getRuntime().exec(path);
        BufferedReader br = new BufferedReader(new InputStreamReader(pro.getInputStream()), 4096);
        String line = null;
        int i = 0;
        while ((line = br.readLine()) != null) {
            if (0 != i)
                sb.append("\r\n");
            i++;
            sb.append(line);
        }
    } catch (Exception e) {
        sb.append(e.getMessage());
    }
}

Upvotes: 2

dReAmEr
dReAmEr

Reputation: 7196

I think below query should work.

query = "LOAD DATA INFILE '" + filename + "' APPEND INTO TABLE Student FIELDS terminated by ',' ;";

For more info:-

http://docs.oracle.com/cd/E11882_01/server.112/e16536/ldr_control_file.htm#SUTIL005

Upvotes: -1

deogratias
deogratias

Reputation: 480

Try making the external table.You can create an external table on your CSV file using ORACLE_LOADER driver and then update your existing table with data in your external table using DML (MERGE for example).

Upvotes: 0

Related Questions