Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Accesing Excel data sheets through JDBC

 
Preethi Suryam
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!
I am working with JDBC and i am trying to access excel data sheets using JDBC.pls can anybody help me out how to do it.
Thanx in Advance
Preethi
 
Sameer Sawla
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have to use JDBC:ODBC bridge. Create ODBC through odbc adminstrator for Excell data. Then you can connect to it.
 
Preethi Suryam
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sameer!
I know how to connect.The problem is i don't know what is the query i have to pass in order to get the data from the sheet.hope u got me.kindly help me.
Thanx a lot for ur response.
Preethi
 
mars76
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
save the excel as a CSV(comma seperated values) file and read data from that text file, hoping that the format of the Excel is fixed in your application .

First of all are you opening that Excel sheet in a browser , i.e, is your application is a web based application? If so , you can as well use one more method,irrespective of the server environment. Just provide the option to download the excel sheet , the user will download that file and he will enter the data and after that he has to upload the same file to the server, form which we are going to read data and store it in tha database...
Hope this will help U, if not let me know exactly what's the application type and the scenario...
Sateesh
 
Preethi Suryam
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sateesh!
I accept data from the user through a form and insert it into an excel sheet directly.i want to access excel through JDBC.we have a driver for that to connect to the excel.until there i am clear.
what is the query i should pass?
for ex:-
"select count * from sheet1" this is not working.i am not able to read the data from the sheet.hope u got me.
Regards
Preethi
 
mars76
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Yes , i got the point. If you have a single sheet in a workbook.
Use the following steps..
1) create a FileDSN and point this to the Excel file from which U want to read data
2) In U'r java file craete the Connection Object using the jdbc-odbc bridge and the query should look like...
String strQuery="select * from [Sheet1$]"
Every thing in the Excel sheet will be stored as a table , I used the name Sheet1$ because,
The default name for the first sheet is Sheet1
and it will be converted as table Sheet1$..
As $ is not allowed enclose in Square brackets...
Hope this will help U..
Sateesh
[This message has been edited by sateesh kommineni (edited December 21, 2000).]
[This message has been edited by sateesh kommineni (edited December 21, 2000).]
 
Preethi Suryam
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sateesh!
Thanx a lot for ur reply.I will follow ur solution and if any queries will trun back to u.Thanx once again.
Preethi
 
Sameer Sawla
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
here is sample for your reference :
import java.sql.*;
class JavaExcel
{
JavaExcel()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc dbc:JavaExcel","","");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("Select Name,Marks from [Sam$]");
while (rs.next())
{
System.out.println(rs.getString("Name"));
System.out.println(rs.getInt("Marks"));
System.out.println("-------");
}
}
catch (Exception e) {e.printStackTrace();}
}
public static void main(String[] args)
{
new JavaExcel();
}
}
Hope this solved your problem.
 
Preethi Suryam
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sameer!
yes even i worked out in the same way and i got it.thanx for ur response.
Have a Nice Day!
preethi
 
Douglas Kent
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys,
Am trying to connect to execl as well on NT 4.0. Used the MS ODBC manager to set up a USER datasource named EXCEL. When I try to run your sample code, I get "SQLException: no suitable driver". I used your getConnection code exactly, as well as trying "getConnection("jdbc dbc:EXCEL","",""); ". What am I doing wrong?
Thanks!
 
Douglas Kent
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Update: I used ""Jdbc:Odbc:EXCEL","","" - that worked to get the connection. Another question: when doing a select, what name do you use; ie. "SELECT * from ???" ? An excel spreadsheet does not have a table name in the classic sense. ALso, how does one reference columns?
Thanks again...
 
Preethi Suryam
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!
The sheet name is the table name in our case.The columns can be referenced with the column name.pls try it out.
Have a Nice Day!
Preethi
 
Naresh Babu
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I am also trying to write & read from excel using JDBC.
When i give:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection"Jdbc:Odbc:ExcelDSN","","");
I am getting Exception as:java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
WHile creating DSN, what is the driver to be choosen?
Thanks
 
Manikandan Kasirajan
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All Pls help me where i am wrong i am coding as below for testing the connection and applet and excel,i get error while making the dsn connection that is in DriverManager.getConnection("jdbc dbc:Excel Files"); as below.
java.sql.SQLException: No data found
Excel Files dsn is default user dsn in odbc for Excel. i even tried by making a new system dsn and user dsn even then i get the same error can any one guide me. pls it is urgent.......
import java.sql.*;
public class excelWrite
{
public static void main(String args[]) {
Connection conn=null;
Statement stmt=null;
String sql="";
ResultSet rs=null;
String srno="10";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println(" "+Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"));
conn=DriverManager.getConnection("jdbc dbc:Excel Files");
stmt=conn.createStatement();
//sql="INSERT INTO [Sheet1$](srno,test,description,output,output1,pass) VALUES ('1','Hari','Venke','Vasan','Swami','Shalin')";
//int i =stmt.executeUpdate(sql);
}
catch (Exception e){
System.err.println(e);
}
finally {
try{
rs.close();
stmt.close();
conn.close();
rs=null;
stmt=null;
conn=null;
}
catch(Exception e){}
}
}
}
 
Braj Prasad
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
check out the following link posted at javaworld. Has all the information you are looking for, required to read Excel Files using JDBC.
http://www.javaworld.com/javaworld/javaqa/2001-06/04-qa-0629-excel.html
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic