Tuesday, May 31, 2011

Selenium - Parameterization with CSV

Earlier we saw how to write down data driven test cases using Excel file. Using Excel for data driven has it's own advatages like you can write down you function in excel and get rid of programming logic from your test. However to connect to excel you need JDBC connectivity which has it's own overhead in terms of processing time and you always need to remember to close the connection.

In a way using CSV for selenium automation is much faster than Excel. so let's see how to use CVS file with selenium automation.

Save the following data as data.csv. First row is header

Scenario name,username,password

import java.io.BufferedReader;
import java.io.FileReader;
import java.util.HashMap;
import java.util.Map;
import java.util.StringTokenizer;

 * @author GauranG Shah
public class ReadCSV {
  * @param scenarioName - Row Name
  * @param columnName
  * @param fileName - CSV file name where data is stored
  * @return - Sting value
 public String getValue(String scenarioName, String columnName,String fileName){
  try {

   // csv file containing data
   String strFile  =  fileName;
   String strLine   =   "";
   StringTokenizer st  =   null;
   int lineNumber   =   0;
   // create BufferedReader to read csv file
   BufferedReader br = new BufferedReader(new FileReader(strFile));

   strLine = br.readLine(); //read first line 
   st = new StringTokenizer(strLine, ",");
   int totalRows = st.countTokens();
   Map<Object,String> mp=new HashMap<Object, String>();
   //Fetch the header
   for(int row=0; row<totalRows; row++){
    mp.put(new Integer(row), st.nextToken());

   while ((strLine = br.readLine()) != null){
    st = new StringTokenizer(strLine, ",");
     //Identified the row Now return the specific element based on column name specified.
     totalRows= st.countTokens();
     for(int key=1; key<=totalRows; key++){
      String value = st.nextToken();
       return value;
  }catch (Exception e){
   System.out.println("Exception while reading csv file: " + e);
  return "Element Not Found";
 //This is just to show usage, you can discard this when you use in your project
 public static void main(String[] args) {
  ReadCSV rc = new ReadCSV();
  System.out.println(rc.getValue("valid", "username","data.csv"));


Monday, May 23, 2011

Selenium - Parameterization with EXCEL

let's see how to make your selenium test data driven using excel. Using excel as a data driven has it's own advantage. The biggest advantage is it's in a Table format, means easy to understand and modify.
Now let's take the simple example of Login test where we have multiple scenarios like Valid Login, Wrong Password, Wrong Username and let's see how we will automate it using selenium and excel.

Note: Make sue the excel sheet where you write down the data in above format has name "login"

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

import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

 * @author Gaurang
public class Excel {

 public static Connection con ;
 public static String dbURL =
     "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ= "+ "demo.xls;"
      + "DriverID=22;READONLY=false";
 public static  String  getValueFromExcel(String SheetName, String ColumnName, String Scenario) throws SQLException, ClassNotFoundException {

  Connection con = DriverManager.getConnection(dbURL);
   if(con == null)
    System.out.println("Not able to connect to MS EXCEL");
  Statement stmnt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  String sFormattedSheetName = "[" + SheetName + "$]";
  SheetName = sFormattedSheetName;
  String query = "Select "+ColumnName+" from "+SheetName+" Where TestScenario='"+Scenario+"'" ;
  ResultSet rs = stmnt.executeQuery( query );  
  rs.next(); //initially cursors is at -1 position
  String value = rs.getString(1);
  return value;
 @DataProvider(name = "fromExcel")
 public Object[][] createData1() {
  return new Object[][] {
    { "Valid Login" },
    { "Wrong Username"},
    {"Wrong Password"}
 public void dataDrivenUsingExcel(String scenarioName) throws SQLException, ClassNotFoundException {
  System.out.println("Scenario="+scenarioName+" UserName="+Excel.getValueFromExcel("login", "UserName", scenarioName));
  System.out.println("Scenario="+scenarioName+" Password="+Excel.getValueFromExcel("login", "Password", scenarioName));