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.- Open Excel Spreadsheet in MS Excel. For example : products.xls.
- 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.
- Connect to Oracle
- Create table using CREATE TABLE command. The structure of the table must match the formation in Excel sheet.
- Download the following Java program.
- Make necessary changes to the program. The table name in INSERT command and parameters are to be changed according to your requirement.
- Compile Java program
- Run java program given below by passing filename where comma delimited data is existing.
- 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; } } --
MCA,GVP,VIZAG,
+91 91777 50037
Who doesn't remember this controversial file-sharing kingpin? Developed by Northeastern University student Shawn Fanning, Napster was a groundbreaking application that enabled users to share MP3s painlessly for the first time through a peer-to-peer network. Napster has since been sued, shuttered, and reborn as a subscription music service, but its legacy remains.
Composing