How to import data file for UFT API testing?


I am working on a project which requires to dynamically import the test data on run time and run the API test corresponding to the test data.

I tried using XMLload which I directly load from the xml request. This seems to be working fine.

But like GUI testing, where we import the test data, I want the same for API testing – where I can import a excel from an external source dynamically and give the values to the request.

How can I do this?

  • You must to post comments

As far as I understand your question, you want to load test data from Excel file. And that is what I have done in my previous project but little differently, so I hope this will be some what useful to you.

These steps will help you drive your test from Excel file where you will provide the location of request XML files and other test data, for example:

  • Execution flag
  • WSDL
  • End Point address
  • Environment
  • and so on…


Steps to import data from Excel file:

Open Data pane (View -> Data)

Click on New Data Source

enter image description here

Select Database (Don’t worry we’ll be using Excel as Database)

New window will appear to Set Database Connection

Click on Build Connection String icon

enter image description here

Click on Provider tab if not selected

Select Microsoft Office 12.0 Access Database Engine OLE DB Provider

Click Next

In Data Source section, provide the path to your Excel (.XLSX) file and leave everything to default on this (Connection) tab

Click on Advanced tab

Select Read Write from Access permissions section

Go to All tab

Select Extended Properties and click on Edit Value

Type Excel 12.0 Xml in Property Value field and hit OK

Now go back to Connection tab and click on Test Connection button

It should show Test connection succeeded message, if NOT than go back and follow all the steps again

Click on OK

Click Next

Provide the sheet name (that you want to import) in Data Source name field

For example: [TEST_DATA$] where TEST_DATA is a sheet name

Type in your SQL query in big box just above Check SQL Statement button

For example:

I want to import all the rows from TEST_DATA sheet where Execution_Flag column is having Y value. And SQL statement for that I’ve used:

SELECT * FROM [TEST_DATA$] WHERE Execution_Flag = 'Y'  

Click on Check SQL Statement button and it should to show the resulted rows in new Query Preview window

Close Query Preview window and click on Finish on Set SQL Statement window

Now you should see resulted rows in Data pane

* What I like about this approach is if you’ll make changes in Excel file and refresh the Data pane, it will automatically update the data in Data pane


Now to read the data from Excel file:

Let’s say I want to read my request XML file from location specified in Excel file (which we have imported)

I am using Read From File activity

Note: This is just an example to give an idea

Drag and drop Read From File activity in your work area

This will complain that path is not specified for the file from which to read in Errors pane

Double click this message and it will highlight the field for you in Properties pane

Hover your mouse on Value field next to File path and click on last icon Link to data source and this will open Select Link Source for 'File path' window

Click on Data source column radio button and then click on TEST_DATA from Select a data source section

Select the corresponding column name from Select data section and click OK, like shown in image

enter image description here


Similarly you can pass value/s to Custom Code from Excel file through Data pane by linking data source and passing it as input and in custom code you can retrieve the value this way:

string environmentName = CodeActivity18.Input.Environment_Name;  

And same way you can write to Excel file as an output from custom code:

string finalResponseXMLPath = "some path";
CodeActivity18.Output.ResponseXML_FinalPath = finalResponseXMLPath;
  • You must to post comments
Showing 1 result
Your Answer
Post as a guest by filling out the fields below or if you already have an account.