Jdbc with MS-excel

[Rakesh Sharma]

Please tell me how to use jadc with MS-excel worksheet. Say I have a file "Emp" in excel and I want to retrieve the data from it. How to do it. It will be of great help if steps and code are given.
Thanks

[hemanth kumar]

Hi rakesh
Steps are :
1)Create System DSN to ur Excel Workbook i.e. excel file using Microsoft Excel Driver.
2)In ur java source code connect to that workbook i.e. excel file
3)then open recordset with the sheet name becoming ur table name
4)the columns become ur field names
thats abt it
Here's the working source code
<code>
import java.sql.*;
public class ConnectToExcel
{
public static void main(String a[])
{

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Connect to Excel DSN
String url = "jdbc dbc:test";
Connection connection =
DriverManager.getConnection(url, "", "");
Statement st=connection.createStatement();
// here sheet name equals to table name [sheet1]
String query = "SELECT * from [sheet1$]";
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
String s = rs.getString("EName");
System.out.println(" field values for EName are " + s);
}

}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
</code>
Here test is the DSN name.Sheet1 is the table name and this1 is the column name
Hope the above helps.
So how was ur space flight .just joking
Hemanth

[Suji N]

Dear Hemanth
Your Code Helped me a lot
Thankyou Verymuch
Yours
Suji

Originally posted by hemanth kumar:
Hi rakesh
Steps are :
1)Create System DSN to ur Excel Workbook i.e. excel file using Microsoft Excel Driver.
2)In ur java source code connect to that workbook i.e. excel file
3)then open recordset with the sheet name becoming ur table name
4)the columns become ur field names
thats abt it
Here's the working source code
<code>
import java.sql.*;
public class ConnectToExcel
{
public static void main(String a[])
{

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Connect to Excel DSN
String url = "jdbc dbc:test";
Connection connection =
DriverManager.getConnection(url, "", "");
Statement st=connection.createStatement();
// here sheet name equals to table name [sheet1]
String query = "SELECT * from [sheet1$]";
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
String s = rs.getString("EName");
System.out.println(" field values for EName are " + s);
}

}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
</code>
Here test is the DSN name.Sheet1 is the table name and this1 is the column name
Hope the above helps.
So how was ur space flight .just joking
Hemanth