How to use Excel to start and write-back(Filter & Datatable Problems)

Help Studio
, , , , ,
1

Hi There!
The purpose of task:
I would like to read Excel file at first(10 company) and go through a loop processes(5 steps)
So all company will perform step 1 and write-back the outcome to Excel file
According to the outcome, RPA will filter those successful companies in step 1 and move forward to step 2 and so on…

The problem I encountered:
I know the output of Read Range activity is kind of temporary Data table not the Excel file itself. Here comes the problem, for example, if there are 5 companies successful in step 1, and RPA will Read Range, do Filter Datatable, and perform step 2. But how do I write-back the result of step 2 to the original Excel file since the source file of step 2 is a filtered Datatable.

The following are pictures to illustrate my idea.
Before step 1:
image

After step 1:
image

Those companies will go to step2:
image

Thank you!!

2

Hey Danny,

Instead of filtering the data table, can you use a for each row and update the values from the original dt IF they meet the success criteria for step 1?

1 Like
3

I think you can start by checking if the DataTable.Rows object has an index property you can use.

If not you can follow this simple workaround

  1. Load the full Excel sheet into DataTable DT
  2. Add a column to the DT using the Add Data Column Activity - maybe call this column “ROW_ID”
  3. Initialize a counter - if you want the row position to be true to the Excel sheet, then your first data row starts at 2
  4. Use the For Each construct
  5. Update the ROW_ID column with Counter

Once you do that your DataTable in memory has reference to the Row position in the Excel.
After you filter the DataTable, the ROW_ID will be part of the filtered Rows and you can always use this to refer back to your Excel and update one or more cells on a particular Row.

Thanks!

1 Like
4

@Thomas_Mitchell Thank you for your prompt reply!
I’m sorry. I couldn’t understand your idea exactly. :frowning:
If I don’t use filter how do I prevent those failed in step 1 companies from going to step 2 ?
I’m not good at using Excel activity in Uipath.

Thank you!

5

@AndyMenon Hello! Thank you for your information.
This is my understanding about what you mentioned.
I will create an additional column in order to store the row positions of original Excel file.
And when I want to write back to the original Excel file, maybe I will use Write Cell and the range of it will be like “C”+ROW_ID column.Tostring so that the outcome will be true to the original file, am I right?

I will try it and tell you the result!
Thank you!

6

Hey Danny,

here is an example.

Step2Conditional
Step2Conditional787×670 17.4 KB

Then you could process the entire DT and write the whole table to excel.

1 Like
7

Thank you! It’s very clear!
And I had never design my processes like yours. I put all the processes(steps) in one single file. lol
I will try your method and tell you the result!
Thank you again!

8

Hello @DannySpace ,

Yep! I followed this process because it was easier to refer to rows by a common numeric ID when reading or writing from a particular row or cell.

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.