Loading Excel sheet data to Oracle table

1. Open Excel Spreadsheet in MS Excel. For example : products.xls.
2. Choose File->Save As and select CSV (Comma delimited) in Save As Type dropdown and enter filename where you want output to go. Example: products.txt.
3. Connect to Oracle
4. Create table using CREATE TABLE command. The structure of the table must match the formation in Excel sheet.
5. Download the following Java program.
6. Make necessary changes to the program. The table name in INSERT command and parameters are to be changed according to your requirement.
7. Compile Java program
8. Run java program given below by passing filename where comma delimited data is existing.
9. Go to SQL*Plus and select data from table.

Java Program To Convert CSF foramt to Oracle Database

import java.sql.*;
import java.io.*;
import java.util.*;

public class  ConvertFromCSF
{
    Connection con;
    PreparedStatement ps;
    public static void main(String args[])  throws Exception
    {
       String filename = args[0];
       ConvertFromCSF obj = new ConvertFromCSF();
       obj.processFile(filename);
    }
    public void processFile(String filename) throws Exception
    {
       // open file
       FileReader fr = new FileReader(filename);
       BufferedReader br = new BufferedReader(fr);

       // connect to Oracle
       Class.forName("oracle.jdbc.driver.OracleDriver");
       con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oracle8i","scott","tiger");
       ps = con.prepareStatement("insert into products values(?,?,?,?)");
       String line, columns[];
       // headings line. Ignore it
       line = br.readLine();
       Vector v;
       while( (line = br.readLine())!= null)
       {
	   v = getColumns(line);
	   Enumeration e = v.elements();
           int i=1;
           while ( e.hasMoreElements())
           {
             ps.setString(i, e.nextElement().toString());
             i ++;
           }
           // insert into table after values for parameters are set
           ps.executeUpdate();
       }
       fr.close();
       ps.close();
       con.close();
    }
    
    public  Vector getColumns(String line)
    {
      Vector v = new Vector();
      StringBuffer bf = new StringBuffer();
      boolean  instring = false;  // indicates whether we are in string
      for ( int i = 0 ; i < line.length(); i ++)
      {
	      if ( line.charAt(i) == ',')
	      {
		  if (! instring )  // if not already in string.
		  {
		   v.add(new String(bf));  // add buffer to vector
		   bf = new StringBuffer();  // reset buffer
  	          } // end of if
	          else
		          bf.append(",");  // add comma also to string as we are in string
	      } // end of if
	      else
	      if ( line.charAt(i) == '"')  // toggle instring flag when " encountered
	      {
	        if ( instring )
	              instring = false;
    		else
		          instring = true;
              }
              else
                 bf.append( line.charAt(i));
      } // end of for
      v.add(new String(bf));  // add value at the end as column
      return v;
  }
}

Srikanth