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

Post Your Comment

Image

Enter the code given in the above image :
Enter Your Name :
Enter Your Email Address :
Enter Your Comment :

Comments