Handling Extra Large Excel Files

@Dr_Raza_Abidi_PhD ,

I do believe you did mention that there are 205 Columns as well ?

If so, Could you provide us with the sample data containing all the Columns.

Since the Last Sample had only one Column.

If the reason is due to the large number of Columns then we would need to Split it and then Process it Separately and then Join.

Also, Mention the Columns that are needed for Processing.

Yes, indeed @supermanPunch, I have also mentioned at the beginning of this post that with one column it is perfectly fine and give the output in 28 seconds with 205K rows but when I apply the same workflow on 224 columns it terminates the process after 10-15 minutes with the exit code.

Up till 10,000 records with 224 columns, the workflow you shared perfectly works fine within 45 seconds. But for 205K rows giving this error.

@Dr_Raza_Abidi_PhD ,

I have modified the workflow to Check if an Invoke Code Update would be able to overcome this issue.
image

Check the Updated workflow Below :
PP_FlowChart_Testing.xaml (42.7 KB)

Let us know if this helps.

@supermanPunch: Pl. find below the error message. Pl. guide how to solve it. Many Thanks,

Hi @Dr_Raza_Abidi_PhD,

There was another thread which asked a similar question regarding handling large excel files.

Why not change the approach to using a SQL stored procedure instead? It will be much easier to write / maintain queries and performance will be really good as well. Let SQL handle the difficult part of the data manipulation/transformation and UiPath the process automation part. More on how to use stored procedures in UiPath here :

@Dr_Raza_Abidi_PhD ,

Take a Look at the below post where a Similar Error was resolved.

Hi @jeevith: Indeed, you are right. SQL is a good option for handling big data. Also, @Palaniyappan suggested me one video of Cristian about handling of big data csv file through SQL ODBC connection. But at the moment, I cannot connect with the database due to some other department dependencies and approvals.

We are using Impala and Hive engines through Hue Editor and I have also downloaded the Cloudera-ODBC-Driver-for-Impala but at the moment cannot use it. Moreover, @supermanPunch very much helped me on this post through various workaround e.g., Invoke Code Activity, and many more etc. I really appreciate his untiring support and help and he also shared me a post regarding “Invoke code issue - Method not found: Microsoft.CodeAnalysis.SyntaxTree” but unfortunately could not solve my problem.

My workflow actually terminates dealing with 205K rows with 224 Columns and works fine with 10K rows with 224 Columns. I am trying to reduce the fields as much as possible but I think problem is with the rows not the columns.

Many thanks,

Hi @Dr_Raza_Abidi_PhD ,

Apologies for the Late post. Yes, It is true that SQL is the best Option to handle large data such as the ones that you are currently handling.

If the Database option is not suitable for your environment, Kindly Try to resolve the Invoke Code Issue , as the use of Invoke Code would be needed in further automations. It is a very much useful activity as well, where we can import codes which can perform operations that isn’t available handily as an activity.

For Instance, Could you try to Write a Simple Code inside the Invoke Code Activity to just Check if the Error Shows up, like the below Code :
Console.WriteLine("Hello")