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

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

Forum Discussion

Recent Discussions

Vulnerable jetty files

RunTestCase input and return proeprties of the same name

Import API Definition in Ready API

Related Content

Unable to retrieve the worksheet list from the specified workbook. - ERROR load Excel

Script to verify DB Table workbook

Specify a variable in SQL Designer