Working with Excel as a database
Some process automation tasks require the creation of bots that repeatedly read data from Excel spreadsheets or, conversely, write them into tables. Thus, the bot will constantly refer to the document using Excel as a database. Sometimes this can lead to errors or stop the bot from working.
The following errors may occur:
"Selectors: Exception of type \"System.OutOfMemoryException\".\r\n"
"Selectors: Not enough memory."
Such behavior of the bot is quite expected. The point is that Microsoft Excel is not initially suitable for working with very large amounts of data. Despite the fact that Excel allows for handling data tables quickly and easily, it still cannot be used as a full-fledged database.
So, if you have the task of automating work with a large amount of data, the best solution would be to use full-fledged databases or work with the CSV format.
Note. It is better to divide large CSV files (more than 1.5-2 GB) into smaller ones. This will increase the performance and stability of the activity. Also, large files can be separately loaded into the database and processed with the help of the tools of the database itself.
Another reason for a high memory consumption can be the situation where an operation of writing to a file is done in a loop. Writing something to a file does not consume a lot of resources itself when done once but if you do it a lot of times for a very short period of time (which may happen with large loops), it may lead to the mentioned error. In such case, it is recommended to use variables in the loop to store the value and then write the resulting value to the desired file after the loop.