UFT API How to Write Web Service Response to Excel

Automation Testing Published on:

The other day I received an e-mail from my blog-reading friend Samar. He asked:

“I've created a local Excel table with one column (e.g Column1). How can I set a column programmatically?”

Rather than sending him a long e-mail reply, I've decided to answer in the form of a blog post, which follows below.

How to Programmatically Write to Excel 2010

1. First, start the HP Flight sample application and enter the end point http://localhost:24240/HPFlights_SOAP?WSDL to import the flight web services into Service Test

(For step-by-step instructions, be sure to check out my post, HP Service Test 11 – How to Test a Web Services Video).

2. Drag the CreateFlightOrder operation onto the main canvas area.

3. Drag a Custom Code activity onto the main canvas area.

4. Under the Custom Code's “Property Sheet,” create an Input value named joePrice.

5. Click on the “Event” icon and create a default handler for the ExecuteEvent.

6. Enter the following code under your onExecuteEvent under the TestUserCode.cs section:

7. Run the script. You should now have a price value in your Excel file:

How to Use Service Test's Built-in Database Commands to Write to an Excel File

1. Follow steps 1 through 3 (above).

2. Drag a Concatenate String operation onto the main canvas area.

3. Under the ‘Concatenate Strings' “Property Sheet,” enter the following under the Prefix “Property”:

INSERT into [Sheet1$] (COLUMN1) values (‘

For the Suffix, click on the “link to data source,” and point the TotalPrice value from the CreateFlightOrder operation:

4. Drag another Concatenate String operation onto the main canvas area.

For the Prefix, click on the “link to data source,” and point to the previous Concatenate String's Result output.

For the Suffix, enter ‘).

5. Drag an Open Connection operation onto the main canvas area.

6. Under the Open Connection “Property Sheet,” enter the following under the Connection String:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\STDEMO.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False

