Read/Write from Excel using Sahi Automation Tool

While automating an application, taking the modern automation frameworks into consideration it becomes a necessary action to make our script interact with an excel sheet or csv data file in order to read and write the data.

Usually we use excel sheet, as this provides more organized way of storing the data.

Let’s see the tasks to be performed:-

Objective: – Read/write data from excel sheet using Sahi.

Resources: – Excel file (.xls, .xlsx), Sahi OS Automation tool and Microsoft Access database engine 2010 (when interacting with office 2010 in a 64-bit OS)

Solution: – We will be writing a test script in Sahi (.sah file) that will perform the following steps:-

–       Login to the application by reading Username and Password from the excel sheet.

–       Execute the script.

–      After the execution is done, insert Pass if everything goes fine else insert Fail if the script is halted due to an error in the functionality.

1.  To set up Sahi in your system and launch Sahi controller you can refer the below blog:-

https://blogs.perficient.com/delivery/blog/2013/12/19/configuration-of-sahi-automation-tool

 

2 . Let’s record the script using the Sahi controller.

In the above screenshot of Sahi controller, click Record button and perform the steps you want to record and save it as “Login.sah”. The script will look something like this:-

 

Login.sah

_setValue(_textbox(“userid”), “Username”);

_setValue(_password(“password”),”password”);

_click(_span(“Login”));

 

//End of the script

 

3.  After the required steps are recorded, in order to interact with the excel sheet we need to write a connection string to the excel driver as follows:-

var db = _getDB(“sun.jdbc.odbc.JdbcOdbcDriver”,

“jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)}; DBQ=D:\\Sahi\\sahi\\Login.xls;readOnly=false”,””,””);

In the above statement you can see the following:-

(a)   JdbcOdbcDriver is required to connect with odbc sources.

(b)   Since we want to read excel sheet, we need use Microsoft Excel Driver (.xls).

Please note if you want to use (.xlsx) driver of MS office 2010 in a 64 bit. We need to install the appropriate driver from the below link:-

http://www.microsoft.com/en-us/download/details.aspx?id=13255

Note: – No driver is required for Microsoft Excel Driver (.xls) in Windows NT.

(c)   The 3^rd argument is to mention the path of excel file to which you want to perform read/write operation. In this case it’s “Login.xls”.

(d)   Readonly as False as this requires write operation as well.

4. After you setup the connection string, let’s see how to read the values from the excel sheet:-

 

Please note few things about the excel sheet.

–       Workbook name – Login.xls

–       Sheet name – Sheet1

–       Username – Column heading

–       Password – Column heading

–       Result – Column heading

In order to read the values from this sheet, we need to write a query similar to sql (which involves Select, Update, Insert and other DML statements).

Var $rs = db.select(“select * from [Sheet1$]”);

In the above statement you can see the following:-

(a)   It’s a Select query to list out the data present in Sheet1 under column headings.

(b)   The result of the query will be stored in the variable called rs.

To get the values from the columns “Username” and “Password”, below is the statement:-

Syntax : <variable name> = $rs[<indexOfRowNum>][<ColumnName>]

Var Username = $rs[0][“Username”]; — admin(output)

Var Password = $rs[0][“Password”]; — password(output)

5. After you execute the script by reading the username and password and click on Login button .If we are able to login successfully insert Pass into “Result” column in the excel sheet else insert Fail. To insert values below is the command:-

db.update(“update [Sheet1$] set Result=’Pass’ where Username='”+$rs[0][“Username”]+”‘”);

and

db.update(“update [Sheet1$] set Result=’Fail’ where Username='”+$rs[0][“Username”]+”‘”);

 

In the above statement you can see the following:-

(a)   It’s an Update query to update any cell in Sheet1 with the targeted data (Pass / Fail).

It becomes important where you place the above two statements in your script. Ideal place would be to place the statement with Pass at the end of the script and the statement with Fail whenever there is an error encountered. This involves try/catch block as part of exception handling.

Let’s see the complete script:-

try

{

var db = _getDB(“sun.jdbc.odbc.JdbcOdbcDriver”,

“jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)}; DBQ=D:\\Sahi\\sahi\\Login.xls;readOnly=false”,””,””);

 

$rs = db.select(“select * from [Sheet1$]”);

Var $Username = $rs[0][“Username”];

Var $Password = $rs[0][“Password”];

_setValue(_textbox(“userid”),$Username);

_setValue(_password(“password”), $Password);

_click(_span(“Login”));

 

db.update(“update [Sheet1$] set Result=’Pass’ where Username='”+$rs[0][“Username”]+”‘”);

}

catch(e)

{

db.update(“update [Sheet1$] set Result=’Fail’ where Username='”+$rs[0][“Username”]+”‘”);

}

Please note that the catch block will execute only when there is an error encountered in the statements placed in try block.