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.
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?
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
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.
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.