Hello,
I would like to set a variable using a value in a particular cell from a rowset created from ReadExcel task.
For example, I want to use a date value in Row 1, Column 4. How to I reference the cell to set the variable?

The only reference I can find is to the column index, as in ${ExcelForDate[4]}.
Is there a row index I can reference?

Thank you.

Hi toniduquette,


A Row Set variable is designed to be all inclusive to simplify the data translation process, but you can access data in a specific row of a rowset.

You can create a forEach loop with a Current Item Variable and a Current Iteration Variable (Found on the Basic and Advanced tabs respectively). The Current Item Variable ${currItem} is the current row, starting with the first and going to the last. The Current Iteration Variable counts the number of times the loop is processed, starting with 1.

So, based on the value of the iteration variable ${loopCount}, you can move the desired value of the Current Item Variable’s column 4 ${currItem[4]} into a variable with a Set Variable task. Below I have the condition statement within the Set Variable task (Control tab), but you could also use an IF statement to set this condition.


ForEach Loop Example:

<project name="C 35580 Test" mainModule="Main" version="2.0" logLevel="verbose">

<module name="Main">

<createWorkspace version="1.0" />


<readCSV inputFile="/wtmccall/MyTest.csv" outputRowSetVariable="rowSet" version="1.0" />

<forEachLoop itemsVariable="${rowSet}" currentItemVariable="currItem" currentIterationVariable="loopCount">

<setVariable label="Get Value of Row 3 Col 4" name="row3col4" value="${currItem[4]}" version="2.0" executeOnlyIf="${loopCount eq 3}" />

</forEachLoop>

<deleteWorkspace version="1.0" />

</module>

</project>