site
stats
By Anish Pillai Anish Pillai Posted under QTP Concepts | QTP Framework

Designing Data Driven Framework in QTP – Part 3

1 Flares Twitter 0 Facebook 0 Google+ 1 LinkedIn 0 Email -- 1 Flares ×

If you have seen the previous articles on Data Driven Framework, we had discussed that there are 3 common ways in which you can store your data in excel sheets or QTP data table and then use it in a data driven framework. Of these 3 methods, we have already covered the first 2 methods in –

  • QTP Data Driven Framework Part 1, where the test data was stored in QTP Data Table and we wrote the code that fetched the data directly from the data table.
  • QTP Data Driven Framework Part 2, where the data was stored in excel sheets and we wrote the code where QTP first copied the data from excel sheet to QTP Data Table and then retrieved the data from the Data Table.


UPDATES

1) You are free to download and play around with the code used for this framework. The download link is available at the end of the article.

2) Just like this, we have written more articles on various other QTP Frameworks. If you wish to have a look at those, please visit QTP Framework main page. The bottom of the page contains links to other QTP Frameworks.



In this final part of the articles on QTP Data Driven Framework, we will cover the 3rd method of storing and retrieving data, where the data would be saved in excel sheet and would be directly fetched from excel sheet only (as opposed to the previous method where the data was first copied from excel sheet to QTP Data Table and then fetched from the data table).

This article will be created on the same lines as the previous article on Data Driven Framework. So let’s directly start with what all will be covered in this article.

In this article we will –

  • 1) create 3 Test Scripts that will work on GMail and Google.
  • 2) create a single excel workbook which will have only one excel sheet inside it. All 3 test cases will be added as 3 different rows in the same excel sheet.


Identifying Test Cases to be automated for this Framework

As part of this article, we will be automating the same set of test cases that we had automated in the previous article. These test cases are –

  • 1) TC_01_GmailInbox: In this test script, we will login to GMail and then will find out the number of unread emails in Inbox.
  • 2) TC_02_GMailLabel: Here, we will again login to GMail and then create a new Label.
  • 3) TC_03_GoogleSearch: In this test case, we will open Google home page, then search for a string and will find out the number of results returned for that particular search.


Identifying the format of Excel Sheet

For the sample framework in this article, we will create an excel workbook and then add only a single sheet to it. All the test cases will be added as separate rows in the same excel sheet. Since we have 3 test cases, we will be having 3 rows of data in the excel sheet (without including the header).

Identifying how to fetch data from Excel Sheet

In this framework, the data will be fetched directly from the excel sheet only. That is, we would be writing code that would open the excel sheet and then fetch the data based on the row and column. This would be accomplished using Microsoft Excel Object Model.

How QTP fetches Data from Data Sheet

Once you have identified the test cases, the data sheet format and the method using which you will fetch the test data, you can start creating your test scripts.

Step by Step method to Create the First Test Case in the Framework

To explain the entire process in a detailed step by step manner, we will take up the Login to GMail portion of the first test case and then explain it in detail. This would help you become familiar and comfortable with the process. Once this is done, you can yourself create the remaining test scripts without any issues.

Step 1: Open QTP, create a new Test Case and save it at any desired location.

Step 2: Since this is the first test case of the framework, we will write the script for GMail login and hard-code the data in the script itself. Then we will run the code to verify that the functionality is working fine. Once we have verified that the functionality is working fine, we can then write the code that will fetch data from the excel sheet.

Step 3: Create an Object Repository and associate it with your test case. Add the required objects for GMail login and then write the code. The code should look something like this –

'Open GMail
SystemUtil.Run "iexplore.exe", "http://www.gmail.com"
'Page Sync
Browser("Gmail").Page("Gmail").Sync

'Login to Gmail
Browser("Gmail").Page("Gmail").WebEdit("UserName").Set "valid gmail login id"
Browser("Gmail").Page("Gmail").WebEdit("Password").Set "valid gmail password"
Browser("Gmail").Page("Gmail").WebButton("SignIn").Click

