Have any one tried maniupulating excel with more than 10000 transactions in a file. I want to understand on the datatable performance and will it be slower than macro
Hi!
Sometimes, handling large Excel Files can be a rather tricky task.
Depending on the size of the file and the number of rows, you might not be able to read it or write it all at once, so you might have to manipulate it in chunks.
Normally, a file with 10000+ rows should be read without issues using the Excel Application Scope . However, files larger than 100000+ rows can present some challenges, so those files should be processed in chunks:
EDIT: I also developed a custom activity that should give you the exact number of rows for an excel file (its been tried and tested a few times and it works on really large files ~ 1 mil. rows or even more). If you consider that you need it for processing your file in chunks, Iâd be more than happy to share it with you!
Thanks. If the idea can be shared please send me the details. i am analysing to compare the processing of large files in UIPath and Macros
Sure!
It mostly has the same parameters that an Excel Application Scope would use.
It currently only gives the number of rows for an excel file but in the future I plan on extending it into a full solution for reading and handling reaaally large excel files
ExcelExtensionActivities.nupkg (3.1 MB)
Itâs pretty easy to use but let me know if you need any help with it!
A big thank you for your package, I have been using it in a process and it has been working fine until I tried to install it without having UiPath Studio installed. What Iâm trying to do is to install and use the custom package to the Robot server but I am receiving Cannot create unknown type '{clr-namespace:ExcelSheetExtensionClasses;assembly=ExcelSheetExtensionClasses}GetNumberOfRows'
, have you encountered such issues @radu_bucur ?
Since you did some sharing let me do some sharing too: Gen_ReadExcelChunks.xaml (24.9 KB)
The component reads the large (tried with 750000+ rows, ~87 MB) Excel file in chunkss. The solution returns a DT and takes the following arguments:
- in_str_Path
- in_int_ChunkSize
- out_dt_Result
- in_int_StartRow
- in_str_StartColumn
- in_str_EndColumn
- in_str_SheetName
- in_int_StartRowNoHeader
Hi,
I think you should try to install my package on the machine where you are running your process, this should fix your issue
Iâm so glad youâre using the package and that its functioning correctly. Iâm currently working on a big update for it . Iâll make sure to post in here when everything is ready and working.
Let me know if you need any other assistance.
P.S. Thanks for providing a sample for reading in chunks, this will surely provide assistance to anyone who is currently struggling with oversized Excel Files
Hi again,
Thanks for helping out. Iâve tried to copy the nuget package on the Robot server (C:\Program Files (x86)\UiPath\Packages\ but no luck, still get the error. Do you have any further instructions you could supply for manually installing a custom package on a robot server that is connected to Orchestrator?
Hey,
Now that you copied the package to your robot machine, you also need to open the studio and install the package.
It should be easy enough, its just like you did when you first installed the package on the machine you developed the workflow.
Let me know if this helps
Thanks for answering. We donât have Studio installed on the robot machine, is that an issue for using external packages?
Read this, please. Try copying the ExcelExtensions folder from %ProgramData%UiPath\Actvities to the machine where your robot is operationg. Maybe thisâll work.
Hello I tried to split the data in single cell in Excel into multiple cells but dinât achieve the data in proper format. Can anyone help me with this issue ?
I am trying to execute your XAML and facing below issues.I have installed suggested package.
Could not find type âExcelSheetExtensionClasses.GetNumberOfRowsâ in assembly âExcelSheetExtensionClassesâ.
I have 20 lakhs records i.e 20 lakh rows in a file. Can Uipath help to do comparison of this large set of data ???
Help is very much appreciated
Hi Rinki,
Iâm not sure you can have 20 lakh (2 000 000 million) rows in one excel file. I think the maximum number of rows is under 1.1 millions. But it should be possible to read and process any valid excel file using UiPath.
If you cannot process the file with the standard UiPath activities because of the size, you can also try the following activity package I developed a while ago: https://go.uipath.com/component/read-extra-large-spreadsheets. I would recommend trying first with the standard Excel Application Scope first, though.