Datasource Excel to array

I am not too sure what you are trying to do, but i will try to answer based on my understanding.

I believe you are trying to parameterize your test request which would accept 2 parameters from the two columns in your excel sheet.
And you have 3 sets of data in 3 rows. If this is what you want to do, then you can create property transfer step and transfer properties from row 1 to properties step which would have two properties. You would then read these from your request and then finally place a data source loop to loop through 3 rows.

If however you want to populate different nodes in your request xml with different row sets, you might want to create 3 property transfers in the same property transfer step (PT1, PT2 and PT3). These would be transfering properties to your request. The catch is, you will have to create as many number of peroperties as there are cells to populate different nodes. An easier way could be groovy.

Hope this helps.

Regards,
Deepesh Jain

Hi Deepesh Jain

deepesh.jain wrote:

I believe you are trying to parameterize your test request which would accept 2 parameters from the two columns in your excel sheet. And you have 3 sets of data in 3 rows. If this is what you want to do, then you can create property transfer step and transfer properties from row 1 to properties step which would have two properties. You would then read these from your request and then finally place a data source loop to loop through 3 rows.

This is what I'm trying to do yes - and a property transfer is what I would use. The thing is though, the node I'm trying to populate is an ArrayOfObjects. So I can't just point and click (or use XPath) where the data should transfer too, I have to dynamically add an element to the Array first, then transfer the data of the row, then add another Array element and so on. So in order to use the property transfer, I would first have to create the same number of Array elements in the request as I have rows in my Excel. That however wouldn't be the point of storing the data in an Excel, as I would have to adjust the request everytime I alter the amount of data in the Excel.

Is there a standard way of doing this or would I have to refer to Groovy? I don't know Groovy at all. With JavaScript it would be easy, but as far as I can see JavaScript isn't really supported. I can only see it in the project properties, but when I switch to JS, it seems the interpreter is still expecting Groovy - and I also can't find any documentation on JavaScript usage in SoapUI.

Thanks,
Christof

How can a node be an array of objects? Does the node accepts multiple values or something? It might help if you can share a sample request. In a hind sight, groovy might be your best bet. It is indeed very similar to Java. Here are the links to two api's which might help you get started:

soapui api:
http://www.soapui.org/apidocs/

groovy api:
http://groovy.codehaus.org/api/index.html

I would also suggest the link below to get you started just to give you some basics on arrays in groovy.
http://pleac.sourceforge.net/pleac_groovy/arrays.html

Hope this helps.

Regards,
Deepesh Jain.

deepesh.jain wrote:
How can a node be an array of objects?

Well, basically a sequence of complex types:

<xsd:complexType name="ArrayOfProduct">
  <xsd:sequence>
     <xsd:element name="item" type="tns:product" minOccurs="0" maxOccurs="unbounded"/>
  </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="product">
  <xsd:all>
     <xsd:element name="number" type="xsd:int"/>
     <xsd:element name="name" type="xsd:string"/>
  </xsd:all>
</xsd:complexType>

So the request would look something like:

<products>
  <item>
    <number>1</number>
    <name>Jacket</name>    
  </item>
  <item>
    <number>2</number>
    <name>Wallet</name>    
  </item>
</product>

So whilst reading from the Excel file, I have to create a new <item> node dynamically on every iteration.

Thanks for the links, I'll have a look.

Okay, I understand this better now. One way to do this is how you are trying to approach this, which i am not sure how to implement.
Another simpler way maybe a little tough to implement is as follows:

You can take a high level call as to what would be the max number of "item" nodes would be present. Suppose you think, you would never have more than say 10, you would want to use groovy to read the number of "item" from excel, populate the nodes from the starting and delete the remaining nodes. The catch is, that every time you run the request or for every iteration, you would need to "reset" the request xml to 10 empty nodes. I did implement a similar solution in one of my projects using nothing but groovy. If you are interested in this approach, let me know and i might be able to share the code snippets.

Regards,
Deepesh Jain

I am attempting to manage an identical situation. The request is an array - a checkout list that can contain 1 or more items. I am using Excel in rows where the item details for the checkout instance are repeated (rows 1-3, or rows 4-9) based upon the number of items being purchased. How can I get the data into the request? I can enter a single line item transaction but cannot find a method for adding addtional line items to the same transaction. Here is a sample of the multi-line item request that I need to manage: (this example has 2 line number sections but it can contain up to 15)

         <web:LineItems>
            <web:LineItem>
               <web:LineNumber>1</web:LineNumber>
               <web:ItemType>1</web:ItemType>
               <web:Division>024</web:Division>
               <web:ItemNumber>000010184</web:ItemNumber>
               <web:LineItemAmountTypeCode>1</web:LineItemAmountTypeCode>
               <web:DollarValuePreDisc>15.00</web:DollarValuePreDisc>
               <web:DollarValuePostDisc>14.00</web:DollarValuePostDisc>
               <web:PriceMatchAmount>0</web:PriceMatchAmount>
               <web:PriceMatchBonusAmount>0</web:PriceMatchBonusAmount>
               <web:Quantity>1</web:Quantity>
               <web:TaxAmount>0</web:TaxAmount>
            </web:LineItem>
         </web:LineItems>
         <web:LineItems>
            <web:LineItem>
               <web:LineNumber>2</web:LineNumber>
               <web:ItemType>1</web:ItemType>
               <web:Division>024</web:Division>
               <web:ItemNumber>000010184</web:ItemNumber>
               <web:LineItemAmountTypeCode>1</web:LineItemAmountTypeCode>
               <web:DollarValuePreDisc>15.00</web:DollarValuePreDisc>
               <web:DollarValuePostDisc>14.00</web:DollarValuePostDisc>
               <web:PriceMatchAmount>0</web:PriceMatchAmount>
               <web:PriceMatchBonusAmount>0</web:PriceMatchBonusAmount>
               <web:Quantity>1</web:Quantity>
               <web:TaxAmount>0</web:TaxAmount>
            </web:LineItem>
         </web:LineItems>

If anyone has source code for creating a datasource link to Excel from Groovy in the Groovy Test Step and can share it I would be grateful. I need to read in multiple data rows from an Excel spreadsheet, create an array of parameters and then feed the array to the XMLBuilder step to create a multi-line cash register transaction for testing of a Point of Sale system. I have tried several methods but have so far been unsuccessful.

If you have done this and will post your code I'll spring for the beverage of your choice!

Example on generating XML in Groovy:

--------------------------------------------------------
import groovy.xml.MarkupBuilder

def writer = new StringWriter()
def builder = new MarkupBuilder(writer)
builder.products {
[ [itemId: 1, name: 'Jacket'], [itemId: 2, name: 'Trousers'] ].each { entry ->
item {
number entry.itemId
name entry.name
}
}
footer( 'attr': 'value', 'Footer text' )
}

log.info writer.toString()
--------------------------------------------------------

The generated XML would be:

<products>
<item>
<number>1</number>
<name>Jacket</name>
</item>
<item>
<number>2</number>
<name>Trousers</name>
</item>
<footer attr='value'>Footer text</footer>
</products>


More details about the MarkupBuilder here:
http://groovy.codehaus.org/Creating+XML ... kupBuilder

Renato