Forum Discussion

JMo_QA's avatar
JMo_QA
New Contributor
7 years ago

Iterative Excel Data Based Test Run

I'm sure I'm not the first to ask this question however I don't see an answer here. I thought I had done this previously when working with Test Complete but for the life of me cannot seem to get it going now.

 

Our tests create data that can only be created once i.e. users, products, documents etc.  Subsequent tests with the same data will fail.  We can generate hundreds of rows of test data and specify on the data loop only to run row 1 of the test data. The next iteration, we have to change the keyword test data loop to use row 2 and repeat for every execution. 

 

Is there no way to set this up via the keyword to move to the next iteration automatically in the excel list?

  • The answer may not be what you think.

     

    We, too, have to make sure that, each time we run a test case, we use "unique" data.  Rather than relying on an external file for determining the unique data, we utilize one of two methods currently.

     

    1) We have an SQL data table with key/value pairs that we use for generating unique ID's for certain functions.  An SQL query to the data table retrieves the current value of a given key/value pair and then increments it.  Each time we run a test, this generates a new unique ID that we can then use to create other unique data.

     

    2) We use something like aqConvert.DateTimeToFormatStr(aqDateTime.Now(), '%Y%m%d%H%M') to generate a unique 12 digit number.  This number is unique every minute.  We then use this to create other data within our test case.

     

    Now... both points I say, "we use this to create other unique data"... basically, we take whatever we generated as a unique number and append it to something else.  For example, for a person's first name that we want to be unique every time, we use a "Set Variable Value" in a keyword test and use a code expression of 

     

    'First' + KeywordTests.MyTest.Variables.uniqueID

    and this generates a unique first name each time we run the tests.  I find this to be a LOT easier than writing some code to make sure we always use the "next" row in an excel spreadsheet.

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor

    The answer may not be what you think.

     

    We, too, have to make sure that, each time we run a test case, we use "unique" data.  Rather than relying on an external file for determining the unique data, we utilize one of two methods currently.

     

    1) We have an SQL data table with key/value pairs that we use for generating unique ID's for certain functions.  An SQL query to the data table retrieves the current value of a given key/value pair and then increments it.  Each time we run a test, this generates a new unique ID that we can then use to create other unique data.

     

    2) We use something like aqConvert.DateTimeToFormatStr(aqDateTime.Now(), '%Y%m%d%H%M') to generate a unique 12 digit number.  This number is unique every minute.  We then use this to create other data within our test case.

     

    Now... both points I say, "we use this to create other unique data"... basically, we take whatever we generated as a unique number and append it to something else.  For example, for a person's first name that we want to be unique every time, we use a "Set Variable Value" in a keyword test and use a code expression of 

     

    'First' + KeywordTests.MyTest.Variables.uniqueID

    and this generates a unique first name each time we run the tests.  I find this to be a LOT easier than writing some code to make sure we always use the "next" row in an excel spreadsheet.

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor

      FYI, the built in Excel "looping" that is part of keyword tests is meant to loop through the excel source all at once.  So, if you have an excel sheet that has 200 rows and you build such a loop in a keyword test, everytime you run the keyword test, it will run all 200 rows, once through each cycle through the loop.  

       

      To use Excel as a data source as you are asking requires using the Excel.Application COM object as a data source, reading and writing both to the file... and this is best done using script code.  There have been folks up here who have managed to do this but, honestly, in my opinion, it seems to be a lot of work simply to create unique data when using some sort of unique identifier scheme will do the same without needing to go that complicated.  The datetime method is VERY easily implemented in keyword tests without needing to utilize any script code.

       

      If you're still interested in using Excel as you've described, start here.

      https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/excel/working-via-com.html

    • JMo_QA's avatar
      JMo_QA
      New Contributor

      Thank you, Tristan, this worked perfectly for what I need!