This site requires JavaScript to be enabled
IE BUMPER

Logo

Dear Tricentis qTest users,

If you would like to submit a Tricentis qTest ticket,

please use this support request form.

Tosca Version
IncidentLookup using list
Language
How can we help?

Solution Suggestions

Please enter your question to get suggestions.
  Help
IE BUMPER
Categories
Boolean Operators
OR or vertical bar symbol (|)
Finds a match if either the terms exist in a document (a union using sets).
AND
Finds a match if both terms exist in a document (an intersection of sets).
NOT, minus (-), or exclamation point (!)
Excludes documents that contain the term after NOT (a difference of sets).
Wildcards
Asterisk (*)
The asterisk symbol performs a multi character wildcard search.
Percent sign (%)
The percent sign performs a single character wildcard search.
Phrases
Quotation marks (")
Use quotation marks to search for an exact phrase with multiple terms.
For examples and more information look at our Knowledge Base: Advanced Search On The Support Portal

Bulk Upload and Download to the new Test Data Service via Excel

The new test data service doesn't provide bulk operations at the moment.  A lot of our customers are still using excel as a data source.

Attached excel sheet contains a macro to upload the content a work sheet to a test data repository and also to download the content of a tds type to a work sheet

 

Disclaimer: This is just a sample please be careful that you understand what you are doing before you use it in your environment. I don't guarantee that it will work in all situations and will not cause any unwanted side effects.


Situation

In order to use Test Data Management with Tricentis Tosca you need to first create records in your test data repository. This can of course be done using the SET TestData - Create & provide new record

But sometimes the Test Data is provided as excel sheet from business experts:

This is especially usefull if you want them to check if the values are correct and in line with their expectations. After they have given their approval TDM has to be prepated with all records.

In addition you might also want them to check and verify the data in your TDM easily via Excel.

Solution

Attached excel sheet contains two excel macros for upload and download. 

You can find starting point on the workbook. There you can see two routines BulkUpload and BulkDownload. The routines are just stubs - the actual work is done the respective classes.

Bulk Upload

BulkUpload takes up to 4 parameters. 

The first parameter is the number of the header row in your data sheet. The values in this row are used as attribute names of the records created in the test data service. In the sample attached to the guide the second row is used.

The second parameter is the worksheet - in our case we use the currently active worksheet. Change that if you want to use a specific work sheet.

The third parameter is the endpoint of the test data service. You need to change that to your server address - it is very unlikely that your test data service will locally ;)

The last parameter is the name of the repository you want to store the data. I am using data , which is the default repository. You can of course change this to your needs. 

The actual work is done the method Upload

Public Sub Upload(ByVal iHeaderRowIdx As Long, ByRef oWorksheet As Worksheet, Optional sTDSEndpoint As String = "http://localhost:81/testdataservice", Optional ByVal sTDSRepository As String = "data")
    Dim rowIdx As Long
    Dim LastRow As Long
    
    If oWorksheet Is Nothing Then
        MsgBox "Sorry no worksheet found! ", vbCritical + vbOKCancel, "Error"
        Exit Sub
    End If
    
    Set theWorksheet = oWorksheet
    mCategory = theWorksheet.Name
    
    SetHeader iHeaderRowIdx
    
    theTestDataService.TestDataServiceEndpoint = sTDSEndpoint
    theTestDataService.TestDataServiceRepository = sTDSRepository
    
    'only rows with content are considered
    LastRow = theWorksheet.Cells(theWorksheet.Rows.Count, "A").End(xlUp).Row
    
    If LastRow <= iHeaderRowIdx Then
        MsgBox "Sorry no content found!", vbCritical + vbOKCancel, "Error"
        Exit Sub
    End If

   

 

    'Delete Category before feeding data
    theTestDataService.DeleteCategory mCategory

   

 

    'loop through each row in the table
    For rowIdx = iHeaderRowIdx + 1 To LastRow
    
        Dim contentRow As Range
        Dim Json As Dictionary
        Set contentRow = oWorksheet.Rows(rowIdx)
        
       Set Json = createJson(contentRow)
       theTestDataService.CreateNewRecord JsonConverter.ConvertToJson(Json)
    Next
End Sub

