Posted: 19 Dec 2019 14:19 EST Last activity: 9 Jan 2020 2:22 EST
. Exception of type 'System.OutOfMemoryException'
Pega Open Span has the least memory Management Automation Tool which I have used so far.
PEGA could not read and reproduce an excel as a data table if the row crosses the 2 lakh count and the scenario vice versa.
Always receive the error message when I pass a datatable to excel or reading excel as datatable using PEGA excel component, if crosses 150000 rows.
Autx_IO_Main failed due to the Reason: Autx_IO_ReportsError opening 'C:\Users\Rjayendr\Desktop\Reports\11 Nov 2019\Bible\12-19-2019\Workings.xlsx' workbook. Exception of type 'System.OutOfMemoryException' was thrown..
***Edited by Moderator Marissa to update platform capability tags****
I was able to create a random Excel file with over 200k rows and it opened fine without error. I suspect this is a limitation with your hardware. Perhaps increasing the available RAM on your system would help. What version of Pega Robotics are you using? Does the connector work fine when you open smaller files? If so, I believe your limitation may be the memory on your machine and not anything specific to the connector.
One item that I could see as your problem might be that you are inadvertently opening the file twice. In the connector properties, if you specify a file name, that file will be opened on startup. If you have a file name in that property the connector will be open on startup and you do not need to call the open method. In my solutions, I generally leave this empty in the properties window and call the open method when I am ready. If you do specify a file name, then do not call the open method again (at least until you close the first file).
With that being said, and given your issues and the size of your file likely containing data not suitable for public posting, the forum is probably not the best place to resolve this issue for you. I would suggest that you open a support request and a member of our support team will contact you to work on this with you. If you do open a support request, please record the SR number here and the ultimate resolution can be posted here for the community.
Thank you, I will raise an SR for solution. Also please find the screenshot of my automation in the attachment for your reference. And I have another issue is we cannot handle two excel at a time. PEGA connectors does not identifies the excel and closes both the excel when I use the 'Close' option in Excel connector tool.
I am attaching a sample solution I used to validate this. I am now able to reproduce the issue with over 200k rows. I believe my reproduction last week had a flaw in it as I can clearly demonstrate the issue with the attached solution.
Incidentally, I am able to get a .Net exception when I generate a DataTable with over a specific number of rows (I think it's maybe 50k or so depending on the number of columns). Even if the connector works to open the file, processing one that large will likely run into this limit in .Net no matter what tool you use to process it.
I was able to get past the error by switching to the 64-bit Runtime. You can set the StartupProject property of the project in Visual Studio and point it to the 64-bit runtime as shown in the attached screenshot. When you deploy this, you'd need to make sure you were using the 64-of Runtime.
I am not exactly sure how you'd do that though, so you might need to do some further research. Unless you still need that script though, you can probably just remove it or comment out the section that isn't working.
If you do want assistance with that script, please open a new thread and provide the code for the script and we can take a look in the new thread.