RPA bot takes empty entries from excel file

RPA BOT keep on taking empty entries from excel file despite using format 2-*

Please refer to below screenshot.
image

I want my BOT to stop at the last valid entry, but it keeps on taking empty entries after the last valid entry.

image

The BOT keeps on taking highlighted in yellow empty spaces or columns in the JIRA search bar.

Is there any way we can use something like " END " in the excel file so that when BOT reaches to this work, it will just end the operations.

2-* means filtering the list such that elements starts from the 2nd index to the end of the list. In this case, we do not know how long is your tickets (list), the bot is simply trying to iterate for every element in that list.

Perhaps a better way to get only valid entries is to use a while loop. For example. while the current ticket is NOT empty, do action and then move the active cell downwards to the next cell. This will continue until the current ticket takes an empty value, the while loop terminates.

Note that you can use other conditions in that while loop if you want to check the current ticket is valid.

Hi Tin,

Thank you let me try that.

Hello, would like to add in that if you are using back-end excel function like Get column, it will automatically stop at the last non-empty entry, so you don’t have to set a filter for the for each loop.

Hi Tin and A_T ,

It would be great if you can make a sample code for me , am feeling bit confused.

Thanks a lot.

You can get value of a whole column using the Get Column action in back-end excel function, heres a simple get column function if you want to get the values of the first column and save them into a list.
get row excel.rar (453 Bytes)

Alright , thanks.

so it will take tickets from the list one by one and process it , so in this case as advised by do , we don’t need to use any filter condition like 2-* ?

Correct, you don’t need filtering since the get column function automatically stops at the last valid data point.

Great , let me try that.

Thanks dear for the prompt response.

I will be using the Filter 2-

As my tickets are starting from the second column.

Since the fist clolumn is heading only which i dont need.

So is the filter is correct - 2- ?

In that case filter condition should be 2-*

I am using that only , but it is taking empty columns from the excel file,

Pls check below.

image

Some times it takes empty spaces from the excel file * highlighted in yellow *

In that case what we are doing is , we manually have to delete the empty rows

Tin , would be great if you can make a small sample code , so that i can try that as well.

It worked fine for me, can you try deleting the empty rows and then try the script again?

1 Like

Yes , that is what we are doing at the moment , which is bit annoying but is working fine.

1 Like

Hi Karan,

Here’s an example of how I would do it. This script iterates through column A and checks if the current cell is NOT empty. It will then update the remarks column with a string from the sample_remark variable. This repeats until the current cell in column A is empty. The bot will then terminate.

Things you need to change in your end:

  • Change the path for “Open Spreadsheet” to the sample excel file that I’ve included.
  • Change the variables inside the recording to fit your needs.

The recording has been tested on v2.2 so if you’re using a newer version, you may need to change the headings of the .rpae file.

Hope this helps

sample.xlsx (6.1 KB)
IterateRowExcel.zip (2.6 KB)

2 Likes

Thank you so much Tin , let me try the same as advised by you.

will be updating you shortly on the outcome.

1 Like