How to deal with OutOfMemoryException?

Help Studio
,
1

While reading a large excel sheet containing more than 350K rows and 150 columns, Outofmemoryexception is encountered. How to avoid this Exception? When does it occurs? Is it related to system RAM?

While debugging it was found that the exception is thrown after excel scope in Assign Activity where the LINQ is used to filter the Data table obtained from reading excel file. The LINQ query used is very simple one, which filters the data table based on one of the column values.

Note : The system on which I am running the project is equipped with 4GB of RAM. Also, in excel all the rows are written in one excel sheet only.

1 Like
2

Hi,
the Exception is raised when there’s not enough RAM to store the data of your DataTable. Performing a Select on a large DataTable can result in this exception.
Try the following:

DataView dv = New DataView (ThisDataTable)
dv.RowFilter = “[ColumnName] < 3”
FilteredDataTable = dv.ToTable ()

The RowFilter is an example - replace with your query. The FilteredDataTable should have the filtered rows you are looking for.

3

Thanks for the answer. How is this query different from the select query? Will it save memory? The query that I used is -

InputDT.AsEnumerable.Where(function (x) x("code").Equals("12300234")).copyToDataTable

4

Hi,
the DataView object just creates a view on the DataTable where you can perform queries.

I read about the LINQ AsEnumerable and found out that it pulls the whole source (here DataTable) into the memory.
See entity framework - What's the difference(s) between .ToList(), .AsEnumerable(), AsQueryable()? - Stack Overflow

Another solution would be to call the Where directly on the DataTable without AsEnumerable.

5

Second Scenario: The same data is available in an excel file with multiple sheets with each sheet containing around 65K rows. In order to convert the data into a data table I am using read range and merging the data tables so I get all the 350K data rows in one data table. After that I applied the query that I mentioned above. No outofmemoryexception was thrown. This scenario was run on the same system with 4GB RAM.