Question
Hoverstate
US
Last activity: 14 Jul 2021 17:02 EDT
Unable to export large excel file to datatable beyond row 960670
RPA using excel connector. Part of the automation exports an original file to datatable to transfer into destination file. Some files are very large, one in question over 960000 rows. When I try to find the last row it will only find up to 960670th row. If I try to find last row with a range above that it returns only 0. If I try to export any table above that range it returns nothing.
Is this an upper limit to what pega robotics can handle? Do I need to write a C# script for this many rows?
edit - Today I tried loading the spreadsheet and looking for the end row of only column 1 between 900000 and 1000000 rows. It found row 958372 as the final row in the spreadsheet. I did an export to datatable for row1 between rows 958362 and 958382. It exported the actual last rows of the spreadsheet. It appears its losing rows somewhere.
The spreadsheet file is a tab delimited txt file, could that have something to do with it?
Pegasystems Inc.
US
@DanQuinlog If the file is tab-delimited, I would suggest processing it as a delimited file rather than with Excel. Save it as a CSV or other text file and your processing would likely be much faster. Once it is a delimited-text file, you can certainly work with it in an automation, however it would be much more efficient to do that sort of processing in a script (especially given the volume).
The Microsoft Excel connector is using Excel interop, so you would likely run into the same limitation in C#. You can try using the Excel Connector instead to see if that makes a difference as it uses an entirely different technology for interacting with Excel.
I am not specifically aware of upper limits specifically to Pega Robotics, but I do no Excel interop has some as this may be what you are getting. I tested a file with 1 million rows and could export it to a DataTable in a few seconds using the Excel connector. I would start with using that and see if that resolves your issue.
Hoverstate
US
@ThomasSasnettThank you for your reply.
I was unable to open the tab separated file with the Excel Connector in Pega Robot Studio. Is there a different method to open the file?
I am using the Excel Connector via Open Office XML, not the Microsoft Connector via COM PIA, is that what you mean in regards to interop? Should I use a different connector?
Pegasystems Inc.
US
@DanQuinlog If you are using the Excel connector then that is the correct one I was mentioning. If the file is actually tab-delimited, it should be a normal text file and open in Notepad (or any other text editor like Notepad++). In that case, you would need to use a script to convert it to a DataTable, as there is no specific utlity we have for that. You could possibly work with it in an automation using static methods from the Global Assembly Cache, however in this case, I would suggest a script for this purpose given the volume of records.
Updated: 14 Jul 2021 17:02 EDT
Hoverstate
US
@ThomasSasnettThank you again - I think that hits the nail on the head.
also I don't know why but I cannot accept your reply as the solution.