Monday, April 29, 2013

DataDriven Testing from SOAPUI Pro using Excel File



Considering all Test automation tool which supports Data Driven testing, then only we call it as Test automation tool otherwise it’s just test tool.
When it comes to data driven testing for WebServices using SoapUI tool, follow the steps below,
What is Data-Driven testing?
 Quite simply put, data-driven testing is when you store test data (input, expected output, etc) in some external storage (database, spreadsheet, xml-files, etc) and then use that data iteratively in your tests when running them.

Create Test Data for SendSMSToIndia request as follows.  [Here I have taken SendSMS WebServices for example. (http://www.webservicex.net/SendSMS.asmx?WSDL)]
Test Data in Excel File
Before creation of test data we have to make sure that how many fields requires test data in SendSMS request. Just open the SendSMSToIndia request by double clicking or Right click and select Open Editor menu option. The below screen shot for SendSMSToIndia request.



For the above three fields I have created the sample test data as shown in the below screen shot. I have given Sheet name as “SendSMSTestData” and saved it in the local system Driver (here I used D:\ drive) with the name as Data_Manager_SendSMSToIndia.xls, here one more important note is while saving the excel file after creating the test data we should save as “.xls” and SoapUI is not support any other format other than .xls.





Make sure you have created the project and added the Request to Test Suite, to Test Case, to Test Step and DataSource and DataSource Loop should be as shown in below screen shots.



In above screen shot you can see the hierarchy of the Test Steps, make sure in you project the hierarchy same as above.

DataSource:- Is Use to reads test data from external source(Excel, File, GRID, XML, Database) into properties of request or can be used for validation.
DataSource Loop:- Is Use to loop or iterate the DataSource till the last row. Here we have select the DataSource and target step where we have to provide our input data.[will be explain in below section].

Now Open the DataSource by double clicking on it or just Right click and select open editor.





DataSource editor looks as below screenshot and the list of available data source types so you can import test data and in that select Excel option for importing the external data from excel file to SoapUI data table.





Once you selected the Excel as DataSource then click on the Browse button to locate the Excel file which you created above, and enter sheet name as “SendSMSTestData” . and change the Option Start at Cell from ‘A1’ to ‘A2’ because in our excel sheet cell A1 is our headings and our actual data starts from cell A2 hence it should be A2.
Now create properties in properties pane you can see in left side of the above screen shot. Click on the plus[+] sign pop with user definable property name as below screenshot.











Click OK then you can see the properties in left pane as below.[repeat for the remaining fields or columns in Excel sheet.]



And you can see the one checkbox as of now you can ignore as it is unchecked.[will discuss it later].
Now all set to import the test data from Excel sheet to SoapUI data table. To import click on the ‘Green’ button  from the DataSource header pane. Once you click on the green button then SoapUI ask user to import all the rows or import only limited rows as below.





By default value will be 10 there you need to enter how many rows you want to import. In my case I want to import all the rows from the excel sheet so I will give zero [0] hence all the rows imported to SoapUI data table as below screenshot.


 Observe the above screen shot while creating the test data we have created 9 set of test data, same set test data is imported.
Now time to map the DataSource properties to our Request. Now SendSMSToIndia request in editor mode as shown below. And here you can see all the three fields as,
·         MobileNumber:
·         FromEmailAddress:
·         Message:
Here you can also see there is small button click on that or Right click on the TestBox then you can see Menu with options in that as below.





In Menu you can see “GetData..” option click on that and again you will see one more sub menu as in below screenshot. And also you can see the DataSource name which we created above.



Now click on the DataSource option there you will see the all the properties which are created in DataSource as below.




Now map the properties of the DataSource to the SendSMSToIndia request in editor, for each field in the request select the appropriate property from the DataSource. One you selected all properties the SendSMSToIndia request in editor look as below.




Once the mapping is completed now open the DataSource Loop by Double clicking or Right Click on it and Select ‘Configure’ option from menu, then you will see as below pop window.




Now here you can see two list boxes first one is ‘DataSource Step’ here you should select the DataSource, because from DataSource Step only we are reading the External data where we imported our excel file data into DataSource in above.
And second one is ‘Target Step’ here you should select the SendSMSToIndia request, because here DataSource supplies the Data from the DataSource selected in DataSource Step’.
Once you selected the correct steps in Configure DataSource Loop then, Configure DataSource Loop should look like as below screenshot.




Then Click on ‘OK’ button.


Now all set to run the Test Case for all the test data in the DataSource. Running the Test case you have to open the Test Case Running window you can double click on the Test Case or Right click and select ‘Show Test Case Editor’ option from the menu.























And one more important note is the steps in the Test Steps should be in the same arrangement. Like DataSource should be at first Step and Request in Second and DataSource Loop should be always last.

To Run the Test Case Click on the ‘Green’ button at the left top Corner of the Test Case pane.
Once execution start you can see the logs in Test Case Log pane ate below and a gree bar with “Finished” message at the top indicates test case execution complted for the all the rows in the DataSource.





That’s all Floks.









43 comments:

  1. Nice blog. It will be more useful if you add more about groovy script..

    ReplyDelete
  2. Really good and detailed tutorial for Data driven testing.
    Thanks

    ReplyDelete
  3. Nice blog for SOAP UI Learners Keep posting new things :-)

    ReplyDelete
  4. Hi i need some help to resolve following issues

    1)I can able to take the data from the Excel file by using Datasource but i want to keep assertions(validation) for each response which is generated based on the input data.

    2)I was unable sending the output(response) to the excel file(out file)

    ReplyDelete
    Replies
    1. Hi Vanajakshi,

      Thanks for your time reading my blog, for your second answer you can find answer in my another blog, (http://lgsofttest.blogspot.no/2013/05/custom-report-generation-from-data-sink.html)

      and for 1st question...
      in Groovy Script you can put your own custom assertion as say validating your expected result and actual result by follwoing below steps

      Make sure your your SoapUI test case in this below arrangment

      1 DataSource
      2 Request
      3 Groovy Script
      4 any script or Data Sink can be any
      5 DataSource Loop

      now in groovy script right click and select Get data and get the data from the Datasource and store it in a variable, next do same right click select get data this time get the data from the Response(I guess you know how) and store it in a variable and after that put 'IF' condition for each row executed in the Datasource will verify with the expected and actual value.

      Now are done with Custom assertion for values.

      Delete
  5. Hi Sunny.....As per your instructions I created datasource,Request and Datasourceloop . I saved data just 5 rows in Excel. Ran tests. I am getting results upto 69th row. I was also setup condition in Datasourceloop. Please help me out to get ride of this problem.

    ReplyDelete
    Replies
    1. Hi Thanks for Reading My blog,
      I suggest you to select from 6th row to till last row and Right click and delete all the rows, and save the excel file. and import the excel file to SoapUI datasource again, and run the test again.

      let me know if you face issue again.

      Delete
  6. Thanks Sunny.....It is working now. Now I am facing different problem. I just entered data in 5 rows (5 records) and saved in .xls format and imported data sheet into Datasource.Then ran the test first time. Received results for 5 rows in Testlog. But I am seeing extra 5 rows with data were added in datasheet. Then I ran same test second time. This time also received test results for 5 rows only, but extra 5 rows were added in datasheet. Why the extra rows are adding in data sheet on each test running ?

    Second concern is I want to add Comments column in my data sheet to briefing about test scenario. How to do uncommenting this column while importing into data source. Because I want to validate test results with Test scenarios in Comments column after running the data driven test.

    Thanks for your time and support as always.
    Jana

    ReplyDelete
    Replies
    1. Hi Jana,
      Regarding your second concern you can add Comments column in your input datasheet, and create new property in DataSource as "Comments", then you will Comments values in your DataSource, if you want to add this Comments column in your report then follow my other blog on reporting.

      Comes to your 1st concern, you have seen this behavior in DataSource? after running for 5 rows you saw 5 rows extra rite,? its not problem this wont cause any issues for running test rows, its default behaviour of SoapUI

      Delete

  7. Hi Friends,

    I am using Soap UI 4.5.2 free version. Please observe project structure




    My Project has only one suite
    Suite has two test scenarios here (can have many)
    Here in my project each test step is a test case
    When I run this suite, I want each test scenario should have one excel sheet
    In that excel sheet, I need to get the test case name, request xml, response xml, and status as shown below






    I have tried some groovy scripting; I am sharing the script also. Please do help


    //Author : Vasudev Reddy
    import java.text.SimpleDateFormat
    import java.io.File
    import java.io.FileInputStream
    import java.io.FileOutputStream
    import jxl.*
    import jxl.write.*

    //Get Project Name
    def ProjectName= testRunner.testCase.testSuite.project.name
    //log.info(ProjectName)

    //Get Test Case Name
    def TestCaseName= testRunner.testCase.name
    //log.info(TestCaseName)

    //Current Date
    dateFormat = new java.text.SimpleDateFormat('dd-MMM-yyyy HH-mm-ss')
    def Date = dateFormat.format(new Date())

    //Selecting a folder path
    def folderPath = "C:\\Vasu\\Test\\" + ProjectName +" Results"
    createFolder = new File(folderPath).mkdir()
    // log.info("Folderpath" + folderPath)


    def workbook = Workbook.createWorkbook(new File(folderPath +"\\"+ "Results " + "(" + Date + ")" + ".xls"));

    def soapuiRequests = testRunner.testCase.getTestStepsOfType(com.eviware.soapui.impl.wsdl.teststeps.WsdlTestRequestStep)
    soapuiRequests.each{

    def sheet = workbook.createSheet(TestCaseName, 0)
    Label header1 = new Label(0, 0, "Test case Name");
    sheet.addCell(header1);

    Label header2 = new Label(1, 0, "Request XML");
    sheet.addCell(header2);

    Label header3 = new Label(2, 0, "Response XML");
    sheet.addCell(header3);

    Label header4 = new Label(3, 0, "Status");
    sheet.addCell(header4);

    def testName = it.name;

    Label testcasename = new Label(0, 1, testName);
    sheet.addCell(testcasename);

    Label requestxml = new Label(1, 1, context.testCase.getTestStepByName(it.name).getProperty("request").value);
    sheet.addCell(requestxml);

    Label responsexml = new Label(2, 1, context.testCase.getTestStepByName(it.name).getProperty("response").value);
    sheet.addCell(responsexml);

    Label status = new Label(3, 1, testRunner.status.toString());
    sheet.addCell(status);

    workbook.write()
    }

    workbook.close()


    I am able to create the folder, excel file inside the folder, first test case row also,
    But iam not able to get all test case one after another.

    I know I am missing the loop somewhere please do help me

    please do provide me the email address so that i can explian in clear with screenshots and send you mail. iam not able to put the images here to explain the flow - See more at: http://www.techcrook.com/2012/11/groovy-scripting-turn-soap-ui-into.html?showComment=1385547336947#c582346570130223039

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Hi Sunny,

      Thanks for your tips. I ran data driven tests using 30 records in spread sheet. Saved document in .xls format (Excel97-2003 workbook). I am seeing strange things with my test results. Out of 30 some records ran successfully and half of the records were not having xml req and response. For some records I did see Xml request but there is no data in Response. Please do help me where I am doing wrong.

      Delete
    3. Hi Thanks for Reading My blog,
      I have small question here, in your excel sheet how many records you have.

      For example if you have 10 records, and you want to run only 10 records then go to excel sheet and select from 11th row to till last row of the excel and delete those rows, then save and import to DataSource, run the suite now you will see only 10 records are executed. its because in some other rows or cell you may have data to vercome this we have to delete the unused rows.

      let me know if you face same issue again.

      Delete
  8. Hi Sunny,

    As per your blog I have created the same in below order.
    1. Data source
    2. Request
    3. Datasource loop

    Step 1 : I have imported the excel data in data source by creating propertyname (i.e .column name of excel sheet)

    Step 2: When i open the Request editor. I can able to see one field, unable to see three fields .

    Can you help me on this ?

    ReplyDelete
    Replies
    1. Hi, Thanks for reading my blog, i have one small questin to you, can you let me know which WSDL you are using and what is actual request you have. Did you use the same WSDL or service as it is in the post or some other service. Please clarify based on that i can help you out.

      Regards,
      SunnyBoy.

      Delete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Hi Sunny,

    The Info provided is good and helpful but I am using a free version. Can you provide me the Groovy Script for Taking Input from Excel and giving an output in Excel with the Assertions.

    ReplyDelete
    Replies
    1. Hi Thanks for your time in reading my blog,

      please find the below answer for you query

      http://lgsofttest.blogspot.no/2014/06/datadriven-testing-in-soapui-open.html

      Regards,
      Sunnyboy

      Delete
  11. Hi Sunny,

    I need your help.

    Actually we are retrieving data from database and exporting data into the excel sheet through SOAP UI, in this case export data overwritten to my previous data but some of the records are not clear(For example In my excel sheet 10 rows of data already existed, after run the soap UI I have got 5 rows of data form database then data is going overwritten in first rows of data but not clear remaining 5 rows of data). is there any solution to clear data before exporting data to excel sheet through Groovy ?

    ReplyDelete
    Replies
    1. Hello,

      if you are using Groovy script, then 1st take rowcount of excel sheet and write new records at next row by providing rownumber.

      Delete
  12. Hi Sunny, thanks for this Blog, this is really helpful. I have a question on Excel sheet data, suppose if excel has empty record, then Soap UI is not reading that field and getting an error. Do I need to use any If condition in Soap UI request? how do I approach? can you please help me on this?

    ReplyDelete
    Replies
    1. Hello,

      You can use If condition to check empty cells. if cell is empty the skip that row and continue with next one.

      Delete
  13. Thanks. It's really help me :)

    ReplyDelete
  14. Hello SunnyBoy, First and most thank you, my question is I am using the open source soapUI 5.0.0 and want to drive data to the test case from excel sheet. would you please help. my WSDL is medical supplier by city so the datasheet will have 15 cities, I want to loop to all 15 cities and confirm the records at the same time(adding an assertion). hopefully I am clear in my question but pls let me know if I need to clarify any question.

    ReplyDelete
    Replies
    1. Hey,

      Thanks for reading my blog,

      Here are the prerequisite required for doing so.
      1. Make sure SoapUI 5.0.0 has jxl jar file in its lib file(under \SoapUI-5.0.0\lib\)
      2. jxl jar is not available in Lib folder, then you need to download jxl.jar file and place it in (\SoapUI-5.0.0\bin\ext\) folder and restart the SoapUI to load the jar file.
      3. As usual create the SoapUI project in SoapUI and follow the steps below,

      //Import the excel and other packages like below,

      import com.eviware.soapui.support.XmlHolder
      import java.io.File;
      import java.io.IOException;
      import jxl.*;
      import jxl.read.biff.BiffException;
      import jxl.write.*;


      //Use below code to read excel file
      Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
      Sheet sheet1 = workbook.getSheet(inputDataSheetName)


      //Use below code to read row and coloumn count
      rowcount = sheet1.getRows();
      colcount = sheet1.getColumns();

      //Use below code to read request Xml
      def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
      def holder = groovyUtils.getXmlHolder("reqOperationName#Request")


      //Use below code to pass value to respective tag or node
      holder.setNodeValue("//*:NodeName", "NodeValue")
      holder.updateProperty() // this line of code will save request xml

      //Use beloe code to Run the request or operation
      testRunner.runTestStepByName(reqOperationName)

      for more details on this, please go through the below blog link this will help you for sure.
      http://lgsofttest.blogspot.no/2014/06/datadriven-testing-in-soapui-open.html


      Let me know if any issues, i can try my best to slove your query.


      Thanks
      SunnyBoy

      Delete
    2. hello Sunny, I appreciate for your response I am using the WSDL file of "http://www.webservicex.net/medicareSupplier.asmx?wsdl
      " a very simple medical supplier and choose operation by city. I use your code but kept getting an error

      import com.eviware.soapui.support.XmlHolder
      import java.io.File;
      import java.io.IOException;
      import jxl.*;
      import jxl.read.biff.BiffException;
      import jxl.write.*;


      //Use below code to read excel file
      Workbook workbook = Workbook.getWorkbook(new File(MyFiles));
      Sheet sheet1 = workbook.getSheet(sheet1)


      //Use below code to read row and coloumn count
      rowcount = sheet1.getRows();
      colcount = sheet1.getColumns();

      //Use below code to read request Xml
      def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
      def holder = groovyUtils.getXmlHolder("reqOperationName#Request")


      //Use below code to pass value to respective tag or node
      holder.setNodeValue("//*:NodeName", "NodeValue")
      holder.updateProperty() // this line of code will save request xml

      //Use beloe code to Run the request or operation
      testRunner.runTestStepByName(reqOperationName)

      //H:\\MyFiles.xlsx

      Delete
    3. Hello,

      Please use the below code for your query.

      import com.eviware.soapui.support.XmlHolder
      import java.io.*;
      import java.net.MalformedURLException;
      import jxl.*;



      log.info("Service Testing Started")

      Workbook workbook = Workbook.getWorkbook(new File("H:\\MyFiles.xls"));
      Sheet sheet1 = workbook.getSheet(inputDataSheetName)

      def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
      def holder = groovyUtils.getXmlHolder("GetSupplierByCity - Request 1#Request")

      try{
      rowcount = sheet1.getRows();
      colcount = sheet1.getColumns();

      for(Row in 1..rowcount-1){
      for(Col in 2..colcount-1){
      String s2 = sheet1.getCell(Col,0).getContents()
      def TagCount = holder["count(//*:"+s2+")"]

      if(TagCount!=0){
      String NodeValue = sheet1.getCell(Col,Row).getContents()
      holder.setNodeValue("//*:"+s2, NodeValue)
      holder.updateProperty()
      }
      }
      // To Test Request
      testRunner.runTestStepByName(reqOperationName)

      }
      } // Row loop Ends Here


      }catch (Exception e) {log.info(e) }
      finally{
      workbook.close()

      }

      log.info("Service Testing Finished")

      Note :- Make sure you are using excel file with ".xls" extension NOT ".xlsx". Use only ".xls"


      Regards,
      Sandeep S S

      Delete
  15. hello Sunny, thank you, when I execute the test I get the below error message. its failing on these line " }catch (Exception e) {log.info(e) } "

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script30.groovy: 38: unexpected token: } @ line 38, column 2. }catch (Exception e) {log.info(e) } ^ org.codehaus.groovy.syntax.SyntaxException: unexpected token: } @ line 38, column 2. at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:140) at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:108) at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236) at

    ReplyDelete
    Replies
    1. Hello,

      Follow this link and steps provided in this topic
      http://lgsofttest.blogspot.no/2014/06/datadriven-testing-in-soapui-open.html

      let me know if any issues.

      Regards,
      SunnyBoy

      Delete
  16. This comment has been removed by the author.

    ReplyDelete
  17. How can I achieve a load test in soapUI ng PRO & check for its results when I wanted the testing to be data driven from an excel sheet?

    ReplyDelete
  18. Hello,

    Thanks for your time for reading my blog,

    Here it is how we can achieve load test while doing data driven test.

    Once we created 'Test Suite' and 'Test Case', under test case we can see "Load Test(0)" default option from soapUI. Right Click on 'Load test' and Click on 'New Load Test', and give any valid name for you load test.

    Once you are done with above step, now we can see all possible load test options and select and set desired load test requirements, and run the test. With this you can achieve load test also.


    Regards,
    Sandeep S S

    ReplyDelete
  19. hi sunny,

    Hi Sunny,

    READY API GET CALL

    I have taken a data source .csv file in order to fetch all the rows. I am using that in a REST request. The request is going to the response in the format of
    /XDImgService/rest/wado?requestType=WADO&studyUID=2.16.840.1.113669.632.20.1211.10000098591&seriesUID=1.2.840.113704.1.111.5692.1127828999.2&objectUID=1.2.840.113704.7.1.1.6632.1127829031.2&contentType=application/dicom.

    It's a GET REQUEST with 5 parameters.

    how to write a groovy script in order to hold that request in requestholder.

    ReplyDelete
    Replies
    1. How to read the data from data source in to this groovy script

      Delete
  20. Hi
    how many columns we can use in excelfile

    ReplyDelete
    Replies
    1. Hello,

      You can use as many as you need. Its depends on your requirements.

      Regards,
      Sandeep S S

      Delete
  21. Hi,
    I want to pass excel file as input to soap request. How to do that?
    I am using SOAPUI - free version.
    My requirement is
    We have URL - where we can upload input file (Excel file).
    The same I wan to test using soapui . Dont know how to pass file itself as input using soap request. PLease help me on this
    If you need any clarification, please sent mail mchelliah55@gmail.com

    ReplyDelete
  22. Awesome blog and this is so spoon feeding as even an amateur could start testing APIs using this clear cut steps in place. I am exporting this page as a PDF for future use.

    ReplyDelete
  23. Nice Info...really very helpful
    actually same kind of data in excel but my request is in json so can you please guide me how to send excel data post request in json

    thanks
    dev

    ReplyDelete
  24. Appreciate you for SoapUI write up. I like to read your Selenium WebDriver write up.

    Thanks.
    Hossain

    ReplyDelete