July 13, 2012

DataTables in QTP

When you test your applications, you may want to check how the application performs the same operations with multiple sets of data. For example, suppose you want to check how your Web site responds to ten separate sets of data. You could create ten separate tests, each with its own set of data. But thats not feasible and code length is going to very large. Your sets of data can be as large as 1000 values. So writing same code lines for each value of test data is not a good practice. Alternatively, you can create Data Table parameters so that your test runs ten times, each time using a different set of data. 
DataTable is just like Microsoft Excel file. The DataTable contains a global sheet and a separate sheet for each action in the test. So we two types of sheets in data table.
1. Global sheet
2. Local sheet
Global sheet is available to all actions in the test and local sheet is available to the corresponding action.  The best practice is to use local data sheet while parameterizing the test.

Types of DataTable:
Design Time DataTable: While creating editing the test scripts, you enter data directly in the data table  displayed there. You can view data table by selecting View >> Data Table option. This data table is called Design Time DataTable.
Runtime DataTable: The data table used while running the test is called Runtime DataTable. The data in the runtime data table may be same as design time data table and/or can be entered by importing data from excel sheet, text file etc. The runtime data table can be seen in the Test Results window when run session is ended.

DataTable Parameter:
Each column in global/local sheet of datatable is called a Parameter. We can rename the parameter just by double clicking on the column header and giving the name to it. Data can be entered in the column/parameter simply by clicking on the cell and entering the value.

Parameterizing the test:
Instead of using hardcoded values in the script, we can store the test data in the data table and give reference of same in the script which help perform the same operations on the AUT with multiple sets of data. This all is done through data table methods and properties.

DataTable Methods:
These methods are applied on either DataTable object or DTSheet object or DTParameter object. Here are the methods and their properties:

DataTable Object - Methods
Add Sheet:
Adds the specified sheet to the run time data table.
DataTable.AddSheet(“My Sheet”)

DeleteSheet:
Deletes the specified sheet from the run time data table.
DataTable.DeleteSheet “My Sheet”

Export:
Exports the complete data table to the specified path.
DataTable.Export(“C:\abc.xls”)

ExportSheet:
Exports the current sheet to the specifed path.
DataTable.ExportSheet “C:\abc.xls”,1

GetCurrentRow: (for global sheet)
Retrieves the current row number of the global data sheet.
Row=DataTable.GetCurrentRow
Reporter.ReportEvent 1, “Row Number”, row

GetRowCount: (for global sheet)
Retrieves the number of rows in the longest column in the first sheet(global data sheet) in the run time data table.
Rowcount=DataTable.Get Sheet(“My Sheet”).GetRowCount
Reporter.ReportEvent 2, “There are” &rowcount, “rows in the data sheet”

GetSheet:
Retrieves the specified sheet in the run time data table.
DataTable.Getsheet(“My Sheet”).AddParameter(“Time”,”08:00”)

GetSheetCount:
Retrieves the total number of sheets in the run time data table.
Sheetcount=DataTable.GetSheetCount
Reporter.ReportEvent 0, “sheet number”, “There are” &sheetcount “sheets in data table”

Import:
Import the specified Microsoft Excel file to the run time data table.
DataTable.Import(“C:\abc.xls”)

ImportSheet:
Imports the specified sheet of Microsoft Excel file to the run time data table.
DataTable.ImportSheet”C:\abc.xls”, 1, ”Name”

SetCurrentRow:
Sets the specified row as the current(active) row in the run time data table.
DataTable.SetCurrentRow(2)

SetNextRow:
Sets the row next to the current row as the new current row in the run time data table sheet.
DataTable.GetSheet(“My Sheet”).SetNextRow

SetPrevRow:
Sets the row above the current row as the new current row in the run time data table sheet.
DataTable.SetPrevRow

DataTable Object - Properties
GlobalSheet Property:Description: Returns the first sheet in the datat table.
DataTable.GlobalSheet.AddParameter “Time”,”08:00”

LocalSheet Property:
Description: Returns the current (active) sheet of the data table.
MyParam=DataTable.LocalSheet.AddParameter “Time”,”09:00”

RawValue Property:
Description: Returns the raw value of the cell in the specified parameter of the current row of the data table. The raw value is the actual string written in the cell before the cell is computed, such as the actual text from a formula.
FormulaVal=DataTable.RawValue(“Date”,”ActionA”)

Value Property:
Description: Data table default property. Retrieves or sets the value of the cell in specified parameter and the current row of the data table.
DataTable.Value(2,3)=”New York”

DTParameter - Methods
DtSheet.AddParameter:   (Adds a new column.)
DataTable.GetSheet(“dtGlobalSheet”).AddParameter “NewColumnName”,”FirstRowVaule”

DtSheet.GetParameter:
Description:  Retrieves the specified parameter from the run time data table sheet.
DataTable.GetSheet(“My Sheet”).GetParameter(“Destination”)

DtParameter – Properties
Name Property:
Description: Retrieves the name of the parameter in run time data table.
ParamName=DataTable.LocalSheet.AddParameter(“NewColumnName”,”Row1Value”).Name

