All About Working with datatables and data Sheets in QTP: Methods and properties

Methods for Datatable object

Following are the methods and properties for interaction with data tables in QTP

1. Datatable.AddSheet  

Adds the specified sheet to the run-time Data Table

datatable.AddSheet(strSheetName)

2. DeleteSheet

Deletes the specified sheet from the run-time Data Table.

Datatable.DeleteSheet(strSheetName)

3. Export Method

Saves a copy of the run-time Data Table in the specified location.

datatable.Export(strExcelFile)

4.ExportSheet Method 

Exports a specified sheet of the run-time Data Table to the specified file.

DataTable.ExportSheet(FileName, DTSheet)

5.GetCurrentRow

Returns the current (active) row in the first sheet in the run-time Data Table (global sheet).

row = DataTable.GetCurrentRow

6. GetRowCount Method

Returns the total number of rows in the longest column in the first sheet in the run-time Data Table (global sheet).

DataTable.GetRowCount

7. GetSheet Method:

Returns the specified sheet from the run-time Data Table.

MyParam=DataTable.GetSheet ("MySheet").AddParameter("Rower", "9")

8. GetSheetCount

Returns the total number of sheets in the run-time Data Table.

intSheet = datatable.getSheetCount

9.  Import Method:

 Imports the specified Microsoft Excel file to the run-time Data Table.

DataTable.ImportSheet(FileName, SheetSource, SheetDest)

10. ImportSheetMethod:

Imports a sheet of a specified file to a specified sheet in the run-time Data Table.

DataTable.ImportSheet(FileName, SheetSource, SheetDest)

11.SetCurrentRow Method:

Sets the specified row as the current (active) row in the run-time Data Table.

DataTable.SetCurrentRow(RowNumber)

DataTable.GetSheet("MySheet").SetCurrentRow(2)

12. SetNextRow Method 

Sets the row after the current (active) row as the new current row in the run-time Data Table.

DataTable.SetNextRow

DataTable.GetSheet("MySheet").SetNextRow

13. GlobalSheet Property 

 Returns the first sheet in the run-time Data Table (global sheet).

 DataTable.GlobalSheet.AddParameter "Name", "Nitin"

14. LocalSheet Property

Returns the current (active) local sheet of the run-time Data Table.

DataTable.LocalSheet.AddParameter "Name", "Nitin"

Methods For DTSheet Object:

Below are the methods to work with the specified sheet in datatable.

1. AddParameter Method 

Adds the specified parameter (column) to the sheet in the run-time Data Table, sets the value of the first row to the specified value

DataTable.GetSheet("dtGlobalSheet").AddParameter "Name","Nitin"

DataTable.AddSheet("MySheet").AddParameter("Name", "Nitin")

paramname = DataTable.LocalSheet.AddParameter("Name", "Nitin").Name 

2. DeleteParameter Method

Deletes the specified parameter from the sheet in the run-time Data Table.

DataTable.GetSheet("MySheet").DeleteParameter("Name") 

3. GetCurrentRow Method

Returns the row number of the current (active) row in the run-time Data Table sheet. 

row = DataTable.GetSheet("MySheet").GetCurrentRow 

4. GetParameter Method 

Retrieves the specified parameter from the run-time Data Table sheet. 

DataTable.GetSheet("ActionA").GetParameter("Date").RawValue

GetParameter("ParamName").value retrieves or sets the value of the cell in the current (active) row of the parameter in the run-time Data Table. 

DataTable.GetSheet("Action1").GetParameter("Destination").Value="Pithoragarh"

DataTable.GetSheet("Action1").GetParameter("Destination").ValueByRow(7) - This gets value for parameter Destination in sheet Action 1 in row 7.

5. GetParameterCount Method 

Returns the total number of parameters (columns) in the run-time Data Table sheet.

paramcount = DataTable.GetSheet("Test").GetParameterCount

6. GetRowCount Method

 Returns the total number of rows in the longest column in the run-time Data Table sheet.

 rowcount = DataTable.GetSheet("Test").GetRowCount

Descriptive programming examples and comparison with Object Repository

Comparing Descriptive Programming with Object Repository

