Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Connecting to Excel

 
Carey Brown
Ranch Hand
Posts: 1538
18
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 !
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic