Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

Read from an ODBC Excel Data Source using Silk4J

Read from an ODBC Excel Data Source using Silk4J


It is possible to set up a jdbc-odbc connection to extract data from an excel source using Silk4J.  In this example I have set up an excel file with some sample row values.  I will read this file into my Silk4J script and print the values into my sample notepad application.

1. First set up a 32bit dsn and add the Microsoft Excel spreadsheet as an ODBC Data Source.

2. In Silk4J add the following references to your script:-

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

3. In my Silk4J script I will be required to do the following;-
- Load the Driver:-
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

- Create Connection with the Data Source "excel":-
Connection con = DriverManager.getConnection("jdbc:odbc:excel");

- Create Statement;-
Statement s = con.createStatement();

- create Query that returns the data:-
String query = "select * from [Sheet1$]";

4. Please see Silk4J script below that performs the above actions and prints the values to notepad:-

package notepad;

import com.borland.silktest.jtf.Desktop;
import org.junit.Before;
import com.borland.silktest.jtf.BaseState;
import org.junit.Test;
import com.borland.silktest.jtf.Window;
import com.borland.silktest.jtf.TextField;
import com.borland.silktest.jtf.common.types.TextPosition;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class mytest {

    private Desktop desktop = new Desktop();

    @Before
    public void baseState() {
        BaseState baseState = new BaseState("silk4j.settings");
        baseState.execute(desktop);
    }

    @Test
    public void test1() {
        desktop.<Window>find("/Window[@caption='Untitled - Notepad']").setActive(); //set notepad active
        desktop.<TextField>find("/Window[@caption='Untitled - Notepad']//TextField").setPosition(new TextPosition(0, 0)); //click on text area

        
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //Load Driver
            Connection con = DriverManager.getConnection("jdbc:odbc:excel"); //Create Connection with Data Source Name : excel
            Statement s = con.createStatement(); // Create Statement
     
            String query = "select * from [Sheet1$]"; // Create Query
            s.execute(query); // Execute Query
            ResultSet rs = s.getResultSet(); //return the data from Statement into ResultSet
     
            while (rs.next()) // Retrieve data from ResultSet
                {
                desktop.<TextField>find("/Window[@caption='Untitled - Notepad']//TextField").typeKeys("Row " +rs.getRow()+": " +rs.getString(1)); //enter data to notepad
                desktop.<TextField>find("/Window[@caption='Untitled - Notepad']//TextField").typeKeys("<Enter>");

                }
     
            s.close();
            con.close();
        }
        catch (Exception e)
         {
            System.out.println("Exception : "+e);
         }        
    }
}

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2013-08-13 12:26
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.