Question
Ford India PVT Ltd
IN
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****
Pegasystems Inc.
US
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.
Pegasystems Inc.
US
- What version of Pega Robotics are you using?
- Do you get the same error if you open a much smaller file?
- This will confirm that the code can open the file and handle it and that the maximum size is likely a limitation of your compuyter and its resources and not anything in the code.
Ford India PVT Ltd
IN
1. I am using Pega Version Proprietary information hidden.
2. There is no issues/error opening smaller files (Eg, same excel with lesser than 100000 rows).
1. Am able to open and manipulate the larger size file so ease in Visual Studio, with the OLEDB, ADODB and INTEROP methods. I have tried those scripts in PEGA. Resulting with 'Out of Memory' issues.
I landed here for some good solutions. But still getting only questions from PEGA team.
Pegasystems Inc.
US
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.
You may open a support request at https://msp.pega.com.
Ford India PVT Ltd
IN
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.
Pegasystems Inc.
US
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.
Pegasystems Inc.
US
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.
Pegasystems Inc.
US
It seems like this is an issue with your script code where it requires you to set the solution to run on an x64 processor.
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.