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

Connecting to Excel

Carey Brown
Ranch Hand

Joined: Nov 19, 2001
Posts: 174

I'm having trouble getting a Jdbc:Odbc connection to an Excel file. None of the examples I've found in the forum explain how to specify which .xls file you want to connect to. So far I have:
import java.sql.*;
class jexcel
{
public static void
main( String[] args )
{
try
{
String fname = "d:\\_carey\\MyDocuments\\Excel\\PhoneCarey.xls";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
Connection con = DriverManager.getConnection(
"jdbcdbcriver={MicroSoft ExcelDNS Driver (*.xls)};DBQ="
+ fname );
Statement s = con.createStatement();
ResultSet r = s.executeQuery( "select * from [Sheet1$]" );
while( r.next() )
{
System.out.println( r.getString(1) );
}
s.close();
con.close();
}
catch( Exception ex )
{
System.out.println( ex );
}
}

}
I get the error: Data source name not found and no default driver specified.

Vladimir Ergovic
Ranch Hand

Joined: Apr 22, 2001
Posts: 63
First you have to register your Excel file to ODBC. Let's say the name is base.xls like in the example. Then you have to access the 'database' under the name which you have registered via ODBC:
Here is the code (modification of your):
import java.sql.*;
class Jexcel
{
public static void
main( String[] args )
{
try
{
String fname = "base.xls";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
Connection con = DriverManager.getConnection("jdbc dbc:" + fname );
Statement s = con.createStatement();
ResultSet r = s.executeQuery( "select * from [Sheet1$]" );
while( r.next() )
{
System.out.println( r.getString(1) );
}
s.close();
con.close();
}
catch( Exception ex )
{
System.out.println( ex );
}
}
}

// but before running you have to do following -step by step just in case
Start/Settings/Control Panel/ODBC Datasources - the location and the name is different for different Win. Click add/Driver for Excel - over there put the location of the file (Workbook) and THE NAME - in this case base.xls. OK. that should be it !

Vladimir Ergovic
Vladimir Ergovic
Ranch Hand

Joined: Apr 22, 2001
Posts: 63
First you have to register your Excel file to ODBC. Let's say the name is base.xls like in the example. Then you have to access the 'database' under the name which you have registered via ODBC:
Here is the code (modification of your):
import java.sql.*;
class Jexcel
{
public static void
main( String[] args )
{
try
{
String fname = "base.xls";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
Connection con = DriverManager.getConnection("jdbc dbc:" + fname );
Statement s = con.createStatement();
ResultSet r = s.executeQuery( "select * from [Sheet1$]" );
while( r.next() )
{
System.out.println( r.getString(1) );
}
s.close();
con.close();
}
catch( Exception ex )
{
System.out.println( ex );
}
}
}
// but before running you have to do following -step by step just in case
Start/Settings/Control Panel/ODBC Datasources - the location and the name is different for different Win. Click add/Driver for Excel - over there put the location of the file (Workbook) and THE NAME - in this case base.xls. OK. that should be it !
Vladimir Ergovic
Ranch Hand

Joined: Apr 22, 2001
Posts: 63
First you have to register your Excel file to ODBC. Let's say the name is base.xls like in the example. Then you have to access the 'database' under the name which you have registered via ODBC:
Here is the code (modification of yours):
import java.sql.*;
class Jexcel{
public static void main( String[] args ){
try
{
String fname = "base.xls";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
Connection con = DriverManager.getConnection("jdbc dbc:" + fname );
....
...
But before running you have to do following -step by step just in case Start/Settings/Control Panel/ODBC Datasources - the location and the name is different for different Win. Click add/Driver for Excel - over there put the location of the file (Workbook) and THE NAME - in this case base.xls. OK. that should be it !
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connecting to Excel