Copy contents in repeated sections in excel worksheet

Help Activities
, ,
1

Hi! I have an excel file that contains a few sections of data in repeated format:

image
image1009×682 18.8 KB

I want the bot to copy the contents in ColF and ColG to a new worksheet and append a new column to reflect the section. This is how I would like the new table in the worksheet to look like:

ColF ColG Section
Ans 91111111 A
Ans 92222222 A
Ans 93333333 A
Ans 94444444 A
Ans 95555555 A
Ans 96666666 A
Ans 81111111 B

How can I do this in UiPath? Thanks!

2

Hi @jac_q

First you need to get the cell addresses of start and stop of tables

for that what I see is ColA is the first column anme always so use find/Replace and find the cells which contain colA these will give you the start range to pick the data

then repeat the same for end. In end I see Cells: always present sos use it . it will give you cells. extract the number from it and remove 1 to get the last rows.

Now use these as range and read the excel and read the datatable and append the data as you need…While appending add a new column to give your section name

cheers

3

Hi @Anil_G , thanks for your reply! I can’t find the Find/Replace activity. Can you advise?

4

Hi @jac_q

Please see below…

Cheers

5

I can’t find the activity in UiPath Studio… Am i missing something?
image

6

HI @jac_q

Enable show modern and check it

image
image310×592 18.1 KB

Regards
Gokul

1 Like
7

Hi @jac_q

As shown above enable the classic activities to see that

And also are excel packages installed,?

Is the modern design enabled for you?

Cheers

8

yes, i’ve enabled modern design and it works now… thanks @Anil_G and @Gokul001 !

may i know how do i extract the number from the cell? which activity should i use?

9

HI @jac_q

You can try with Read Cell activity. In the range pass the cell reference "A3" something like this.

Regards
Gokul

10

Hi @jac_q

Are you asking how to extract number from the Cell returned? if yes then you just need to use a regular expression activity to get the number

else if you need the value then follow what @Gokul001 suggested

cheers

11

@Anil_G : I’m referring to your earlier reply →
then repeat the same for end. In end I see Cells: always present sos use it . it will give you cells. extract the number from it and remove 1 to get the last rows.

sorry, i get the following error. how do i set my variable?
image

12

HI @jac_q

Go to the output and do ctrl+k which will let you create a variable of the same kind needed as output

Here I can see you are trying to assign different type of variables.

To the step that I gave…say you got the cell A32 then do as below to get 32 out of it

image
image1418×834 15.3 KB

Type ctrl+k to give the new variable
image

then use result(0) to get the number of cell and to increment use Cint(result(0))+1 will give the next row value.

cheers
cheers

13

Thanks. For the find/replace activity, it returns the first occurrence of the value found. How do I get it to find the next occurrence of the value in each section?

14

Hi @jac_q

So once you have the first occurrence you have the cell value where it is found…so now give the range starting from the next cell… so that it now searches from the cell after that

Can you please mark solution if the issue is resolved that will help others with similar question

Cheers

15

Ah I see! Thank you! 1 more question - while appending, how do I add a new column to give it the section name? Which activity should I use?

16

@jac_q

You have add data column to add new column to the datatable but I am unsure of the exact question you asked.

Hope this is what you are asking for…

Cheers

17

I’m referring to your above reply. I tried add data column but it’s giving me error because every time it loops, it creates a new column which the column name already exists.

19

@jac_q

You will do add column only once…then you will use add data row to add the data of section as well

for each section you need not do add data column again, you will only do add data row

cheers

20

Ok thanks. How do I use it? I’m getting an error - “For Each Row in Data Table: Collection was modified; enumeration operation might not execute.”

image

21

@jac_q

Let me tell what you are doing …then you tell me if that is correct or not

You have a datatable and your looping through it and for every row you are adding one more row…

Cheers