Approach Manipulate excel with 10000 plus records

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!

1 Like

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

1 Like

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 :slight_smile:
ExcelExtensionActivities.nupkg (3.1 MB)

It’s pretty easy to use but let me know if you need any help with it!

7 Likes

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
2 Likes

Hi,
I think you should try to install my package on the machine where you are running your process, this should fix your issue :slight_smile:

I’m so glad you’re using the package and that its functioning correctly. I’m currently working on a big update for it :wink: . 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 :slight_smile:

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.