Exporting and Importing between Oracle database and Excel file

[pjeypal]

Hi,

I have an XLS file with over 1000s of records which i need to export to an oracle table with the same record structure as excel doc.I wrote this code for transferring data from excel to oracle.

import java.sql.*;

public class XLSHandler {
public PreparedStatement xlsData;

public XLSHandler() {
export("");
}

public void export(String xlsFile){
Connection connection=null;
try{
//Class.forName("oracle.jdbc.driver.OracleDriver");
//Connection c = DriverManager.getConnection("jdbcracle:thinhostname:1521:SID", "user", "passwd");

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbcdbc:jarus");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select * from [WithParent$]");
ResultSetMetaData rsmd=rs.getMetaData();
int numberOfColumns=rsmd.getColumnCount();
//System.out.println(""+numberOfColumns);
Connection con1=DriverManager.getConnection("jdbcdbc:htool","scott","tiger");
xlsData=con1.prepareStatement("insert into buildhierarchy" +
"(location,description,parent,equipment,eqdescription,drawing,ex01,ex02,ex03,ex04,ex05) values(?,?,?,?,?,?,?,?,?,?,?)");
while(rs.next()){
for(int i=1;i<=numberOfColumns;i++){
if(i>1)System.out.print(",");
String columnValue=rs.getString(i);
System.out.print(columnValue);
xlsData.setString(i,columnValue);
}
//System.out.println("");
}
xlsData.executeUpdate();
st.close();
con.close();
}
catch(Exception ex){
System.err.print("Exception:");
System.err.println(ex.getMessage());
}

}
/*public static void main(String args[]){
new XLSHandler();
}*/
}

But this does not seem to work properly and mostly just 1 or 2 of the 1000 records are inserted.Any alternate method for importing to oracle and exporting back to excel from oracle table?

Any help appreciated.

Thanks,
Priya

 

[pjeypal]

Hi,
It was a small error.I put executeUpdate() outside loop.ITs working.Any tips of how to export data from oracle table back to Excel file.

Thanks
Priya

 

[pjeypal]

Hi,
Importing data to oracle from Excel is working perfectly with the above code.But when i try to export data back from oracle to Excel using the same method its raising exception

Exception:[Microsoft][ODBC Excel Driver] Operation must use an updateable query.


I'm adding the code here for exporting from oracle to Excel

import java.sql.*;

public class OratoXLS {
public PreparedStatement xlsData;

public OratoXLS() {
export("");
}

public void export(String xlsFile){
Connection connection=null;
try{
//Class.forName("oracle.jdbc.driver.OracleDriver");
//Connection c = DriverManager.getConnection("jdbcracle:thinhostname:1521:SID", "user", "passwd");

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbcdbc:htool1","scott","tiger");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select * from buildhierarchy");
//ResultSet rs=st.executeQuery("select * from [WithParent$]");
ResultSetMetaData rsmd=rs.getMetaData();
int numberOfColumns=rsmd.getColumnCount();

Connection con1=DriverManager.getConnection("jdbcdbc:jarus");
xlsData=con1.prepareStatement("insert into [WithParent$]" +
"(location,description,parent,equipment,eqdescription,drawing,ex01,ex02,ex03,ex04,ex05) values(?,?,?,?,?,?,?,?,?,?,?)");
while(rs.next()){
for(int i=1;i<=numberOfColumns;i++){
if(i>1)System.out.print(",");
String columnValue=rs.getString(i);
System.out.print(columnValue);
xlsData.setString(i,columnValue);
}
System.out.println("");
xlsData.executeUpdate();
}

st.close();
con.close();
}
catch(Exception ex){
System.err.print("Exception:");
System.err.println(ex.getMessage());
}

}
/*public static void main(String args[]){
new XLSHandler();
}*/
}