Service Test 11.20 : How to Write Web Service Response to Excel 2010 DataTable

PerfectoTopAd

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:

string price;
price = this.CodeActivity12.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='D:\\STDEMO.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False");
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = "INSERT into [Sheet1$] (COLUMN1) values('" + price + "')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();

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

38 comments
Thulasi - January 17, 2012

How to write the output of a database querry to an XML File?

Reply
    Joe Colantonio - January 18, 2012

    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

    Reply
      SREESATHYA - April 6, 2013

      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.

      Reply
      Shanmukha - January 8, 2014

      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

      -Shanmukha

      Reply
STUSER - January 18, 2012

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”);
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = “INSERT into [Sheet1$] (COLUMN1) values(‘” + price + “‘)”;
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();

Reply
    Joe Colantonio - January 19, 2012

    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

    Reply
STUSER - January 23, 2012

@Joe, No need of your time now. I was able to send output to spreadsheet . Thanks for the code

Reply
abc - July 26, 2012

Joe,

I am facing issues with this.I teried the same code.I donot get any error but, I dinot see EXCEL updated.Please help

Reply
    Joe Colantonio - July 27, 2012

    abc » What version of Excel are you using?

    Reply
      abc - July 30, 2012

      Excel 2003.I tried to save the file in Excel 2007 also.

      Reply
        Joe Colantonio - July 30, 2012

        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

        Reply
abc - July 30, 2012

How do I create default handler in step 6.Please explain with example.

Reply
abc - July 31, 2012

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.

Reply
Kavitha - August 2, 2012

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.

Reply
    Joe Colantonio - August 2, 2012

    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

    Reply
      Kavitha - August 3, 2012

      Hi Joe,

      Thanks for your quick reply, your information helped me to resolve the issue.

      Reply
Madan - August 20, 2012

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…

Reply
Max - December 27, 2012

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 :
InventoryItem[1]
Author
Description
Id
Isbn
InventoryItme[2]
Author
Description
Id
Isbn

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.

Reply
amit - December 27, 2012

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?

thanks.

Reply
Rakhi - February 17, 2013

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

Reply
Kumar - April 3, 2013

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

Reply
    Joe Colantonio - May 21, 2013

    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

    Reply
Rajeev - April 9, 2013

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);
conn.Open();
string strExcel = “”;
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel=”select * from [sheet1$]”;
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,”table1″);
return ds;

Reply
Noel.R.Sinnou - April 12, 2013

Hello Joel ,

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

Can you share across a few sample frame works please ?

Reply
Sri - May 23, 2013

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?

Reply
Sri - May 24, 2013

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

Thanks

Reply
Sri - May 28, 2013

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?

Reply
Jose - December 6, 2013

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.

-Jose

Reply
Sun - December 11, 2013

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:
Problem:
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?

Reply
    Joe Colantonio - December 11, 2013

    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
    fclose(fd1);

    }

    Reply
Nick - October 27, 2014

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?

Reply
Ike - January 8, 2015

as always Joe , you are a genius ! love your TestTalk and good solutions here.

Reply
Chandra Bhushan Singh - November 8, 2016

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!

Regards,
Chandra

Reply
Avaya - January 24, 2017

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.

Thanks,
Avaya

Reply
Anusha - February 18, 2017

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.

Regards,
Anusha

Reply
Click here to add a comment

Leave a comment: