aspose file tools*
The moose likes JDBC and the fly likes Accesing Excel data  sheets through JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Accesing Excel data  sheets through JDBC" Watch "Accesing Excel data  sheets through JDBC" New topic
Author

Accesing Excel data sheets through JDBC

Preethi Suryam
Ranch Hand

Joined: Nov 17, 2000
Posts: 92
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

Joined: Nov 24, 2000
Posts: 8
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

Joined: Nov 17, 2000
Posts: 92
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

Joined: Nov 30, 2000
Posts: 30
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

Joined: Nov 17, 2000
Posts: 92
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

Joined: Nov 30, 2000
Posts: 30
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

Joined: Nov 17, 2000
Posts: 92
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

Joined: Nov 24, 2000
Posts: 8
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

Joined: Nov 17, 2000
Posts: 92
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

Joined: Oct 06, 2000
Posts: 171
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

Joined: Oct 06, 2000
Posts: 171
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

Joined: Nov 17, 2000
Posts: 92
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

Joined: Mar 20, 2001
Posts: 29
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

Joined: Jul 07, 2003
Posts: 4
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){}
}
}
}


Warm Regards<br />Mani
Braj Prasad
Greenhorn

Joined: Apr 08, 2002
Posts: 16
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
 
wood burning stoves
 
subject: Accesing Excel data sheets through JDBC