JDBC Tutorial

Retrieving data from excel

In real world applications, there is a possibility of retrieving the data from conventional data bases like ms-excel.

Steps for retrieving data from ms-excel:

  1. Create an excel sheet, enter the column names along with data, rename the sheet1 as user defined name which is treated as table name.
  2. retrieving data excel
  3. Save the excel sheet in a current working directory.
  4. For example:

    D:\advanced\jdbc\stbook.xls
  5. Create DSN for excel
  6. create DSN excel
  7. Use xldsn while obtaining a connection from excel.

For example:

Connection con=DriverManager.getConnetion ("jdbc : odbc : xldsn");
                        

Note: In order to refer excel sheet name as a database sheet name we should use the format [<sheet name> $]

Write a jdbc program to retrieve the data from excel?

Answer:

import java.sql.*;

class XSelect {

    public static void main(String[] args) {
        try {
            DriverManager.registerDriver(new Sun.jdbc.odbc.JdbcOdbcDriver());
            System.out.println("DRIVERS LOADED...");
            Connection con = DriverManager.getConnection("jdbc:odbc:xldsn");
            System.out.println("CONNECTION  ESTABLISHED...");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from [student$]");
            while (rs.next()) {
                System.out.println(rs.getString(1) + " " + rs.getString(2) + "	" + rs.getString(3));
            }
            con.close();
        } catch (SQLException sqle) {

            sqle.printStackTrace();
        }
    }// main
};// XSelect