ODBC connection to MS Excel file

[Lakshmi.Vaidhyam]

hi,

i am new to java programming. i need to read the contents of MS Excel file using ODBC Driver.
i cannot use a DSN connection. i have written the following code to establish a DSNLess connection.

String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
String excel_path="C:\\employee.xls";
String Connection_str="jdbc:odbcriver={Microsoft Excel Driver(*.xls)};DBQ="+ excel_path +";DriverID=22;READONLY=false;";

try {
Class.forName(driverName);
Connection con = DriverManager.getConnection(Connection_str,"","");
} catch (SQLException e) {....

but i get the exception
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)


could someone please help me.......



Thanks,
Lakshmi

[Modifier]

String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
String excel_path="C:\\employee.xls";
String Connection_str="jdbc:odbcriver={Microsoft Excel Driver(*.xls)};DBQ="+ excel_path +";DriverID=22;READONLY=false;";

try {
Class.forName(driverName);
Connection con = DriverManager.getConnection(Connection_str,"","");
} catch (SQLException e) {....

This is your coding. And you have not mentioned the excel_path value in proper way. That is why you are getting this exception error.

String excel_path="C:\\employee.xls";

This must be as

String excel_path="C:/employee.xls";

Do this change and execute your program. You would not get any error.

[Lakshmi.Vaidhyam]

i have tried bothe ways. i get the same exception whether i use "/" or "\\"

[Scubie67]

This error can get generated if you have not created the Data-source properly. Follow the steps to create the data source :

• Click on the start button and then open the Control Panel.
• In Control Panel you will find the Administrative Tools.
• Go in that and select the option Data Source ODBC.
• Then click on the User DSN.
• After clicking on that click on the add.
• Then select the Driver Do Microsoft Access.
• After this click on select and Browse the .mdb file(your access file) and clicked then click OK.

Hope that your error will be fixed after creating the Data Source.

[Lakshmi.Vaidhyam]

ODBC connection with the DSN works fine. but i cannot use it for the application. i need to establish a DSNLess connection. i.e., i cannot expect users of my tool to go add DSN manually in the Control Panel giving exactly the name i have mentioned in the code. i need a DSNLess connection.

[karthika]

Hi Lakshmi
Did u get a solution for DSNless connection to excel?Pl. mail me at kattu.pune@gmail.com

[Janos™]

Hi Lakshmi
Did u get a solution for DSNless connection to excel?Pl. mail me at kattu.pune@gmail.com

I think that in Excel VBA, you could set it up via a connection. I have just never used (that I'm aware of) the type connections you're talking about but I would wonder if it will work via an ADODB connection. In any case you can get a direct reference from microsoft article here - http://support.microsoft.com/kb/165866