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

Selecting Data Sheet format for a Data Driven Framework in QTP

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

In the previous article on Data Driven Framework using QTP Data Tables, you saw how you can add data in QTP Data Table and then write a script that can fetch data from this Data Table. This is the most basic method and also the easiest to implement. But the issue with this approach is that though it is easy to use and implement, it is not a suitable method when the number of test scripts is large.

So whenever you have a large number of test cases or you have large amount of data that needs to be validated, it is always advised that you use some external source (which would be Microsoft Excel in most of the cases) to store your data. But identifying a data source is just the beginning of the story. The most important part that you have to take care is how your data should be organized in the external data source.

There are many ways in which you can save or group your data in the excel sheets. Consider a scenario where you have, say 20 different test cases to be automated. Let us see what all different ways are there to save the data for all these test cases in excel sheets.

  • 1) You can use only one excel sheet where you can save data for all the test cases.
  • 2) You can have one excel workbook and 20 sheets in it, thus each test case will have a separate sheet for itself.
  • 3) You can also have 20 different workbooks (each with one sheet) for the test cases, i.e. One workbook for each test case.
  • 4) If you are dividing your test cases into multiple business flows, then you can have a separate sheet for each business flow.
  • 5) Other than these, there can be many others ways that you can use to save your data in the excel sheets.

As you have seen above, there are many different ways you can use to save data in the excel sheets. The important question that arises here is – which of these methods should you use in your Data Driven Framework? Please note that this is one of the most important things that need to be done as part of a Data Driven Framework and this format should be identified before you start writing your test scripts.

This is because your entire code and framework flow will be decided based on the format or structure of the data sheets. And if at a later point (when you have scripted quite a number of test cases) you come to know that the data sheet format you have used is not that efficient, you might need to do considerable amount of rework in the scripts and the data sheets to make the necessary corrections.

To help you with this identification process, we will cover some of the most common data sheet formats that are used in data driven frameworks. We will also see the advantages and disadvantages of each of these formats and will also try to identify the scenarios where these data sheet formats/structures can be used.

Format 1: Adding all the test cases in a single excel sheet

Here, you will have only 1 excel sheet (or workbook) and the data for all the test cases will be saved in that single excel sheet only. Here each row in the data sheet will cater to 1 test case. So if you have 10 test cases, you would be using 10 different rows in the excel sheet (i.e. 1 row for 1 test case).

Where can this type of data sheet be used? You can use this data sheet format in the following scenarios –

  • 1) In this type of format, all the test cases are saved in the same sheet, hence they will be sharing the same number of columns. There would certainly be cases where a Column used by one test case will not be used by some other test case. Hence this type of data sheet is more suited where the test cases have similar sort of flows so that most of the columns are used by most of the test cases.
  • 2) Since there will always be many cells that will be left blank by one or the other test case, this data sheet format will be more effective and manageable when you have fewer number of test cases and lesser number of data for each of these test case. Both these points will ensure that the number of columns stays within manageable limits.

Sample Scenario where this data sheet format can be used. Refer the test cases in the below table and the data requirements for each of these test cases.

Test Case ID Purpose Data Requirements
TC_Gmail Login to Gmail and open the first email from inbox. URL, User ID, Password
TC_YahooMail Login to Yahoo Mail and open the mail whose subject matches the one given in data sheet. URL, User ID, Password, Email Subject
TC_Facebook Login to Facebook and open a friend’s wall. The friend’s name should be taken from data sheet. URL User ID, Password, Friend’s Name

The above 3 test cases have –

  • a) Somewhat similar kind of flow (because of Login component).
  • b) Have few data requirements and hence fewer columns in the data sheet.

So these test cases can use shared data sheet approach. The below figure shows the sample data sheet for the above mentioned test cases.

Data Sheet Structure in Data Driven Framework


  • 1) You can change all the test data from a single sheet only.
  • 2) The coding portion, where you fetch data from the data sheet is relatively easier to write due to the single sheet structure.


  • 1) This type of data sheet format is efficient only when the number of test cases and the associated columns are less.
  • 2) Since the data sheet contains columns for all the test cases, identifying which column is associated with which test case is somewhat difficult and this may sometimes result in errors while filling the data.

Format 2: Having separate Excel Sheets for each Test Case

In this format, you can use separate excel sheets for each test case. For example, if you have 10 different test cases, you will have 10 different excel sheets.

You can add all these excel sheets in the same workbook or you can have separate workbooks (each with one sheet) for each test cases.

TIP: One best practice that you can follow here is to keep the same name for the excel sheet and the test case. Example: If your test case name is TC_01, then you can have the data sheet for this test case named as TC_01. This is advantageous because of the following 2 reasons –

  • 1) You can easily identify the data sheet for a particular test case using its name.
  • 2) You can use Environment.Value(“TestName”) property to find the test case name and thus you can write a generic function to fetch data based on the test case name.

Where can this data sheet format be used? This is the most common data sheet format that you can use in the following scenarios –

  • 1) You can use this data sheet format when you have test cases with entirely different flows (and hence different columns that would not be used much by other test cases).
  • 2) This format is also suitable when you have large number of test cases or large number of data requirements (columns) for the test cases.
  • 3) This data sheet format is also well suited for scenarios where you have to run each test case multiple number of times. In each excel sheet, you can add as many number of rows as the times your test case needs to run. And then you can use a for loop to loop through all the rows in the data sheet and run that particular test case that many number of times.

