Optimize iterations in Excel

When working with Excel files, If you encounter errors such as “Selectors: An exception of the \”System.OutOfMemoryException\” type was thrown” OR “Selectors: Not enough memory,” it means that there is not enough memory in the system for a large number of calls to Excel

Why does this happen?

Large number of iterations with Excel can cause this behavior. Excel is a binary file that holds information about all the worksheets in a workbook. Excel consumes more memory while importing data.

How do we fix this?

There are several ways to optimize iterations in Excel:

  1. Reading all data from Excel and saving that data to a variable which can be used for iterations;

  2. Reading data from .CSV file instead of Excel document;

  3. Reading data from a Database (MySQL, PostgreSQL, MSSQL, Oracle).

1 Like