Insert data from Excel to Oracle in Java

It is tutorial I would show you how you can easily load your data from an excel file to Oracle in Java.

Database: Oracle 10g
Excel: 97-2003 (.xls)
Oracle Driver: ojdbc14 from here or here
Poi jar: from here or here  (for this tutorial I use version 3.8)
IDE: netbeans 7.1.1

I suppose that you know how to create a java project and add libraries to a project .

1. Create a java Project and add ojdbc14 and poi jars to it.
  After adding jars under project properties you will have something like this under project lib folder.

2. Create a table called STUDENTS.

 

CREATE TABLE students
   ( id varchar2(5) PRIMARY KEY,
     stname varchar2(50),
     country varchar2(30),
     types varchar2 (2)
   )

  

3. Prepare your excel file  and make sure you have saved it as a 1997-2003 (.XLS) file

  

4. Here is your code

public static void main( String [] args ) throws ClassNotFoundException, InstantiationException, SQLException, IllegalAccessException {

    String fileName="C:\\book\\list.xls";
    Vector dataHolder=xlsReader(fileName);
    printCellDataToConsole(dataHolder);
   }
   public static Vector xlsReader(String fileName)
   {
   
     Vector cellVectorHolder = new Vector(); //Vector definition

    try{
   
    FileInputStream myInput = new FileInputStream(fileName); //stream creation
    POIFSFileSystem file = new POIFSFileSystem(myInput); //POIFSFileSystem Object
    HSSFWorkbook workBook = new HSSFWorkbook(file);
    HSSFSheet mySheet = workBook.getSheetAt(0);  //getting the first sheet from the WorkBook
    Iterator rowIter = mySheet.rowIterator(); //cells iterator

      while(rowIter.hasNext()){
          HSSFRow row = (HSSFRow) rowIter.next();
          Iterator cellIter = row.cellIterator();
          Vector cellStoreVector=new Vector();
          while(cellIter.hasNext()){
              HSSFCell cell = (HSSFCell) cellIter.next();
              cellStoreVector.addElement(cell);
          }
          cellVectorHolder.addElement(cellStoreVector);
      }
      
    }catch (Exception e){e.printStackTrace(); }
    return cellVectorHolder;
  }

  private static void printCellDataToConsole(Vector dataHolder) throws ClassNotFoundException, InstantiationException, SQLException, IllegalAccessException 
  {
     
    String id="";
    String name="";
    String country="";
    String type="";
    
    for (int i=0;i
 

5.Finally run your project, Yep ! You  made it, Congratulations !!!
 If you face any problem along then don't hesitate to drop a comment.