Handling Extra Large Excel Files

Hi @supermanPunch : As you solved my previous post but I am experiencing the error again. I shall be very grateful if you look into the attached .xaml file and screenshot. I am really fed up, pl. humbly request to help me out.

Many thanks,


PP_FlowChart_Testing.xaml (25.0 KB)

Hi @Dr_Raza_Abidi_PhD ,

Could you try Changing the Highlighted Condition in If Statement in the below image to the below Condition :

String.IsNullOrWhiteSpace(row("Account Opening Date").ToString)

The Error occurs when there is an Empty String in the Account Opening date Column, but since it is already handled in the Outer If, there should be no conversion problem of the Date.

Let us know if you are still facing issues after you have changed the condition.

1 Like

@supermanPunch : Thanks a lot for your great help. Issue is resolved now but when I run this code on the excel file of 1 column and 205K rows, it perfectly fines and gives output in 28 seconds. But on the other hand, executing on excel file of 224 Columns and 205K rows, did not give me speedy output and keep on running and I manually terminate the process by clicking stop.

Is there any suggestion, please?

I received the following error on the file of 224 Columns and 205K rows.

Many thanks,

@Dr_Raza_Abidi_PhD ,

Let us get to know the Total Time it takes for the Execution to compare with the Real World Scenarios as the data is really huge (224 Columns).

But we could give a Try on keeping only the Required Columns that is needed to perform Operation at the beginning, then Combine the Other Columns after the Operation using Join Datatable Activity. Although the Performance would need to be measured.

Let us know what is the total time taken for this Current Scenario. We could Make a Separate Topic to Work on the Performance Solution.

1 Like

@supermanPunch: Thanks, following is the screenshot for your reference and it took almost 10 minutes to execute but failed and stopped with an exit code as mentioned in the screenshot.

Hi!

Perhaps the problem is caused by a large amount of data. Try to significantly reduce the table and start the robot.

If the issue does not occur, then the problem is not in the robot, but in the amount of data.

@Sergey_Petrenko : Yes, indeed, the problem is with large amount of data but it is my requirement to produce the outcome at 225th column with 205K rows. But as per @supermanPunch, I should take those columns which are required and afterwards, join the data table activities.

But If there is no any workaround so, definitely I will do this as suggested.

Many thanks,

1 Like

@supermanPunch : Pl. find below the screenshot of the current scenario I ran as per your instruction. It took 10 minutes to come up with an error writing the data into the same reading file of 224 columns:

I ran this file of 224 columns with 10,000 Rows, It perfectly works fine but when I ran on 205K rows terminates with an error exit code as mentioned in the screenshot.

Hi @Dr_Raza_Abidi_PhD ,

Just to be clear, Does the Error occur when writing the Datatable to the file ?

@supermanPunch : I don’t know but I think when writing into the same file It gives an error.

@Dr_Raza_Abidi_PhD , Could you Debug the workflow instead of running, we should be able to identify the point of Error at that time.

Hi @supermanPunch : Pl. find below the screenshot for your reference.

Process flow terminates at this point. Could not understand.

Many thanks,

Hi @Dr_Raza_Abidi_PhD ,

Could you add a Write Line with the value as row("ETB/NTB").ToString Before the Assign Activity like shown below.

This is just to confirm the value present.

Hi @supermanPunch : Pl. see the screenshot. It seems unusual behavior, sometimes showing the integer value and sometimes show the proper date.

@Dr_Raza_Abidi_PhD ,

The Integer part is also handled in the Try Catch Block. In the Catch Block we are converting the Integer or OADate value to it’s DateTime format.

But Could you make the process run and then show the Screenshot of the Output Panel after the Error is Thrown
Need to Check if there is any other value present which does not correspond to the date column.

As an alternate, I will work on using an If condition instead of a Try Catch.

Hi @supermanPunch : I am trying to do as advised by you but could you please tell me that how should I run the whole loop in one go because I am pressing step into (F11) one by one record and could not be possible to run 205K records one by one.

Pl. guide.

Many thanks,

@Dr_Raza_Abidi_PhD ,

Please Click on the Run Button and not the Debug.

image

@supermanPunch : I have run the file from Debug File and checked the Write Line showing data with no abnormalities seen but at the last gave the error with exit code. Pl. see the screenshot.

Thanks,

Hi @Dr_Raza_Abidi_PhD ,

I have made a Bit of modification to the workflow that I think is the one you’re referring to.

Since we only need the XL Scope for Reading the Data in Excel and Not Processing Datatable, I have kept the Processing of Values in Datatable outside of the Scope.

Also the Try catch block is Replaced with an If Condition Statement, just to avoid Error being thrown every now and then.

Let’s Check if this gives any performace benefit.

Check the Updated Workflow Below :

PP_FlowChart_Testing.xaml (30.0 KB)

@supermanPunch :

FYI, pl. :cold_sweat: :cold_sweat:

Again the same error.