-3

Is there any way to import CSV file to oracle table using JAVA?

Filburt
  • 17,626
  • 12
  • 64
  • 115
Jigar Prajapati
  • 93
  • 1
  • 10
  • Have a look at [this](https://www.mkyong.com/java/how-to-read-and-parse-csv-file-in-java/) – Jacob May 17 '17 at 10:46
  • 2
    Use SQL\*Loader or external tables. Why write your own import routines when Oracle provides them for you? – APC May 17 '17 at 10:48
  • after sql*loader installation..can i import csv using java with controller file code? – Jigar Prajapati May 17 '17 at 10:50
  • sql*loader is a program, java is a language – thatjeffsmith May 17 '17 at 11:33
  • @JigarPrajapati In order to read data from CSV file, you do not need to have SQLLOADER, it is being used to load data from Oracle database – Jacob May 17 '17 at 11:33
  • sql developer is a java application that can easily load csv to a new or existing table, but there's no code involved from the user perspective, just a GUI Wizard – thatjeffsmith May 17 '17 at 11:35
  • I believe you can do this. [Using the code from this question as a starting point](http://stackoverflow.com/questions/3774432/starting-a-process-in-java), launch an OS process to run **SQL*Loader** which will then load the data into your database. Best of luck. – Bob Jarvis - Слава Україні May 17 '17 at 11:56

1 Answers1

0

Sample code:

import java.io.*;
import com.opencsv.*;
import java.util.*;
import java.sql.*; 
public class loadcsvinoracle {  
        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/db12c", "hr", "hr");
                PreparedStatement sql_statement = null;
                String jdbc_insert_sql = "INSERT INTO CSVFILE"
                                + "(COL1, COL2, COL3) VALUES"
                                + "(?,?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                /* Read CSV file in OpenCSV */
                String inputCSVFile = "csvfile.csv";
                CSVReader reader = new CSVReader(new FileReader(inputCSVFile));         
                String [] nextLine; 
                int lnNum = 0; 
                //loop file , add records to batch
                while ((nextLine = reader.readNext()) != null) {
                        lnNum++;
                        /* Bind CSV file input to table columns */
                        sql_statement.setString(1, nextLine[0]);
                        sql_statement.setString(2, nextLine[1]);
                        sql_statement.setString(3, nextLine[2]);
                        // Add the record to batch
                        sql_statement.addBatch();
                        System.out.println ("New line " + nextLine[0] + nextLine[1] + nextLine[2]);
                }                       
                //We are now ready to perform a bulk batch insert              
                int[] totalRecords = new int[7];
                try {
                        totalRecords = sql_statement.executeBatch();
                } catch(BatchUpdateException e) {
                        //you should handle exception for failed records here
                        totalRecords = e.getUpdateCounts();
                }
                System.out.println ("Total records inserted in bulk from CSV file " + totalRecords.length);                
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

You will need to download and add opencsv to classpath.

Md Omar Faruque
  • 1
  • 3