Lookup Datatable activity taking longer time to fill the excel column values

Help Activities
, ,
1

Hi Friends,

I have an **Excel file-1.xlsx (3.0 MB) ** to be filled. I have data in excel file-2. Excel file-2.xlsx (112.2 KB) I want to fetch the data from excel file-2 and fill it in excel-1 And I am using LookUp Datatable activity to fill the data(clientwise data).

The robot is able to fill the data but it is taking longer time which is more than an hour for just 5 clients

Now I want to fill the data for 60 clients… I don’t know how to proceed further.

In my code, I have used only one LookUp Datatable activity to fill the 5 clients data (which is around 459 rows).

My Excel file-1 & Excel file-2 are attached below for your reference.

Note:
Excel file-1 : I want to fill the column P in this file.

image
image1893×980 94.7 KB

excel file-2 :
Column P values to be filled based on SALES PREMIUM column from the file.

Excel file-2 shown below for your reference

image
image1812×707 38.2 KB

I have filled only 5 clients using single lookUp datatable activity. but taking longer time.

The five clients data to be filled are

1.Erafone
2.XL-Era
3. JB
4.PPP(Indosat)
5. HCI(Home credit)

Excel file-2 has all the formulas, which I wanted to fill it into excel-1 columns
For eg., For Erafone, I want to fill it in column P(GMV Erafone) in my excel file-1

similarly, for XL-Era client, I want to fill GMV XL-Era which is Column AC

image
image1535×972 62.6 KB

image
image1621×962 74.4 KB

Please do the needful I want to do it for 60 clients. Please provide me some idea on how to fill for 60 clients using single lookup datatable activity in less time

@aanandsanraj @balupad14 @Palaniyappan

Thanks

2

@eshwarsai_ks Can you also provide a sample output file for the Input files that you have provided, It will help us to understand the logic to be implemented in a faster way.

1 Like
3

Please find the sample output file attachedOUTPUT_.xlsx (1.1 MB)

Please note that column P (GMV Erafone) for the client Erafone is formula based values.

similarly, for other clients (XL-Era, JB,PPP(Indosat),& so on…), the columns to be filled was
GMV XL-Era(column AC)
GMV JB (column CC)
GMV PPP(Indosat) (column EB)

4

Hi @eshwarsai_ks,

I have created a logic to update formula for a client check out the xaml code
Main.xaml (11.0 KB)

1 Like
5

Thank you so much for your support @aanandsanraj. I would like to know one suggestion from you. Which one of the below points will give faster in order to fill values from one excel to another excel ?

1. Using Single Lookup Datatable to fill values in multiple columns
2. Using multiple look up Datatable activities to fill values in multiple columns

If I use LookUp DataTable for once to fill values for more than one columns, it is taking longer time which is 30min (only for 3 columns with 459 rows each)

Would it be a good option to fill data from one excel to another excel using LookUp Datatable activity??

May I know any other options do we have in UiPath to fetch data from one excel and fill other columns in 2nd excel file?

Lookup Datatable is giving accurate results but taking so long to fill (just tested for 5 clients, it took nearly 30minutes, i want to do it for 60 clients, so I can’t imagine how much time will it take )

Please suggest me on the same.

Thank you

6

I will check the workflow and get back to you asap @aanandsanraj

7

@eshwarsai_ks

Instead of use lookup between the two datatable better use filter activity to find the specific value in the column. Then use for each loop to update the value it will be very fast and bot will complete the process in few seconds.

I created the xaml to update formula directly on the excel instead of table. If you want to update on the table just modify the code where formula is writing.

1 Like
8

Thank You @aanandsanraj I am going through the code. I will get back to you shortly :slight_smile:

9

Hi @aanandsanraj , It supposed to start filling data from the cell P5.instead it is filling from P4 and it is filling data from the wrong column

image
image1723×825 62.3 KB

as there was no formula for this column in the source file

image
image1907×679 60.7 KB

SOurce file : Excel-file-2

image
image1136×388 12.8 KB

10

Also, It is taking 15 minutes to fill for just one column which is for ERAFONE.

I have 60 clients @aanandsanraj. In order to fill all the 60, it will take so much time na?

Any other possibilities other than this method?

11

@eshwarsai_ks

Try this Main.xaml (13.5 KB)

1 Like
12

Thank you very much @aanandsanraj it worked in just 2:52 mins But I need to test it for other 59 clients as well. I will make use of the same logic to apply for all the clients and put it in a loop.

May I know whether can I use the same logic instead of LookUp Datatable?? Because I have other excel files to search for a data like above and then get the values and then need to fill the other columns. some columns are from one file and few of the columns from different excel files.

I’m thinking to make use of the same logic instead of using LookUp Datatable activity.

Any suggestions on this??

13

@eshwarsai_ks

As I already mentioned use loop activity for these kind of logic. Lookup is very slow and use it in the correct scenario will help. So my suggestion would be loop activity.

One thing I have to tell you is I just updated only one column in the loop, you can update as many as column in that. So you can update all the clients value in the loop and it will not take much time. I hope it will be completed within 5 mins for all 60 clients.

1 Like
14

Thank you so much for your time and suggestions @aanandsanraj

15

I have another excel file like below where I am referring to column Row Labels and getting the values of “SUM OF AMS MARGIN”, “SUM OF UW COST” & SUM OF QTY

image
image1192×711 39.5 KB

I want to fill the above values in Excel-File - 1.xlsx by taking column Type as my unique value and fill the above 3 columns values in my ExcelFile-1 in columnD, Col F and Col H.

Excel file-1.xlsx :

image
image1817×642 36.6 KB

Can I use the same logic?? If yes, Where do I need changes. PLease support on the same.

Thank you

16

@eshwarsai_ks

Yes you can use the same logic. Just go through the code and try to understand the same. In the for each loop you have to modify the code for your new excel.

1 Like
17

Thank you. @aanandsanraj will do it :+1: