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