Srikanth Technologies

Loading Excel sheet data to Oracle table

The following procedure is to be applied to load data that is in an Excel spreadsheet 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. Write the following code:
    import java.sql.*;
    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();
        public void processFile(String filename) throws Exception
           // open file
           FileReader fr = new FileReader(filename);
           BufferedReader br = new BufferedReader(fr);
           // connect to Oracle
           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
        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
    		           bf.append(",");  // add comma also to string as we are in string
    	      } // end of if
    	      if ( line.charAt(i) == '"')  // toggle instring flag when " encountered
    	        if ( instring )
    	              instring = false;
    		          instring = true;
    		      bf.append( line.charAt(i));
          } // end of for
          v.add(new String(bf));  // add value at the end as column
          return v;
  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 by passing filename where comma delimited data is existing.
  9. Go to SQL*Plus and select data from table.