topic RE: Problems with applying formula in large Excel Worksheet in Product Forum

Problems with applying formula in large Excel Worksheet

EvangelinaCurró — Mon, 28 Mar 2022 12:34:00 GMT

Hi!

I'm trying to modify an Excel file (120.000 rows aprox.) but every solution I tried are slow and uses lots of memory (in case of save data in collections), I read about a VBO's named "SPGMI - MS Excel Booster", with a function named "Apply formula in Range". It doesn't work, or I don't really know how.
Could somebody explain to me how "Apply formula in Range" works? Or give me an example of that?

I need to modify and apply formula in an entire column. If you know another way to solve this, every help is welcome.

Thanks!

------------------------------
Evangelina Curró
------------------------------

RE: Problems with applying formula in large Excel Worksheet

PvD_SE — Mon, 28 Mar 2022 15:07:00 GMT

Hi E,

Depending on what formula you want to apply and if you want to extract data as a result of the formula, I'd suggest taking a peek at OLEDB. This would allow you to run a classic SQL select against an XL file, where you extract a subset of the data into a collection. I do not think this works if you only want to modify the XL content by a formula. But it does help applying a filter to data that you want to have in a collection in your process.

In general BP does not fancy working with importing large chunks of data from an XL or CSV and will crash regularly with combinations of larger numbers of rows and/or columns. The OLEDB function works much better and much faster in this respect.

You'll fins instructions on how to use OLEDB here.

------------------------------
Happy coding!
Paul
Sweden
------------------------------

RE: Problems with applying formula in large Excel Worksheet

ewilson — Mon, 28 Mar 2022 16:02:00 GMT

FWIW - We released an update to the Utility - File Management VBO back in February that includes new action, named Get CSV Text as Collection - Chunked, for reading large CSV's (200K+ rows) in a chunked fashion. That way you eliminate memory exceptions.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Problems with applying formula in large Excel Worksheet

EvangelinaCurró — Mon, 28 Mar 2022 17:58:00 GMT

Hello Paul!

I need to modify a complete column using the left function and replacing "-" to "/".
After that, I need to apply formula related to another XL books (IF (nested) and VLOOKUP functions).

Could I use OLEDB for that?

Thank You So Much!

------------------------------
Evangelina Curró
------------------------------

RE: Problems with applying formula in large Excel Worksheet

PvD_SE — Tue, 29 Mar 2022 10:08:00 GMT

Hi E,

Don't think OLEDB can be used to an existing XL without opening it in a BP process*. My proposal was based on that the data was required in a BP process and OLEDB would be used to get large blocks of data to a collection without encountering OutOfMemory errors. I assume that while extracting data with OLEDB, you could modify it in transit before it will be put into a collection, like you can with SQL*.

* Experts: please confirm or deny

Counter question:
Just curious: Why would you be modifying the contents of an XL without extracting the data to a BP process?

------------------------------
Happy coding!
Paul
Sweden
------------------------------