Selenium Java - Connecting Spread Sheet Using JDBC


Sample.xls
LoginID
FirstName
LastName
Qua@12
Mark
Steven
Msmith
Masq
Smith
Yellow
David
Robin
Yuri98
Yuri
Isral
roy6943
Roy
Johnson


Since Excel comes with an ODBC driver, we'll use the JDBC-ODBC bridge driver that comes packaged with Sun's JDK to connect to our spreadsheet.

In Excel, the name of the worksheet is the equivalent of the database table name, while the header names found on the first row of the worksheet is the equivalent of the table field names. Therefore, when accessing Excel via jdbc, it is very important to place your data with the headers starting at row 1.
1. Create a new ODBC Data Source using the Microsoft Excel Driver. Name the DSN "excel", and have it point to c:\Samples.xls.

2. Type in the following code:
  package classes;
  import java.sql.*;

  public class TestServer
  {
    static
    {
        try  {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        }
        catch (Exception e) {
            System.err.println(e);
        }
    }

    public static void main(String args[]) {
        Connection conn=null;
        Statement stmt=null;
        String sql="";
        ResultSet rs=null;

        try {
            conn=DriverManager.getConnection("jdbc:odbc:excel","","");
            stmt=conn.createStatement();
            sql="select * from [Sheet1$]";
            rs=stmt.executeQuery(sql);

            while(rs.next()){
                System.out.println(rs.getString("LoginID")+
                  " "+ rs.getString("FirstName")+" "+
                  rs.getString("LastName"));
            }
        }
        catch (Exception e){
            System.err.println(e);
        }
        finally {
            try{
                rs.close();
                stmt.close();
                conn.close();
                rs=null;
                stmt=null;
                conn=null;
            }
            catch(Exception e){}
        }
    }
 }

Our data is residing in the worksheet called "Sheet1", you'll have to refer to the sheet as Sheet1$ in your SQL statements. And because the dollar sign symbol is a reserved character in SQL, you'll have to encapsulate the word Sheet1$ in brackets, as shown in the code.

7 comments:

  1. Thanks for the great information in your blog Selenium Training in Chennai

    ReplyDelete
  2. Thanks for sharing great information in your blog. Got to learn new things from your Blog . It was very nice blog to learn about Selenium.
    Selenium

    ReplyDelete
  3. Advanced Selenium Framework Training in Chennai by Vishwa

    Hi, Reach Mr.Vishwa for Best Selenium and Coded UI Training in Chennai with real time project assistance. This people will teach everything from the basics up to advanced level scenarios from frameworks.

    I learned from Mr.Vishwa and Raj they are very good automation people for Selenium Frameworks. After joined with them I learned many things and now I am writing automation scripts in own project. Vishwa 9003085882
    selenium training in chennai

    ReplyDelete
  4. nice blog...The leading training concern THE CREATING EXPERTS are providing SELENIUM training and here is the link

    http://thecreatingexperts.com/selenium-training-in-chennai/

    contact 8122241286

    ReplyDelete
  5. The Creating Experts provides training in SELENIUM

    They provide hands on training with real time scenarios

    http://thecreatingexperts.com/selenium-training-in-chennai/

    contact +91-08122241286

    ReplyDelete
  6. Very Nice...Contains A to Z concepts...Thanks a lot.. and looking for the best selenium training in Chennai with certification from us. we have experienced selenium experts offer selenium course training with industry exposure. Check Here https://goo.gl/VRNEXv

    ReplyDelete