Sample Scenario Consider the below table where you have different test cases, their purpose, data requirements and the number of times each of these test cases need to be run.

TestCase ID Purpose Data Needed Iterations
TC_Google Open Google, search for a string (value taken from datasheet) and find the number of results URL, Search String 3
TC_Facebook Check Facebook’s Login functionality with different input values URL, UserName, Password 5
TC_Gmail Login to Gmail and find out the number of new emails in Inbox URL, UserName, Password 1

For the above test cases, we can create the data sheets as shown in the below image.

Data Sheet format in Data Driven Framework


  • 1) Since each test case is added in a separate sheet, it is very easy to scale up this framework to add more number of test cases. To add more test cases, all we have to do is add more sheets to the existing workbooks.
  • 2) Since each test case has a separate data sheet, it becomes very easy to run any test case any number of times. For this we just need to add multiple rows in the same data sheet. Running 1 test case multiple times doesn’t affect the execution of other test cases in any way.


  • 1) This framework is not very efficient when you have similar sort of flows in different test cases. For example, let us consider that you have 10 test cases where you have to Login to GMail once and then do some other action. Now suppose that you have used the same UserID and Password for each of these test cases. Let’s say that you have changed the password for your user ID. Now in order to change the password for all the test cases, you would have to make the changes in all the 10 test data sheets.

Format 3: Creating separate excel sheets for each Business Flow

In this case, you first divide your test cases into multiple business flows (reusable and non-reusable) and then you create an excel sheet for each of these business flows. Check the below image which shows few test cases and the business flows for each of these test cases.

Data Sheet Format for Data Driven Framework

Now you can create the data sheets for each of the business flows. There would be some business flows that would not need any data (CountEmails and Logout in this case). For those business flows, you need not create any data sheets. You can keep the name of the data sheet same as the business flow. This would help you identify the data sheets without any issues. Let us see how the data sheets would look like for this scenario.

Data Sheet format for Data Driven Framework

Where can this data sheet format be used? You can use this type of data sheet structure when you use pure modular framework or modular based hybrid framework. More the scope of reuse of business flows, the better it would be for this data sheet design.


  • 1) In case of reusable flows, all the test data for different test cases is available at a single place. So its easy to change the data in this case. For example, if you want to change the password for your test cases, you can easily do it as all the login details will be available in a single data sheet.


  • 1) Since the data sheets are created on the basis of business flows, if you want to change the complete data for a test case, you have have to do it in multiple data sheets because your test case will most probably be using multiple business flows.
  • 2) Also, keeping a track of all the data sheets associated with a test case becomes bit difficult sometimes.

This was all about the common data sheet formats that you can use in Data Driven Framework. Please note that each of these formats has its own advantages and disadvantages and hence none of them is superior over the other. Based on your requirements and analysis of test cases, you should be choosing a data sheet structure that would help you get the maximum benefit.

Let us know what you think about this article. If you feel that there is any other data sheet format that can be included here, then please 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.

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- 0 Flares ×
  • Pingback: Designing Data Driven Framework in QTP – Part 2 - Automation Repository - Automation Repository()

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

  • Anish, its been a pleasure reading your articles. They are very explanatory. Thanks for contributing.

    • Anish10110

      Thank you.. 🙂 Keep visiting.. 🙂

  • Ashwini


    The explanations are crystal clear and perfect, withh all flows neatly expalined!

    Keep Rocking!

    • Anish10110

      Thank You.. 🙂

  • nai

    thank you so much, learning a lot through your articles.

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

  • Venkatesh

    You are really making tomorrows proffessionals 🙂

  • Vinay

    These are very nice site for learning QTP for Beginners. Will recommend to others to refer this site.

    If we have any doubt or stuck with issues, how to post it or contact for a solution.

    • Anish10110

      Hi Vinay,

      You can either email me or post your queries in the comments section of the related article.

  • Anila

    This is a good site to learn frame work.Thanks for the efforts

  • Seth

    Excellent article! But could you include something about tests that have dependencies on other tests. For example: Test 1 requires dataTable 1, Test 2 must run after Test 1 and requires dataTable 2, How to get Test 1 and Test 2 to run together in a paired iterative manner. Meaning Run Test 1 and Test 2 for row #1 in each of their respective data-tables, then run Test 1 & Test 2 for row #2 in each of their respective rows, and so on?

  • Naresh

    i am getting some problem please send me the solution
    problem…is…i wrote the data in some excel file and saved it ..i am copying that excel data into internal excel of QTP data is comming to internal excel file ..i am using datatable.importsheet "filepath","externalexcelsheetname",1…..but its not working can u send me the answer please

  • Guest07

    The article is perfect. A couple of questions. In the examples for Format 1,2, what would be the purpose of testing of 3 different and unrelated web applications (google, gmail, and facebook) in the same test run? In the example for Format 3, what would be the purpose of having 3 different sets of Username and Password for 3 different test cases? If all we need is to login, would not it be better to use the same credentials and, if this is a case, should not be those credentials be stored just in one place (just in 2 cells, not in 6 cells like it's shown in the example) so they can be modified easily?

  • amey24

    GOD of QTP m/m/

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