Excel Table To Variables

excel
excel-iteration
excel-loop

#1

For a Quick POC, I wanted to read an Excel table that contained three columns, first name, middle name, and last
name. Then I wanted to put those into variables to use further in the POC (which would including posting to a form and then also submitting to a separate URL). Excel structure looked as follows:

image

I used the Get Range function to extract the table (rows 2–>n), and then the FORloop to iterate the rows. For each row, I wanted to set the value of a temporary variable to match the name of the column (for use later). in this case:
firstname=(column 1)
middlename=(column 2)
lastname=(column 3)

The Clipboard Copy Action does not have an Index # field like the Enter Text action, and I became stumped on how to accomplish this. Eventually I resorted to using a nested FOR loop and then based upon the index of the inner look copied to the temporary variable I wanted.

While this worked, It’s hard to believe this is the proper way to accomplish this. Anyone have a better solution?

Also, is there any intent on adding Index # support to the Clipboard Copy function when using a list variable?

Below is the actions I used to perform this for those interested. While this works for 3 columns, it will not work effectivly for 10-15 columns.

Any thoughts would be appreciated.


#2

Hi @haider_raza,
You can use filter field to use indexes as shown below:

  1. You copy your data to a table variable
  2. Then use a For Each loop to iterate through rows (your list item)
  3. Use another for each loop to iterate through the row and use a filter (as shown in the image below) to specify which column/index you are referring to.

We do plan to support indexes in the future for sure.
Hope this helps.


#3

Thanks for the speedy response. However, this still means that I need to have a for loop for each column with a filter and then do a copy to clipboard and a paste from clipboard - correct?

Having an Index # option on the Copy from Clipboard would allow me to do 10 direct assignments, instead of 10 for loops with filters.

Just want to make sure I understand correctly.

Thanks


#4

@haider_raza,

You can use a temporary variable in the Constant Value action and use the following syntax to get a list element:

${item[0]}

or

${item[5]}

This feature is experimental but should work fine in 1.1.6


#5

good day

Is there perhaps a way to use the filter option in excel from express directly?

regards


#6

Hello @gerhardus_meyer please refer to the comments in this topic.
Also please note that RPAx does not have a separate Filter Excel action.

Best Regards,
Ivan Vezhnavets