Read Range Workbook activity slow if sheet contains formulas

Hi Experts

In an automation using the Read Range Workbook activity for reading an Excel range I have noticed that the activity is very slow (it takes 10 mins to read the sheet) if the sheet contains rows where column A does not contain any data and the following columns contains a formula, e.g. a VLOOKUP formula using input in column A and returning #N/A if nothing is entered in column A.

If I remove the “empty” rows that only contains the formulas the file is read within seconds.

Any idea why this is?

Hi @jacchr

When you have formulas in a column, Excel needs to calculate those formulas before returning the values. In your case, the VLOOKUP formula is likely being evaluated for each row, even if column A is empty. This calculation process can significantly slow down the reading operation.

Can you try reading the data using the Read Range activity instead of Read Range Workbook & see whether it makes any difference?

Hope this helps,
Best Regards.

@jacchr

Can you check in Read Range is the preserve format is enabled
If so, it will take some time
Also, how much data you have in the excel sheet

Thanks,
Srini

Hi @jacchr

This problem is not to be handled through UiPath, you should ensure the data given to the read range is clear and fast, you might standarize you excel file and then read it

Regards

In an ideal world, yes - but for this specific process these “empty” rows will occur randomly.

Hi @Srini84

It is less than 200 rows and maybe 5-6 “empty” rows only containing the formula. Unfortunately toggling the Preserve Format does not make any difference.

Hi @arjunshenoy

The sheet contains less than 200 rows and it might only be a couple of rows where column A is blank. If there are no rows only containing the VLOOKUP formula the file is read almost instantly.

Adding an Excel Application Scope and with a Read Range activity inside certainly makes a difference. It works both with or without the rows only containing the formula. However to my knowledge the Excel Application Scope/Read Range require that Excel is installed on the machine running the process. Hence the Read Range Workbook activity was used originally.

1 Like

@jacchr

Glad you figured it out. Happy Automation!

Best Regards.

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