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

Question

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...

What if you want to load it into postgresql next week? Or derby? Or h2? Or... Please us standard SQL for trivial things like this. — nablex
– nablex, Commented Sep 9, 2014 at 7:12

VikiYang · Accepted Answer · 2014-09-11 09:36:12Z

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());
    }
}

Community · Accepted Answer · 2017-04-13 12:42:36Z

0

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).

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Sep 9, 2014 at 6:37

deogratias

4802 gold badges8 silver badges23 bronze badges

Comments

dReAmEr · Accepted Answer · 2014-09-09 06:37:40Z

-1

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

answered Sep 9, 2014 at 6:37

dReAmEr

7,2047 gold badges40 silver badges67 bronze badges

3 Comments

dReAmEr Over a year ago

Which oracle version you are using?

Subho Over a year ago

i am using oracle 10g

Ben Over a year ago

This is MySQL; not Oracle

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related