how to create CSV files (editable in Microsoft Excel) from Data extracted from tests

[Deleted User]

Often you will want to extract values that are returning from a test and write them into an excel file. This could be useful when building data sources dynamically from previous tests. SOAtest does not have a native interface to create Microsoft Excel files, however we can easily create CSV files that can be read by our CSV data source and can be edited by Excel.

To accomplish this we will utilize the XML Transformer to extract the values we want to write into our CSV file and then utilize a Write file tool to create the file
1. Start by attaching an XML transformer to the Response envelope of the your client
2. Double click on the elements that you wish to extract

3. You then want to combine the XPaths to ensure the values print out to one line (as it is now
they will create 3 separate lines
a. To do this start by double clicking on the first Selected XPath and copying the XPath to
a notepad
/:Envelope/:Body[1]/:getItemByIdResponse[1]/:Result[1]/id[1]/text()
b. Go ahead and copy all of the selected Xpaths
c. Your note pad should look similar to this
/:Envelope/:Body[1]/:getItemByIdResponse[1]/:Result[1]/id[1]/text()
/:Envelope/:Body[1]/:getItemByIdResponse[1]/:Result[1]/title[1]/text()
/:Envelope/:Body[1]/:getItemByIdResponse[1]/:Result[1]/quantity_in_stock[1]/text()
d. You will now add the pipe character in between your statements this will add the values to
one line
/:Envelope/:Body[1]/:getItemByIdResponse[1]/:Result[1]/id[1]/text()
|
/:Envelope/:Body[1]/:getItemByIdResponse[1]/:Result[1]/title[1]/text()
|
/:Envelope/:Body[1]/:getItemByIdResponse[1]/:Result[1]/quantity_in_stock[1]/text()
e. In the XML Transformer go ahead and Remove all of your extractions but one

f. Double click this Selected XPath and Paste in the combined XPath into the XPath field
g. Click Evaluate to ensure you are extracting the correct elements

4. We now want to add commas between each extracted value
a. Drop down the Options Arrow and select Allow Alteration

b. Click on the new Alter Tab and select the elements again(this time we are altering their contents)

c. Double click on each element and select Append and put a comma in the Value field (don’t add a Comma to the Last element)

5. We now want to create the new line between each run (or data source row)
a. Open the Extract tab in the XML Transformer
b. Click on any element and press “Extract XPath”
c. Select the new XPath and click Modify

d. Change the XPath to concat('',/n),
Please note that the quotation marks above, are two single quotation marks ' and not one double quotation mark ".

e. Press OK
f. This will append a new line
g. Save the XML Transformer
6. The final step is to write the values into a CSV file
a. Right click on the XML Transformer and select Add Output
b. Select the write File tool

c. Configure the WriteFile to create a CSV file. You can find the documentation on using the WriteFile tool in the SOAtest Documentation here: Parasoft SOAtests User's Guide > Reference > Available Tools > Write File
d. Be sure to check the Append Check Box (this continues to write to the file instead of overwriting)
e. Once configured the tool should look like this

The tools are now properly configured. You can use this method to load up the CSV file with values. In addition to this you will be able to edit these values with Microsoft excel as well as using the Excel and CSV data sources.

This technique can also be used to load up a data source that you will eventually use in a Virtualize Responder as the Data Source correlation.

reactancexl

Has anyone actually got this to work? This seems really simple but It does not work for me.

OmarR

What portion of the workflow are you having trouble with? Is there an error you're seeing?

reactancexl

I have two repeating nodes. On the alter tab I xpath (all occurrences) for each and append a comma on the first node, not the second. On the extract tab i randpoml picked a node and replace the xpath with concat('',/n). when run I get zero bytes. I would expect the output would be two columns on the csv (one for each node). I have tried several different methods.

reactancexl

I can get it to work horizontal, where I have node1 comma separated values on first line and node 2 comma separated values on line2. The concat('',/n) just does not work and the only way I get close is doing it this way. Here is my screenshot.

sujaldalia

Any update on this? I'm having similar problem that concat(''/n) does not split the records

Ireneusz Szmigiel

I'm using similar approach, but with string-join and concat.
In my case XML Transformer with following Xpath in Extract part:
string-join(/*:Envelope/*:Body/*:getItemByTitleResponse/book/productInfo/(concat(id[1]/text(),',',name[1]/text(),',',stockQuantity[1]/text())),codepoints-to-string((13,10)))
looks like bellow and works like a charm on my side.

Then you just need to add WriteTool to save output on filesystem.

Be aware about codepoints-to-string usage, it will create CSV file with MS Windows format (CRLF) and that you may need to wrap texts with double quotes (or other delimiter) for MS Excel.
Ireneusz Szmigiel

Shashi7403

Hi all, getting save the csv file but in file value not get reflected .

Shashi7403

Any one can help one scenario ?
1. I have one request with multiple data and want to hit the client .
2. Client will response one unique id.
3. I have to save unique id and then once aggai have to hit the client.
4. Client will give response based on unique id .
5. In response dependent on first request where we have data.
Example * Request**

   <Bindings>
<Binding>
  <A>1066003212646</A>
  <B>4567890</B>
</Binding>

1066003212646
4567890

**Response **

34567584404958ertyhuj

Request
Above Unique id.
Response :based on above request data getting response

1796863457667
RXQIHIANSMALEJHHCZXLVLGUGMURIRVIKBSCIGQPEFMNGRVW

I want to store First Request and second response in CSV file then based on second request , i want to fetch the data and setup the final response.

jakubiak

Hi @Shashi7403, if I understand your scenario correctly you don't need to use a CSV file to handle your scenario. You can simply save the value from the first response using a data bank, and then parameterize your second request using the parameterized value. The data bank will allow you to store and reference the value without needing to write out a CSV file. Please let us know if that works for you.

Shashi7403

Thanks Jak. I have tried with Data Bank but not yet archive the GOAL :-(
But the problem is we have multiple same child node which have different value .
So first Request need to save in Excel or CSV. (This approach i am trying do that)
from the first request will get response as Unique ID. (This Unique have to save and also want to map with first request).

Now once again have to get above Unique id and then have to sent to client.
Now finally will get ACK Response where data belong to first request.

jakubiak

The fact that you have multiple child nodes with different values does not require you to save as CSV. The only time you would need CSV is if you need to save those multiple values. If you need to save only a single value, a data bank will work as long as you create an XPath that extracts the exact value that you need. So how many values do you need to save to send in the second request?

Shashi7403

Not able to generate CSV file in system location.
i have follow the above steps. but still no luck

amarendrapattnaik

its working for if you have SOAP web services, but how we can do if it's RESful APIs we don't have any JSON Transformer (with XML transformer we can't see the data elements in Tree view)

jakubiak

For JSON you can use an XML Converter tool to convert the JSON to XML, and then you can use teh XML Transformer as described.

UK_

Is it possible to get the headers/tag names in CSV using this approach ?

jakubiak

Yes - you can use an XML Converter tool to convert from CSV to XML, and then use an XML Data Bank to extract the values you want.

UK_

I want to create a CSV file with the header/tag names. When I extract the values from XML into CSV, I get the values but no headers/tags. I want to know how I can have the headers/tag names in the CSV that I am creating.

jakubiak

Can you post an example XML/CSV so it's more clear what you are trying to do?

UK_

Unfortunately I can't. I am using the process described to create CSV files from Data extracted from tests. The data extracted is in XML format. When I use the Transformer to write it to CSV file, it gives me the value of the element. I need to have the first Row in CSV as the Column name which is same as tag name.

For example, if my element is:
SERVICE

I am able to extract SERVICE in my CSV. I want to add Description as my Column name in CSV.

jakubiak

I think the easiest thing would be to write an XSL file that will do the transformation that you are looking for, and then use SOAtest's XSLT_Tool to do the transformation. I did a quick Google search and here is a post that might get you started: https://stackoverflow.com/questions/365312/xml-to-csv-using-xslt

sushil

hi is there any way to save complete dynamic JSON output in csv in columns . right now i used write file and saved in CSV but it is saving in one row.

my expectation is to save all json values in csv with 2 columns , field name and field value.

i cant use data bank because json file is dynamic, one file might have 10 field and other might have 100 .

thank you in advance

bsaikrishna84

USAGE : For Responder

I tried using the above logic but am getting below error. can some please help on this ?

Cannot convert XPath value to Java object: required class is org.w3c.dom.NodeList; supplied value has type xs:string

I have DB tool where i get resultSet. I chained XML transformer to output with below query and chained a TextBank to save result. Below xpath is working when i click evaluate xpath. But when i tried to run am getting above error.

string-join(/resultSet/rows[1]/row[1]/(concat(TRNIDENTIFIER[1]/text(),',',SORCATEGORY[1]/text(),',',AMOUNT[1]/text())),codepoints-to-string((13,10)))

I have rows till index of 187.

to

for each row i have TRNIDENTIFIER, SORCATEGORY & AMOUNT where i need to pull out each of the values and have to form an xml response to send it back to client

jakubiak

There were two different issues that got fixed in SOAtest and Virtualize related to that error message - one was fixed in 9.10.7 and the other in 9.10.8. What version of Virtualize are you using?