Forum Discussion

rnxn's avatar
rnxn
Occasional Contributor
3 years ago

Return DOB for specified customer from Excel workbook (similar to Excel VLOOKUP)

Hello, 

 

I'm running SOAP requests using customer information from a test customer database. 

Each customer's DOB changes on a daily basis so that they remain the exact same age down to the day - I have an Excel file with the test customer names and DOB (as calculated to reflect the age in the test customer database).

 

Is there a way I can use the Excel file as a data source, and have ReadyAPI look up the test customer forename and surname I use in the request in order to return the correct DOB as calculated that day? Similar to a VLOOKUP in Excel?

 

If not, do you have any suggestions as to other ways I may be able to tackle this issue?

 

Thanks in advance!

  • Hi,

     

    Re iterating over a spreadsheet to find a row.

     

    I'm using this table in my Excel lookup spreadsheet.

    CustomerId Customer Name Surname DOB
    1 John Lennon 9/10/1940
    2 Paul McCartney 18/6/1942
    3 George Harrison 25/2/1943
    4 Ringo Starkey 7/7/1940

     

    To do the lookup, I wrote some Groovy script.  I used this page to help me read the Excel spreadsheet... https://www.anilvij.com/how-to-read-data-from-excel-using-groovy-soapui/ in order to write my Groovy script.

     

    Here's the Groovy script you can use as an example.  Note : This works for xls files, not xlsx.

    import com.eviware.soapui.support.XmlHolder;
    import jxl.*;
    import jxl.write.*;
    
    // For the sake of example, these are the values returned from your db and these are what we will look for in Excel.
    def forename = "George";
    def surname = "Harrison";
    def dob = "25/2/1943";
    
    
    Workbook workbook1 = Workbook.getWorkbook(new File("C:\\temp\\CustomerData.xls")) //file containing the data
    
    Sheet sheet1 = workbook1.getSheet(0) //Index 0 will read the first sheet from the workbook, you can also specify the sheet name with "Sheet1"
    //Sheet sheet1 = workbook1.getSheet("Sheet1")
    
    size= sheet1.getRows().toInteger() //get the number of rows, each row is a data set
    
    log.info("Number of rows is ${size}.");
    
    // We will use these in our loop.
    def found = false;
    def row = 0;
    
    log.info("Looking for ${forename} ${surname}");
    
    for (int i = 1; i < size; i++) {
    
    	def id = sheet1.getCell(0,i).getContents(); // getCell(column,row)  getContents() teases out the value.
    	def sourceForename = sheet1.getCell(1,i).getContents();
    	def sourceSurname = sheet1.getCell(2,i) .getContents();
    
    	log.info("${id} - ${sourceForename} ${sourceSurname}");
    
    	if((forename.equals(sourceForename)) && (surname.equals(sourceSurname))) {
    		// We've found a match.  Let's note the row and break out of loop to save time.
    		found = true;
    		row = i;
    		break;
    	}
    }
    
    if(found){
    	// Compare DOB
    	// You don't have to use assert, you could use a boolean check or whatever suits.
    	assert dob == sheet1.getCell(3,row).getContents();
    	log.info("DOB is a Match!");
    } else {
    	log.info("Could not find ${sourceForename} ${sourceSurname} in the  Excel lookup sheet.");
    }
    
    workbook1.close() //close the file
    
    

     

  • richie's avatar
    richie
    Community Hero
    Hey rnxn,

    Im struggling to understand and visualise exactly what you need

    You state "Each customer's DOB changes on a daily basis so that they remain the exact same age down to the day".
    Can you clarify what you mean by each customers dob changes on a daily basis? Im dont understand what you mean by this....someones DOB remains constant.
    Do you mean the customers "age" changes dynamically relative to the current date? If "yes"

    Sourcing a datavalue from a source file (to populate a payload parameter) is easy using the Datasource (filetype) test step.

    If you can just explain about the dob/age thing so i dont make false assumptions then we'll be able to sort you out.

    Cheers,

    Rich
    • rnxn's avatar
      rnxn
      Occasional Contributor

      Hi Rich, 

       

      Thanks so much for responding to my query, and apologies that my explanation of what I'm trying to do wasn't so clear.

       

      Basically the test customer database was created in 2017, and the DOB of the customers in the DB is dynamic which keeps the age static. So the Excel doc we have works out their age in days at the time the test pack was produced, and in another column deducts that value from "TODAY" to produce a new date of birth which maintains the customer's age. So if the customer was 10,000 days old when the test pack was created, the date in this column will mean they are always 10,000 days old (this reflects the DOB in the database). Hope that makes more sense. 

       

      So the value in the DOB field is dynamic, and I want to use this Excel doc as a data source. My aim is that I will be able to choose a test customer name from the test pack at random, and using the values in <Forename></Forename> and <Surname></Surname>, ReadyAPI will look up the DOB from the corresponding row in the Data Source and pull that value in the DOB column through into the <DOB></DOB> field. 

       

      Thanks

       

      Rachael

      • richie's avatar
        richie
        Community Hero
        Hey Rachael,


        Ok...i gotcha....so my question: how important is this random thing you want? Can you get away with "not random"?

        Reason i ask, its quite simple to source values from a file for data driven tests (to populate certain parms in either your URI or your payload), however if you want random, youll need groovyscript to 1. Pick a random row in the source file to run the test.

        Also, how many tests are you thinking of executing at any one time? Is it just a single testcase each time or do you want separate test cases executed in a loop?
        If its a single test case each time, i think even i could do the groovy for that (and my groovy sucks), if you want to loop tests (so repeat the testcase X number of times) youll need to add code to iterate through rows in the spreadsheet (i think i can probably do this in groovy too....ive done it a number of times, but its been a while and its like everything else....if you dont use it, you lose it!).

        TNeuschwanger / ChrisAdams / nmrao are the groovy gods on this thing, so you might want them helping you rather than me.

        So! Back to my original question....how important is the random thing? If its not essential i can create a sample project for you in about 15mins if you provide sample .xls and uri and payload (btw...sharing this stuff is only a security issue if you give me the hostname!)

        If you want random, it'll probably take me several hours, cos its been quite awhile since i last did this.

        Cheers!

        Rich
  • rnxn's avatar
    rnxn
    Occasional Contributor

    richie ChrisAdams 

     

    I think it might help if I give a bit more context to the situation. 

    I work for a financial institution that provides mortgages, so the test cases I'm putting through are test mortgage applications. When I run the XML request (or 'submit the application') it compares my customer's name, DOB and address to the 'Credit Reference Agency' (CRA) data, or the customer database. I don't have direct access to this to be able to reference the DOB's directly from there, which is where the spreadsheet comes in - it calculates the DOB to mirror what will be in the CRA data, so I can use the DOB from the spreadsheet to get a match on my customer from the CRA. 

     

    So I want to be able to choose any customer from our list, and for it to cross reference the customer's name with the spreadsheet (which I thought was my Data source but maybe I'm wrong? Sorry I'm very new to this). So I don't necessarily want to pick a random row, I just want it to return the correct DOB whether I choose the customer on row 1, 100 or 1000 (choosing the customer would be 'at random' by me, but not actually randomised in the technical sense of the word).

     

    "What you need is the means to iterate over each row in Excel until you find the matching forename/surname pairing and return the DOB for that."

    Yeah, this is exactly what I need. 

     

    At times I may run 150 test steps in a row, but other times it may only be a handful of test steps. The same customer information is likely to be used in each test step, but different customers may be used, and some test cases may be testing scenarios with up to 4 applicants, so there will be 4 different DOBs to match. 

     

    Sorry again if I still haven't managed to make it fully clear, so far the most advanced thing I've managed to do in ReadyAPI is set custom project properties and reference them in my XML, so I'm very much at a beginner stage!

    • ChrisAdams's avatar
      ChrisAdams
      Champion Level 3

      Hi,

       

      Re iterating over a spreadsheet to find a row.

       

      I'm using this table in my Excel lookup spreadsheet.

      CustomerId Customer Name Surname DOB
      1 John Lennon 9/10/1940
      2 Paul McCartney 18/6/1942
      3 George Harrison 25/2/1943
      4 Ringo Starkey 7/7/1940

       

      To do the lookup, I wrote some Groovy script.  I used this page to help me read the Excel spreadsheet... https://www.anilvij.com/how-to-read-data-from-excel-using-groovy-soapui/ in order to write my Groovy script.

       

      Here's the Groovy script you can use as an example.  Note : This works for xls files, not xlsx.

      import com.eviware.soapui.support.XmlHolder;
      import jxl.*;
      import jxl.write.*;
      
      // For the sake of example, these are the values returned from your db and these are what we will look for in Excel.
      def forename = "George";
      def surname = "Harrison";
      def dob = "25/2/1943";
      
      
      Workbook workbook1 = Workbook.getWorkbook(new File("C:\\temp\\CustomerData.xls")) //file containing the data
      
      Sheet sheet1 = workbook1.getSheet(0) //Index 0 will read the first sheet from the workbook, you can also specify the sheet name with "Sheet1"
      //Sheet sheet1 = workbook1.getSheet("Sheet1")
      
      size= sheet1.getRows().toInteger() //get the number of rows, each row is a data set
      
      log.info("Number of rows is ${size}.");
      
      // We will use these in our loop.
      def found = false;
      def row = 0;
      
      log.info("Looking for ${forename} ${surname}");
      
      for (int i = 1; i < size; i++) {
      
      	def id = sheet1.getCell(0,i).getContents(); // getCell(column,row)  getContents() teases out the value.
      	def sourceForename = sheet1.getCell(1,i).getContents();
      	def sourceSurname = sheet1.getCell(2,i) .getContents();
      
      	log.info("${id} - ${sourceForename} ${sourceSurname}");
      
      	if((forename.equals(sourceForename)) && (surname.equals(sourceSurname))) {
      		// We've found a match.  Let's note the row and break out of loop to save time.
      		found = true;
      		row = i;
      		break;
      	}
      }
      
      if(found){
      	// Compare DOB
      	// You don't have to use assert, you could use a boolean check or whatever suits.
      	assert dob == sheet1.getCell(3,row).getContents();
      	log.info("DOB is a Match!");
      } else {
      	log.info("Could not find ${sourceForename} ${sourceSurname} in the  Excel lookup sheet.");
      }
      
      workbook1.close() //close the file
      
      

       

    • ChrisAdams's avatar
      ChrisAdams
      Champion Level 3

      Hi,

       

      I've got it working.  I'll write a reply over lunch.