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 g...
  • tristaanogre's avatar
    tristaanogre
    7 years ago

    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
7 years ago

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
    7 years ago

    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
    7 years ago

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