The type or category of the record is taken from the name of the worksheet. You can change that of course. This version also assumes that existing data is deleted before data is uploaded. If you don't want to replace the data just comment the line. The rest of the code is pretty obvious. 

Every row with content in the first column is checked and a new record is uploaded. 

We are using a simple JSON object which looks like that.

{

"category": "user",

"consumed": false,

"data": {

"first": "John llD8SfQXi7QV",

"last": "VLQ0iZ",

"age": "23",

"state": "used"

}

}

By the way the attribute consumed  is reponsible for the lock status - since I want to use the data later on, i set it always to false which means unlocked

The method CreateJson handles the creation of json.

Note: There is almost no error handling implemented - i leave that to you, dear audience ;)

Bulk Download

Bulk Download is pretty simple as well. Like before the  routine in the workbook is just a stub - the actual work is done in the respective class.

The first parameter is the worksheet - in our case we use the currently active worksheet. Change that if you want to use a specific work sheet.

The second  parameter is the endpoint of the test data service. You need to change that to your server address - it is very unlikely that your test data service will locally ;)

The last parameter is the name of the repository you want to store the data. I am using data , which is the default repository. You can of course change this to your needs. 

Again looking into Download method reveals the inner working.

Public Sub Download(ByRef oWorksheet As Worksheet, Optional sTDSEndpoint As String = "http://localhost:81/testdataservice", Optional ByVal sTDSRepository As String = "data")
    Dim rowIdx As Long
    
    Dim JsonArray As Collection
    Dim JsonObject As Dictionary
        
    
    If oWorksheet Is Nothing Then
        MsgBox "Sorry no worksheet found!", , "Error"
        Exit Sub
    End If
    
    Set theWorksheet = oWorksheet
    
    theWorksheet.Cells.Clear
    mCategory = theWorksheet.Name
    
    theTestDataService.TestDataServiceEndpoint = sTDSEndpoint
    theTestDataService.TestDataServiceRepository = sTDSRepository
    
    Set JsonArray = JsonConverter.ParseJson(theTestDataService.FetchAllRecords(mCategory))
    
    Set theHeader = theWorksheet.Rows(1)
    
    rowIdx = 2
    For Each JsonObject In JsonArray
           
        AddRow rowIdx, JsonObject
        rowIdx = rowIdx + 1
    Next
    theWorksheet.Rows(1).AutoFilter
    theWorksheet.Columns.AutoFit
    
End Sub

The name of the active worksheet is used as type/category of the records you want to load into the worksheet. The rest is prettty self-explanatory.

Worth to mention is that i tried to create a readably table like

If you don't like the colors you can change it in the method AddCell - just search for lines with Style

I also added the unique link to the record in TDS - it could be potentially used later on to update changed values. I didn't do that but it is quite easy to implement.

 

[opt.] Reference

This sample use a VBA json converter i found on github https://github.com/VBA-tools/VBA-JSON 

'' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '
'
' Based originally on vba-json (with extensive changes)
' BSD license included below
'
' JSONLib, http://code.google.com/p/vba-json/
'
' Copyright (c) 2013, Ryo Yokoyama
' All rights reserved.
'
' Redistribution and use in source and binary forms, with or without
' modification, are permitted provided that the following conditions are met:
'     * Redistributions of source code must retain the above copyright
'       notice, this list of conditions and the following disclaimer.
'     * Redistributions in binary form must reproduce the above copyright
'       notice, this list of conditions and the following disclaimer in the
'       documentation and/or other materials provided with the distribution.
'     * Neither the name of the <organization> nor the
'       names of its contributors may be used to endorse or promote products
'       derived from this software without specific prior written permission.
'
' THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
' ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
' WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
' DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
' DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
' (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
' LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
' ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
' (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
' SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '

Attachments
1 0

Chase Oubre

Created: 2018-09-13 19:05:39

Thanks for this.  Just a few tips if anyone is struggling, youll need to update the TestDataService url and Repository in the marcos.  There is 3 places where you need to do this.  It is best to search the project with find.  Also using the step through debug feature in Macros was helpful in debugging.

0 0

Akash Srivastava

Created: 2019-06-06 18:55:27

I believe the same could be achived by creating a data parameterized Template for TDS and execute it based on the row count. This will avoid any Macro dependency then.

IE BUMPER

Tosca is the perfect solution

Optimize - Manage - Automate

Download Trial