How do I create data binding for a List Variable?

I have a variable of type List and want to set up that variable type in my data binding for one of my Test Suites. I’ve created an excel file, but when I try and execute my Test Suite, it reads the variable as a string instead of a List. I’m not sure how to create the data in excel, so it will read it as a list. Does anyone know how? Thanks.

6 Likes

Hi
Did you find a answer to this

Hi, I have this same question and issue.
I do know that I need to “Uncheck” the box that binds all values to ‘String’ for me able to assign my variable to a ‘List’.

The issue comes when defining the values within an excel, anything i put there (eg: “value1”,“value2” or [value1,value2] or other combinations) all result in the text being interpreted as a String. (note: if using [] in the text, the List variable adds its own brackets and then the console shows [[value1,value2]]) so not adding them is probably best.

I can’t get Katalon to interpret the excel cell text to be a List or a set of elements. It takes the entire text as a String and sets it as the ONLY (first) element.

Any help on syntax for the excel cell text to get Katalon to define it as List elements would be greatly helpful.
Thanks

No, you can’t do this.

Katalon Studio uses Apache POI internally to read the Excel file content. POI determines the way how an Excel cell is interpreted to a Java/Groovy object. See the following page:

And the behavior of POI library depends on the specification of Excel itself.

A Cell in Excel could have one of the following data types:

  • Float
  • Date
  • Calendar
  • String
  • Boolean

Please note, Excel does not have a Cell type “List”.

So if you typed in a Cell "value1","value2" or [value1,value2] — these are recognised as a String by Excel. Therefore POI recognises these as a String, and Katalon Studio just follows the same way.

If you want to have multiple values of a row in a sheet, you can make as many columns named valueX as you want like this:

name value1 value2 value3 value4
foo abc def
bar 12 34 78
baz yam

Let me show you a sample code which evaluates a String ["value1","value2","value3","value4"] into a List object in Groovy.

String expression = '''
	["value1", "value2", "value3", "value4"]
'''

GroovyShell sh = new GroovyShell()
Script script= sh.parse(expression)
Object result = script.run()
assert result instanceof List
println result

When I ran this, I got

2021-02-12 15:58:45.401 DEBUG testcase.StringToList                    - 5: assert result instanceof java.util.List
2021-02-12 15:58:45.405 DEBUG testcase.StringToList                    - 6: println(result)
[value1, value2, value3, value4]

However I would warn you that this code is very fragile, easily break. For example if I give

String expression = '''
    ["value]
'''

This sample code raises an exception:

2021-02-12 17:07:10.217 ERROR c.k.katalon.core.main.TestCaseExecutor   - ❌ Test Cases/StringToList FAILED.
Reason:
org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Script1.groovy: 2: expecting anything but ''\n''; got it anyway @ line 2, column 11.
   	["value1]
             ^

1 error

@mark.bain

Is this what you want?

@kazurayam
Thanks for this information. (sorry for the long wait in response).

Not quite. Let me restate the issue.

  1. I create a variable in my Katalon script and define it as a ‘Map’
  2. I use Data Bindings in my Test Suite script to map an Excel Cell to that variable (defined as MAP)

How can this be done?
EG:
Script Variable : fruitMap = [ : ] (empty map)
Test Suite Script : assign Excel sheet and tab to Script

  • Assign fruitMap to Excel column
    Excel Column contains : [ banana, orange, apple ] or [ fruit1 : banana, fruit2 : orange, fruit3 : apple]

Script code will now use variable “fruitMap” as a Map and index through it.

The Data Binding part is the part I can’t get to work. Either I am setting up the Binding part (assigning) incorrectly, or the value in the Excel Cell is not correct. (or something else, like not possible to do)

Hopefully that explains my issue better.

Let me confirm your idea. Please answer to the following questions.

Q1. You don’t want to make your Excel sheet look like this. do you?

A B C
fruit1 fruit2 fruit3
banana orange apple

Q2. You want to make your Excel sheet look like this. Am I right?

A B C
[banana,orange,apple ] [fruit:banana,fruit2:orange,fruit3:apple]

Q3. If I propose you to make your Excel sheet look like this, do you agree with it?

A B C
["banana","orange","apple"] ["fruit":"banana", "fruit2":"orange", "fruit3":"apple"]

I mean, you are asked to type many double quotes (") to enclose keys and values.

I expect that you would say
Q1 -> No
Q2 -> Yes
Q3 -> No

I suppose, you would repeat that you want to convert a single Excel cell value

[fruit:banana,fruit2:orange,fruit3:apple]

into a Groovy variable named fruitMap of the type Map<String, String> . Am I right?


I am not convinced that the Q1 data format is not enough for you.

In my humble opinion, the Q2 data format is not the way you should take in Excel. It ruins Excel.

Katalon Studio does not support such a text parsing and type conversion for the Q2 data -> Map.

That conversion would require a difficult programming. You need to develop a special Parser for yourself.

By the way, I introduced GroovyShell in the previous post. GroovyShell is a text Parser built in the Groovy runtime engine, and ready to use for everyone. GroovyShell can parse a String

["fruit":"banana", "fruit2":"orange", "fruit3":"apple"]

(which I proposed in the Q3) into a Map object. If you accept the Q3 data format, you do not need to reinvent a wheel.