RawValue Property:
Description: The raw value of the cell in the current row of the parameter in the run time data table. The raw value is the actual string written in the cell before the cell is computed, such as the actual text from a formula.
The following example uses the RawValue property to find the formula used in current row of the Date column in the ActionA sheet of the run time data table.
The statement below runs the Value:=Now()
FormulaVal=DataTable.GetSheet(“ActionA”).GetParameter(“Date”).RawValue

Value Property:
Parameter default property. Retrieves or sets the value of cell in the current row of parameter in the run time data table.
DataTable.GetSheet(“ActionA”).GetParameter(“Destination”).Value=”New York”

ValueByRow Property:
Retrieves the value of the cell in the specified row of the parameter in the run time data table.
DataTable.GetSheet(“ActionA”).GetParameter(“Destination”).ValueByRow(4)

DTSheet Methods
AddParameter Method:
Add the specific parameter(column) to the sheet in Run Time data table, sets the value of the first row to the specified value, and returns the parameter so that you can directly set or retrieve properties of new parameter in same statement.
Variable=DataTable.AddSheet(“My Sheet”).AddParameter(“Time”,”08:00”)

DeleteParameter Method:
Deletes the specified parameter from the sheet in run time data table.
DataTable.GetSheet(“My Sheet”).DeleteParameter(“Time”)

GetCurrentRow Method:
Returns the row number of the current(active) row in the run time data table sheet.
Rownum=DataTable.GetSheet(“My Sheet”).GetCurrentRow
Reporter.ReportEvent 1, “Row Number”, rownum

GetParameter Method:
Returns the specified parameter from the run time data table sheet.
DataTable.GetSheet(“My Sheet”).GetParameter

GetParameterCount Method:
Returns the total number of parameters(columns) from the run time data table sheet.
Paramcount=DataTable.GetSheet(“My Sheet”).GetParameterCount
Reporter.ReportEvent 2, “There are”, &paramcount, “columns in this sheet”

GetRowCount Method:
Returns the total number of rows in the longest column in the run time data table sheet.
rowcount=DataTable.GetSheet(“My Sheet”).GetRowCount
Reporter.ReportEvent 2, “There are”, &rowcount, “active rows in this sheet”

SetCurrentRow Method:
Sets the specified row as the current row in the run time data table sheet.
DataTable.GetSheet(“My Sheet”).SetCurrentRow(2)

SetNextRow Method:
Sets the row next to the current row as the new current row in the run time data table sheet.
DataTable.GetSheet(“My Sheet”).SetNextRow

SetPrevRow Method:
Sets the row above the current row as the new current row in the run time data table sheet.
DataTable.GetSheet(“My Sheet”).SetPrevRow

DTSheet Properties:
Name Property:
Returns the name of the run time data table sheet.
Sheetname=DataTable.LocalSheet.Name
Reporter.ReportEvent 1, “The active sheet is”, sheetname


14 comments:

  1. Simply wаnt to saу уour агticle is as amаzіng.
    Thе cleaгnеss fοr your sunmit is
    juѕt sрectacular and thаt і сould
    think you're knowledgeable in this subјect.
    Fіne alοng wіth your permіsѕion allοw me tο
    grasp your RSS feeԁ to staу up to dаte wіth drаwіng close post.
    Thankѕ οne millіоn and please κeep up the enјoyablе ωork.



    Tаκе а look at my page :: Leaгn Me ()

    ReplyDelete
  2. I've been using QTP for four weeks now, and I would like to say your article just made it simple for me to understand/use data tables. Thank you

    ReplyDelete
  3. Content is superb...hats off

    ReplyDelete
  4. Its Realy amazing "Munek Dahiya", Once again thank you very much for detailed explanation

    ReplyDelete
  5. Hello Munek,

    Thanks for your article. It is looking good. I've a doubt on 'Value' parameter example under - DtParameter – Properties.

    Can you please have a look at this statement -

    DataTable.GetSheet(“ActionA”).GetParameter(“Destination”).Value=”New York”

    Here, you're using 'GetParameter' method but setting a value to the run time data table. I believe that should be 'SetParameter'.

    ReplyDelete
    Replies
    1. Hi RamaRao,

      Thanks for your comment!

      We need to first reach the parameter to which we want to set value. So "GetParameter" method selects that parameter "Destination" and then with the help of Value property we set the value "New York" to "Destination" parameter.

      And notice that there is no "SetParameter" method provided by QTP in data tables.


      Hope this helps you to learn. Keep Learning... :)

      Delete
  6. Goood Article .

    It really helped me in learning concepts of Data Table in QTP

    Keep up the good work and keep writing

    ReplyDelete
  7. Too good.

    Thank you for such a nice article.

    ReplyDelete
  8. really a good one....covering almost all data table functions......thanks :)

    ReplyDelete
  9. Great article, totally what I wanted to find.

    ReplyDelete

Your kind comments are always welcome. Put your comments here if you have problems in this post or you want me to add something else in this post.