'Page Sync
Browser("Inbox").Page("Inbox").Sync

Run the above code and see if it is working fine.

Step 4: Now you have to create the data sheet to replace the hard-coded data with your custom function that will fetch the data from the excel sheet.

To do so, create a new excel workbook and save it as DataSheet.xls inside your framework folder. (you can give any meaningful name to the Workbook)

You would notice that the excel workbook already has 3 excel sheets – Sheet1, Sheet2 and Sheet3, added to it. Since we require only one data sheet in the workbook, delete sheets Sheet2 and Sheet3 from the excel workbook.

Now rename Sheet1 to some meaningful name (say TestData). Your excel workbook will look something like this.

Data Sheet Format for Data Driven Framework

Step 5: Once you have created the data sheet, the next step will be to add data to this excel sheet. Add the data in the excel sheet as shown in the below image. Please note that the values in Column 1 should be same as the test case name, so that QTP can pick up the data correctly based on Test Case name.

Adding Test Data in Excel Sheet

Step 6: Now you will need to write the code that will fetch the data from this excel sheet. The logic for fetching the data goes like this.

  • i. QTP opens the excel sheet.
  • ii. QTP loops through the first column to read the test case name from each cell.
  • iii. QTP compares the test case name (from the excel) with the current test case name (which is currently opened in QTP).
  • iv. Once a match is found on the test case name, QTP the loops horizontally to read the data from the particular row where the test case name matched.

Fetching Data from Data Sheet

Check the below code that will fetch data based on Test Case name and Column Name.

Function fnGetDataFromExcel(strColumnName)

Dim iReqdCol
fnGetDataFromExcel = "<empty>"

'Open the Excel
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open(sExcelPath)
Set oSheet = oExcel.Sheets("TestData")

'Find the total number of rows in the excel
iRows = oSheet.UsedRange.Rows.Count
iCols = oSheet.UsedRange.Columns.Count

'Find the column number from where data needs to be fetched
For iC = 2 to iCols 'Loop started from 2 because the first column is Test Case name
If oSheet.Cells(1, iC).Value = strColumnName Then
iReqdCol = iC
Exit For
End If

Next

'Loop through the rows to find the test case name
For i=2 to iRows 'Loop started from 2 because 1st row is header
If oSheet.Cells(i, 1).Value = sCurrentTestCase Then
fnGetDataFromExcel = oSheet.Cells(i, iReqdCol)
End If
Next

'Close the Excel
oExcel.Quit()
Set oExcel = Nothing
Set oWorkbook = Nothing
Set oSheet = Nothing

End Function


Step 7: As done in the previous article, you can create a custom set function that will first fetch the value from the excel sheet and then it will populate the fetched value in required text boxes in the application. The code for the custom function is shown below.

Function fnSetValue(objControl, strColumnName)

  Dim sReqdValue

  'Get value from Excel Sheet
  sReqdValue = fnGetDataFromExcel(strColumnName)

  objControl.Set sReqdValue

End Function

Since both these functions (shown in Step 6 and Step 7) are functions that will be reused across all the 3 test cases, you would need to store them in an external function library and associate the function library with all the 3 test cases. The final code (for the login portion of the script) should look something like this.

'Get the Test Case Name that will be used in fnGetDataFromExcel function
sTestCaseName = Environment.Value("TestName")

'Open GMail
sUrl = fnGetDataFromExcel("URL")
SystemUtil.Run "iexplore.exe", sUrl
'Page Sync
Browser("Gmail").Page("Gmail").Sync

'Login to Gmail
Browser("Gmail").Page("Gmail").WebEdit("UserName").fnSetValue "UserID"
Browser("Gmail").Page("Gmail").WebEdit("Password").fnSetValue "Password"
Browser("Gmail").Page("Gmail").WebButton("SignIn").Click

'Page Sync
Browser("Inbox").Page("Inbox").Sync

