I have .xlsb excel file with 50k records how to read in less time

Help Activities
, ,
1

Hi team

I have .xlsb file with 50k records, read range workbook activity is not supporting so im using excel read range activity which is taking so much time.( more than 10min )

What would be the other possibilities to make it read faster
Please help me with this.

Thanks
Praveena

2

@praveena.nadi Read in chunks like first read 10 thousand than next 10 to 20 and so on

3

There is no way around the simple fact that more data means more time to process it.

4

Hi,

Could you try to use Excel ad Db:

Using Excel As Database - News / Knowledge Base - UiPath Community Forum

Note: You will have to check if it works well with xlsb file.

5

@raja.arslankhan i need to get a data variable from other excel and compare it with all the records in this excel. So may not be possible to read in chunks.
By any chance, can i use oledb here ? If so, please guide me.

6

Taking lot more time actually, almost saw it ran for 20min atleast.

7

Hey,

If you have to look for a particular value in the excel data which is huge, i would recommend not reading it in first place and directly use Find/Replace Value & Vlookup activities in excel itself.

Thanks,
Prateek

8

@praveena.nadi i understood your problem but when you will read through chunks then you will also merge it in another table. In the end you will get proper whole datatable

9

@praveena.nadi

Can you please tell what kind of manipulations or search you are trying may be depending on that we can suggest any alternative approach

Also we can use excel as db and can use where conditions to get only required data or check only require ddtaa…you need to install drivers and then can get connection strings from here

Cheers

10

Hi @Anil_G @raja.arslankhan @prateek.mehandiratta9 ,

I have a CSV file with 25k records where im trying to get debit amount of each invoice number and search for the same invoice number respective debit amount in .xlsb file with 50k records, if match found, i need to update status in csv file.

this is the overall project.

here i am able to read csv file using read csv activity with very less time even with 25k records, but .xlsb file with 50k records using excel read range activity is taking lot of time (more than an hour)

Since i need to compare each and every record from csv file with .xlsb file, i want to make it as fast as possible. Please guide me with this briefly.

11

@praveena.nadi

Did you try reading only the invoice number column and invoice amount column separately ? even that is taking time?

if not read them separately and then we can merge together

lookup is an option but not sure for 25k records in loop it might take time…so try reading individual columns and then we can merge or use row index to find the total and invoice number as needed

cheers

12

In addition to this, As i am looping through each row using for each row in datatable, How to update status on excel sheet ?
because, if match found, I need to write status to the “status” column in csv excel file.

image

please help me with this.

13

@praveena.nadi

Use the for loop index to get the index of the looped row…and use the index in wrote cell "S" + varIndex

Cheers

14

its getting executed but not writing into the excel.

15

@raja.arslankhan @Anil_G

In this current project, I need to do group by invoice number and sum of debit amount column and then i have to compare it with other excel sheet’s debit amount.

input:

Excel1
invoice number Debit status
12345 500rs
12345 100rs
12345 400rs

Excel 2
invoice number Debit
12345 1000rs

I need to do the sum of all debit amounts for invoice number 12345 in excel1, then search for same invoice number 12345 in excel2 and compare respective debit amount. if match found, then update status on excel1.

please help me with

  1. how to do group by and sum and then compare with second excel file
  2. update the status in excel 1.

thannks

16

Hi @praveena.nadi

Instead of using the Excel Read Range activity, you can use the Excel Application Scope activity. This activity provides a more efficient way of working with Excel files as it utilizes the Excel application directly.
If the Excel file has the “Read-Only Recommended” option enabled, it can significantly slow down the reading process.

Thanks!!!

1 Like
17

@Anil_G , for updating the status in csv file, i cant use write cell activity since csv file can not be read using read range activity.
So what i can do for updating status in csv file ?
If i have to use write csv activity, then please guide me how to use this

18

@praveena.nadi

In modern excel activities I beleive write cell wpuld work on csv can you please check?

If not…in the loop directly update the status in the datatable and then write the data back to csv using write csv

Currentrow("Status") = "StatusUpdate"

Hoep this helps

Cheers

19

Write cell can only be used inside excel process scope, but here im not reading csv file with excel process scope, so may not be possible to use write cell activity.
In other way,
Im able to update status on status row in datatable but
Im not sure how to use write csv. Do i need to use inside loop so that it gets updated for every row from datatable or once datatable for loop is done, i should then write back to csv ? Please guide me with this.
If possible, with code.

Thanks

20

@praveena.nadi

After the loop just use write csv activity and pass the datatable to it and the file details and the data of csv would be over written

As the datatable is updated in the loop all the status row values would be updated at once on csv

Cheers

1 Like