Suppose we record on Google page for testing and then click back for Search button, steps will be recorded in QTP as shown below. An object reposiory stores all the objects used in the recording. Issue with Object repository is that we need to associate object repository with test for execution and if the project size increase, it becomes difficult to maintain the repository.

Browser("Google").Page("Google").WebEdit("q").Set "TESTING"
Browser("Google").Page("Google").WebButton("btnG").Click
Browser("Google").Page("testing - Google Search").Link("Search").Click

Suppose we do not want to use objects from object repository, we can use descriptive programming to describe the objects.

Descriptive programming can be implemented in following two ways:

1. Using Static description:

Suppose we have to define description for 
Browser("Google").Page("Google").WebEdit("q").Set "TESTING"

We need to do the following:

1. Use object spy to identify the object in the page, mostly for html pages, html id is unique for the object, so will use html Id as property for the object.

Browser("title:=Google").Page("title:=Google").WebEdit("html id:=gbqfq").Set "TESTING":

Also in case of multiple object with similar properties, for example, multiple instances of browser, we can use ordinal identifier to identify the object

the above code using ordinal identifier and using regular expressions can be written as:

Browser("title:=.*","index:=0").Page("title:=Goog.*").WebEdit("html id:=gbqfq","index:=0").Set "TESTING":

2. Using Description Object

In descriptive programming, we can create description object and define the description of the object. For above code, we can create description object for each of the following:

Set objdescBR = description.Create
objdescBR("title").value ="Google"
Set objdescPg = description.Create
objdescPg("title").value ="Google"
Set objdescEdit = description.Create
objdescEdit("html id").value ="gbqfq"
objdescEdit("index").value =0

Browser(objdescBR).Page(objdescPg).WebEdit(objdescEdit).Set "TESTING1"

Description object can be very useful if we want to perform similar actions of multiples, for e.g checking multiple checkbox, providing same text in multiple edit box and so on. 

We will describe some examples on Descriptive Programming

1. Getting total number of links in the page:

Set objdescBR = description.Create
objdescBR("title").value ="Google"
Set objdescPg = description.Create
objdescPg("title").value ="Google"
Set objdesc = description.Create
objdesc("micclass").value ="Link"
Set objLinks = Browser(objdescBR).Page(objdescPg).childobjects(objDesc)
Msgbox objLinks.count

Similar to this example, we can fine multiple objects type in the page based on class of the object, Please give it a try for Images, and checkbox in the page

2. We can also like clicking on a link with a particular name or entering value in a particular edit box with code similar to as  below:

Call clickLink("Orkut")

Public Function clickLink(LinkName)
Set objdescBR = description.Create
objdescBR("title").value ="Google"
Set objdescPg = description.Create
objdescPg("title").value ="Google"
Set objdesc = description.Create
objdesc("micclass").value ="Link"
Set objLinks = Browser(objdescBR).Page(objdescPg).childobjects(objDesc)
For i = 0 to objLinks.count - 1
If (objLinks(i).getroproperty("name") = LinkName) then
objLinks(i).click
End If
Next
End Function

How to create and run a batch file

Batch files help user to execute a set of command to be executed in the defined order by  executing a batch file. We can execute large number of commands in one go using bat file.

Creating a batch file

A batch file has extension .bat .To create a new bat file, we can save a file in text editor like notepad in format .bat.

Some useful batch commands

@echo off – this will not echo the text post the command and will show only the execution result in the command prompt.

@echo on – this will echo the text post the command and will show the command as well as the execution result in the command prompt.

Pause– If we use pause, the batch file execution is halted until further intervention of user by pressing keys.

Arguments %1,%2,….. – This gives the parameter for the batch file.

echo val will display val in the command prompt

:: and rem can be used to add remarks to the script

Cls can be used to clear the command prompt

Example :

For e.g we need to compare 2 txt files, but name of files to be compared needs to be passed dynamically and log path also needs to be defined dynamically.

The content of batch file for this will look like

@echo off

Fc %1 %2 /n>%3

Pause

To execute this bat file, we have to write in run as

D:\Sample\new.bat d:\Sample\test4.txt d:\Sample\test7.txt d:\Sample\test9.txt