Step 10: The above code covers the login portion of the test case where the values are fetched from the excel sheet. Run this code and check that it is working fine without any errors. Once this is done, you can use the same steps above to complete this test case and then work on the remaining 2 test cases also.

While adding data for the remaining two test cases, all that you have to do is to add some additional columns (if required) and add the data in different rows. Once this is done, the custom functions will pick up the required values based on the test case name and the column names. The below figure shows how the excel sheet might look like once you add data for all the test cases in the excel sheet.

Data Sheet Format in Data Driven Framework

This was all about the different common ways in which you can use the data driven framework in QTP. If you are beginner in QTP, I would recommend that you go through all the articles on Data Driven Framework from QTP Framework Main Page link so that you have a clear idea about the different ways in which you can use the data driven framework. This would help you understand the entire process and thus you would be in a better position to choose appropriate framework based on your needs.

You can also download the code for this framework from the below download link. If you have any feedback or comments about this article, do let us know about it using the comments section.



If you enjoyed this article, you can join our blog to get new articles delivered directly in your inbox.

Visit QTP Frameworks Main Page for more articles on QTP Frameworks. You can also visit our QTP Tutorials page for more QTP Tutorials.

1 Flares Twitter 0 Facebook 0 Google+ 1 LinkedIn 0 Email -- 1 Flares ×
  • Shalini

    Thanks for sharing your knowledge.

    Your mode of explaining things is superb!!!

    Please continue your work. Its a great help for people like us.

    We learn a lot from your articles.

    • Anish10110

      Thank you Shalini for the feedback.. 🙂

  • Nikhil

    Bro these are the best available tutorial rt now

  • Nikhil

    Superb bro tutorial is really grt

    • Anish10110

      Thank you Nikhil… 🙂

  • Girish

    Awesome, You are doing great Job

    • Anish10110

      Thank You Girish.. 🙂

  • Pingback: QTP Framework Tutorials - Framework Types, Examples & Code Snippets - Automation Repository()

  • Swetha

    Hello Anish,

    I tried using this code to access data from excel and login to gmail. But I was getting the below error.
    ActiveX component can't create object: 'Excel.Application'

    Please let me know how to overcome this.

    • Anish10110

      This has got something to do with some Excel or MS Office issue. Nothing related to code I guess. Can you try and see if Excel and ActiveX components are installed properly.

  • Abhinov

    hi,
    fnSetValue has 2 arguments whereas we are passing only one..is it valid plz explain..also u have passed the value as fnSetValue "UserID"..is it valid..or there is any syntax error…Plz help

  • Abhinov

    Hi Anish,
    Its not working for me..
    The sUrl variable has no value …hence the browser opens but no address…:(
    ALso,
    For i=2 to iRows 'Loop started from 2 because 1st row is header
    If oSheet.Cells(i, 1).Value = sCurrentTestCase Then
    fnGetDataFromExcel = oSheet.Cells(i, iReqdCol)
    End If
    Next
    for this part of program,could u plz elaborate 3rd last line…what will be assigned to fnGetDATAFrom Excel

    • Anish10110

      Are you using the sample framework that I have provided? Or are you trying the code on your own? Because there might some issue with function library mapping.

      Also, the 3rd line that you mentioned, that is a way in which you want to return a value from a function. Suppose you have a function called ABC(). Now suppose you want to return some value from this function, you will have to use the statememt..

      <function name> = <message>
      So for function ABC(), this code will be –> ABC = "some message";

      And to retrieve this value outside the function, you have to use something like this..
      value = ABC();
      so value will contain "some message";

      Regarding the statement…. oSheet.Cells(i, iReqdCol) ….. this is returning the value of the cell based on the row and column number.. i is row, and iReqdCol is the column.

  • Abhinov

    Hi Anish,
    This fnSetValue is not working for me..could u plz help me…
    Error
    "Object doesn't support this property or method: 'Browser(…).Page(…).WebEdit(…).fnSetValue'

    Line (12): "Browser("name:=Gmail: Email from Google").Page("title:=Gmail: Email from Google").WebEdit("name:=Email").fnSetValue "UserID"".

    • Anish10110

      Abhinov,

      Send the error screenshot to my email id (anish@automationrepository.com). I will check the issue and will reply back to you.

  • Abhinov

    hi,
    Regarding the statement…. oSheet.Cells(i, iReqdCol) ….. this is returning the value of the cell based on the row and column number.. i is row, and iReqdCol is the column.

    will this return the value of cell or we should use it with "oSheet.Cells(i, iReqdCol).value"

    • Anish10110

      Both the methods should work fine I guess.. When I was trying this code, I didn't find any issues with it. So I used it this way only. Have you tried out both the methods??

  • Eswari

    If the username column contains anyh special characters or numbers means it should give an error msg ,how we will handle this.
    Pls clear my doubht

    • Anish10110

      Is it some negative value you want to verify or a case where someone mistakenly enters wrong value in the excel sheet?

      • Eswari

        Thanks for your reply
        In Datadriven testing we will give test data in an excel sheet isn't it ,in that if we give specialcharacters or numbers to a particular field in the application it has to throw an error .

        In datadriven testing we check for both valid and invalid data isn't.

        • Anish10110

          Yes, you can check for both valid and invalid data in this framework. Only thing is that you should handle the error in the code. For example, suppose that the application throws an error when you give some special characters. In this case, you can add in if statement in your code, which can check if the error message comes or not. This way you can give both valid and invalid data and check the functionality.

  • sachin

    nice post 🙂 way of explaining is superb 🙂

    • Anish10110

      Thanks Sachin.. 🙂

  • Deepika

    Hi Anish ,
    I have a doubt in Step 6 :
    fnGetDataFromExcel = "<empty>"
    Why have you used this statement , i tried running code without this also and it works fine.Any special reason for using this line of code?
    Thanks ,
    Deepika

    • Anish10110

      Hi Deepika,

      You mentioned in right. The code works without using fnGetDataFromExcel = "<empty>" also. The only reason I have used it is because it is a coding practice that I follow. Thats all.

  • kishore

    This is excellent explanation. Appreciate your efforts and keep up the good work!!

  • kishore

    Hi,

    I have a question about a scenario where I need to use 2 records of test data for one test case.
    One record for positive test case and one for negative scenario. Can you explain how to add 2 rows of data for one test case in the above scenario? Thanks in advance..

    • Anish10110

      There are many ways of doing it. The most common would be to

      >> Add 1 more row to the data sheet, where your test case name will remain the same but the data will change (this can be the negative test case data)

      >> Then in your script, you have to add a for loop in such a way that it doesn't stop after finding out the first row of data. So what happens is script will find the first row, run the script based upon the data. Then it will go further and find the 2nd row. So the same test will run for different set of data. This generic code can be extended to include many different sets of data.

      Only condition is that the flow should remain same (or similar).

  • Pingback: complete real time scripts of qtp « TESTING HUB()

  • AB

    Hi Anish,

    Can you please explain what to do next in the below situation:

    We are using an external excel sheet using excel object model ;the code consists of multiple actions now not sure how to implement that the whole flow should start for the next row in the excel automatically.

    Thanks,
    AB

  • bhargavi

    getting unknown error for fngetdata = ows.cells(i,reqcol)..please suggest me

  • Shrikumar

    Hi Anish,

    Where did we get the Value sTestCaseName in function library?? the function is returning null value

  • Sanjukta

    Hi Anish, Excellent articles. But in both part 2 and 3 I am not able to follow

    sTestCaseName = Environment.Value("TestName")
    What is "TestName"?What value is exactly being passed to sTestCaseName?My custom function as a result is not setting any value (because the get value from excel/datatable function not working)

    • Naveen

      TestName is an environment variable it gives Current executing test case Name.you can find built in here File->TestSettings->Environment

  • Sanjukta

    Hi Anish,
    Using the above scenario how do I parameterize the radio buttons (First, Business and Economy) from the excel sheet (with column name "Class"). It seems these are three separate radio buttons and not radio group.Could you help out?

  • Naveen

    Thanks for providing step by step procedure to create Data Driven Frame work

  • Raghu Raam

    I am NOt able to understand the below line of code

    Function fnGetDataFromExcel(strColumnName)

    Step 27

    CODE: If oSheet.Cells(i, 1).Value = sCurrentTestCase

    What is here "sCurrentTestCase"? Which value we need to pass here. Is it hardcoded ?I didnot find any value (or) variable .sCurrentTestCase is only available at that location.

    Pleae help me out.
    thanks in advance

    • Shailendra

      Hi Raghu,

      In code pasted above, under function "fnGetDataFromExcel"

      it's a typo mistake. It should be sTestCaseName not sCurrentTestCase

  • Shailendra

    Hi Anish,

    Could you please explain what is the use of variable "sCurrentTestCase".

    Where we are passing & using this variable value?

    Thanks
    Shailendra

    • Shailendra

      In code pasted above, under function "fnGetDataFromExcel"

      it's a typo mistake. It should be sTestCaseName not sCurrentTestCase

  • Pragatheesh

    Hi Folks,

    I would like to create an automation for below scenario. Kindly check and let me know if it is feasible using QTP.

    We are working on a support model and we used to do internal audits. We used to take incidents report for each team member respective to status of incidents [ Open, In progress, Closed, Pending response ]. We are taking report by loading our ticketing tool website and manually search every user and setting ticket status filters and taking the count.

    For example , at the end of day , report spreadsheet will have data like below.

    Name Open In progress Closed Pending Response

    Ravi 3 4 0 5
    Kumar 2 2 2 0

    Have to automate below things.

    1.Load the website
    2. In Loop
    Set the filters for every team member and Get the count for each status automatically
    store the counts in excel sheet

    Is it possible using QTP?

    Thanks,
    Pragatheesh.

  • Anshika

    Hi Anish,

    If i am passing data which contains zero values some error occurs and environment variable is not created eg. if i pass 20,0,23 as value in one of the column the code is unable to pick up values from any column.
    Can you explain why?

  • Jessy

    Anish,

    Urgently need your help. I am using Excel 2010 and QTP 12 for this example. while iam accessing EXCEL it throws me an error at this point

    sUrl = fnGetDataFromExcel("URL")

    General Error – sUrl = fnGetDataFromExcel("URL")

    Please help me.

  • Jessy

    Anish, I solved it myself. I was using fnGetDatafromExcel(objControl, StrColumnName) instead of fnGetDatafromExcel(StrColumnName). Writing it here as it could be helpful for some others. We don't need to bind any control to fnGetDatafromExcel as it is not needed here.

  • Jessy

    Small Correction –
    sCurrentTestCase is STestCaseName and Also write
    STestCaseName = Environment.Value("TestName") in the function library.
    you can get the value of STestCaseName that is used in the fun lib.
    Instead of oSheet.Cells(i,iReqdCol) use oSheet.Cells(i, iReqdCol).Value
    Also
    For i=2 to iRows 'Loop started from 2 because 1st row is header
    If oSheet.Cells(i, 1).Value = sCurrentTestCase Then
    fnGetDataFromExcel = oSheet.Cells(i, iReqdCol)
    End If
    Next
    Also make sure in this line below that you use same columnname "UserID" as you used in the Excel sheet.
    Browser("Gmail").Page("Gmail").WebEdit("UserName").fnSetValue "UserID"

  • Guest07

    In fnGetDataFromExcel(strColumnName), where is sExcelPath coming from? Also, in fnGetDataFromExcel(strColumnName), sCurrentTestCase is used but, in the script calling fnGetDataFromExcel(strColumnName), sTestCaseNam is used:

    'Get the Test Case Name that will be used in fnGetDataFromExcel function

    sTestCaseName = Environment.Value("TestName")

1 Flares Twitter 0 Facebook 0 Google+ 1 LinkedIn 0 Email -- 1 Flares ×