Excel to Oracle Data Transfer

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

 

--

 

MoHaN.K.RaJ

MCA,GVP,VIZAG,

+91 91777 50037