(FYI – IF you do not have the ACE ODBC driver you should be able to get it by installing http://www.microsoft.com/download/en/details.aspx?id=13255

7. Drag an Execute Command operation onto the main canvas area.

For the Connection property, point to the Open Connection's “Connection Output” property.

For the Command Property, point to the previous Concatenation String's “Results” property.

8. Run the script. You should now have a price value in your Excel file.

Hope this post helps you to excel with Excel and HP Services Test 11.20

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    1. Thulasi » Hi Thulasi- have you tried using ST 11.20’s db activity with the XML\String to XML activity to get the functionality you are looking for? If you use the Database\Select Data activity and point the String To XML’s Source String parameter to Select Data’s results does it work for you? Are you having issues with this approach? Let me know. Cheers~Joe

      1. Hi Joe, We are working on UFT 11.5 currently. We have some clarifications. Like a “query id” that gets generated for test flow in service test we do not see one getting generated for UFT. due to this we are unable to export value to a data in data table.
        Query in Service Test:
        sql = “UPDATE [ZipCodeDetails$] SET CITY='” + city + “‘,ST='” + region + “‘,Country='” + country + “‘ WHERE TC_ID='”+this.Query1.GetValue(“TC_ID”).ToString()+”‘”;

        this.query1 that we have used here am not sure how to handle in UFT. Please guide me.

      2. Hi Joe,

        i want to connect to db through HP ST.not through the code.directly using the Data Source Option available in HP ST.

        Could you please help me out how to connect


  1. Hi Joe,
    I tried above steps. Test has passed successfully but nothing updated in excel sheet. Please help what else should i check.

    Here is my code:

    string price;
    price = this.CodeActivity5.Input.joePrice.ToString();
    System.Data.OleDb.OleDbConnection MyConnection;
    System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
    string sql = null;
    MyConnection = new System.Data.OleDb.OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’C:\\STDEMO.xlsx’;Extended Properties=Excel 12.0;Persist Security Info=False”);
    myCommand.Connection = MyConnection;
    sql = “INSERT into [Sheet1$] (COLUMN1) values(‘” + price + “‘)”;
    myCommand.CommandText = sql;

    1. STUSER » Hi – what version of Excel are you using? I took the code you sent and was able to run it in ST. It did write the price value to the STDEMO spreadsheet. What happens if you create a new script with just a custom code activity and hard code a value for joePrice does it work? Let me know. Cheers~Joe

        1. abc » I will update this post but this code is specifically for Excel 2010. If you are using 2007 you cant try to change the Extended Properties=Excel 12.0 to Extended Properties=Excel 8.0. For 2003 you would have to change the Provider to = Provider=Microsoft.Jet.OLEDB.4.0. Sorry for the confusion. Hope this helps~Cheers Joe

  2. Joe,

    I followed all the steps mentioned for this, however, I do not see excel updated.Could you please guibe me on what might be the issue.

  3. Hi, we have encountered an issue with respect to Data Source using ‘Excel’.
    Can you please let us know how to iterate a single test flow for multiple Rows in an Excel through Service Test. Let us know the configuration to be made in order to achieve this task.

    1. Kavitha » Hi Kavitha – you should be able to do this by specifying a For loop at the Test Flow Level. For example in your ST test click on the ‘Test Flow’. The Test Flow should appear in your Property Sheet. Click on the Input button (Step Icon). Under the Input section click on the ‘For’ Loop radio button and specify the number of iterations that you want to run. Hope I’m understanding your issue correctly. Please let me know if this works for you. Cheers~Joe

  4. Hi Joe,

    I may need your help again. I have similar issue, but I would like import results into local table with in Service test.

    For example, I have created some test to add two numbers from local table. I have added three columns (column a, b and c) in local table and I have filled column ‘a’ and column ‘b’ with values and left column ‘c’ empty. My test will add values from columns ‘a’ and ‘b’ with in a loop for all rows in local table.
    I would like to get the result into column ‘c’ ..

    is there any way to do that please?

    Many Thanks…

  5. Hi Joe,

    First, I want to thank about giving all this information, because in HP Service Test Help file many things are not clear and on internet also it is very difficult to find these answers, I think you are only the right guy.

    My question is : I am getting multi-element response lick for example If I pass book title as input value to “searchbookbyTitle” operation then some time I will be getting 1 book information and sometime more than one (now this is dynamic) but response element name are same (response element with same name but in array parent element)

    Eg :

    In Select Link Source window : Output section :

    So when I used above procedure to write response to excel it is writing only first array “InventoryItem[1]” values now I can select other InventoryIteme[2] elements and add them to “Expression” box in “Select Link Source window” but it will throw error
    (and practically we shouldn’t select each array element because it may be 1000 element also – and we can’t predict also the number of these multi-element response)

    so maybe I am not sure there is option through which I can write multi-element values to excel file, there is “TestFlow” node in “Available Steps” section of Select Link Source window with two child Properties (“Current iteration number” and “Number of iteration”)
    but I am not able to use this option and may be it is not for this purpose, I tried to in HP Service Test help file and on other sits available on internet but didn’t got any solution
    can you please tell me solution.

  6. Hi Joe,

    I used above method to write in excel but in my case some time I am getting multi element value response for example operation taking input as book name and giving output of available books in stock with book isbn number, author information. So there may be possibility that I pass C++ book title then I may getting two book information or more in one response in that case how I can store entire multi element response in different row of excel. (eg if input C++ and out put info is for 3 book then store in 3 different rows of excel)

    is there any way to do that please?


  7. Hi Joe/All,

    We are planning to use UFT 11.5 for web service automation, is there any framework or approach similar to qtp automation ? Is there a way to implement data driven framework for hp st ? Please share the details.

    Thanks in advance.

    – Rakhi

  8. Hi Joe,
    How do I read Response Header information? I need to pass this information to next service on fly. Can you please help on this please. Thanks – Vijay

    1. Kumar » Hi Kumar – sorry for my late reply. To get header information you can use custom code. In the web service’s STServiceCallActivity you can add this.StServiceCallActivity.HttpResponseHeaders to capture response info

  9. Hi Joe,

    As in the below query I need to update an excel row for a specific condition

    say Update [Sheet1$] set tcstatus = ‘pass’ where tcid=’tc1′;

    i tried this, but its not updating the cell. can you please help me on this

    string strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” +”Data Source=”+ Path +”;”+”Extended Properties=Excel 8.0;”;
    OleDbConnection conn = new OleDbConnection(strConn);
    string strExcel = “”;
    OleDbDataAdapter myCommand = null;
    DataSet ds = null;
    strExcel=”select * from [sheet1$]”;
    myCommand = new OleDbDataAdapter(strExcel, strConn);
    ds = new DataSet();
    return ds;

  10. Hello Joel ,

    We are planning to implement Web Service Automation Framework using Functional approach .

    Can you share across a few sample frame works please ?

  11. Instead of writing the response to the local machine.how can I write the response to the spreadsheet that is stored in QC in Test resources. How can I do that? How can I get the path of the spread sheet in the Test resources in QC?

  12. Hi Joe
    Can you respond to my query please? How to write response to an excel that is stored in QC? In the custom code – under Data source I’ve given the path of my excel sheet that is in QC like this “[QualityCenter\\Resources] Resources\BB\data\DEMO.xlsx” doesnt work. How can I give the path of a file that is stored in Test resouces folder? Please help


  13. Thanks Joe. But I was thinking to write webservice response from HP service test 11.2 directly to QC- resource module. Is there a way I can do that WITHOUT using QTP?

  14. Hi Joe,

    Thanks for sharing the above information. I was able to use it in my UFT API Test version 11.52 with a linked Excel data option.

    After saving the UFT API Test in ALM, other users of my team are unable to open the API Test in ALM when they tried opening them in their UFT. It seemed, when I included the absolute path of the linked Excel file, it prevents any user from adding the API Test into their solution.

    Is there a way to fix this? Any suggestion is much appreciated.


  15. Hi Joe,

    We have a regression suite developed using HPST 9.5 and dont have cleint approval to migrate to the newer version (11.2 or 11.5) for those scripts. Could you please help for the below:
    Currently, we are manually capturing the test results by copy pasting into a notepad from the report generated.
    Needed solution: is there a way we can redirect the results from the report generated o notepad?

    1. Hi – its been awhile since I used ST 9.5 – since its built on LoadRunners Vugen can’t you use C to write to a file. Something like this:

      //Function to write to an output file
      int LogFile(char *buffer)
      int fd1;

      //change the output file location if needed
      char *filename = “c:\\temp\\MyOutput.log”;

      //check if there is a fopen error.
      //If fopen is ok, will append to the output file. Else, return an error and exit
      if((fd1 = fopen(filename, “a”)) == NULL)
      lr_error_message(“ERROR: Failed to open file for appending: %s”, filename);
      return -1;

      //append to outputfile
      fprintf(fd1, “%s”, buffer);

      //close file


  16. Hi Joe,

    My question is similar to Sri’s above. How can you write a value to a referenced DataTable in ST/API 11.52(3) within the custom code. The way it works in a GUI script does not seem to work here (DataTable(“column_header”, dtLocalSheet);)

    I’ve tried it two ways:
    var = DataTable(“column_header”, dtLocalSheet);
    string var = DataTable.ReferenceEquals(var, dtLocalSheet);

    Any ideas?

  17. Hi Joe,

    While I am importing excel data file from local drive using the line of code GetDataSource(“InputData!Sheet2″).Import(new ExcelfileImportArgs(@”C:\InputData.xls”,”Sheet2″,True);

    getting error message that “A data source with specified name does not exist.” and if I am adding it through data source wizard it is working fine.

    Can you please look into it and help me out?
    Thanks in advance for your help and support!


  18. How to add muiltiple response data to excel datasheet using write to file function from the toolbox menu -> File -> Write to File.

    I was able to add one item of response under Write to File method in the field contain but I need to store muiltiple items and there is only 1 Contain option.


  19. Hi Joe,

    Your website is a good one.It has helped me during many difficult situation. Thanks a lot.

    I have a question.I want to parameterize the web service request with data from excel sheet. Can you help me to parameterize the request using custom coding?
    Currently I am linking each field manually to data source . I have a request with more than 300 fields.So it is taking a lot of time.

    Thanks in advance.


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Latest Automation Trends: Top 13 Predictions for 2023

Posted on 01/04/2023

Welcome to my annual Latest Automation Trends: Top 13 Predictions for 2023 article. ...

Chrome Selenium Driver in Visual Studio C#

Posted on 12/21/2022

I'm sure you've all heard of Selenium by now. It's a popular tool ...

15 Reasons Why You Should (or shouldn’t) Automate a Test

Posted on 12/20/2022

Just because you can automate tests doesn’t necessarily mean that you should. In ...