Issue while processing large excel data

[Sahil_Garg]

Dear Forum members,

I have to process one datatable which is having more then 2 lakh rows.
I have to visit each cell.
But for each row is taking many hours to process whole data.

Is there any way to process it in minutes ?

Any help will be appreciated.

Regards,
Sahil Garg

[supermanPunch (Arpan)]

@Sahil_Garg Can you explain what exactly is the operation that you are trying to perform ?

[Sahil_Garg]

Hi,

I have to change each cell value for a particular column, but total rows are more then 2 lakh

[supermanPunch (Arpan)]

@Sahil_Garg Can you please explain in Detail as to what is actually happening , so that we can help you in Optimising if at all it is possible.
Also For a Better approach of understanding the Problem, You can Provide us with the below requirements.

1. Sample Input Data.
2. Operation Being Performed.
3. Expected Output

[Sahil_Garg]

Apology buddy, data is confidential.

Assume there is Date column having 2 lakh rows of date and we have to convert format of date for each cell.

If i use row(“Date”) = NewDatestr
it is taking many hours.

can you suggest me any other solution so that many hours can be saved.

[supermanPunch (Arpan)]

@Sahil_Garg Can you check if you are able to solve the issue with the help of the below component ?

Novigo Solutions - DataTable Date Formatter - RPA Component | UiPath Marketplace

[Sahil_Garg]

Actually can’t use external activity

[NIVED_NAMBIAR (NIVED N)]

Hi @Sahil_Garg

For faster approach use the below code in invoke code with dt1 datatable as argument type in/out

dt1.AsEnumerable().ToList().ForEach(Sub(row)
row(“Date”)= NewDatestr)

Try this

Mark it as solution if it resolves ur query

Regards

Nived N

Happy Automation

[Sahil_Garg]

Hi Nived,

Your solution worked but it is still taking time to convert all.

Do you have any query for find and replace in data table without opening excel.

[Srini84 (Srinivas)]

@Sahil_Garg

Check below post for connecting excel as a database

How To Use Excel As Database – In UiPath

Once you finished your configuration, then you can use SELECT / UPDATE Query with Execute Query activity

Hope this helps you

Thanks

[Sahil_Garg]

Thanks you all for reply.

This post is help me for this problem .

LINQ query to update(replace)datatable in uipath

@Rachel7 this is maybe a too quick shot as it replace any space in any column to NA and returns a datatable. But you can give a try (From r In dtData.AsEnumerable Select ia = r.ItemArray.toList Select ic = ia.ConvertAll(Function (e) e.ToString.Trim.Replace(" ",“NA”)).toArray() Select dtCorrected.Rows.Add(ic)).CopyToDataTable() NEW VERSION, simplified: (From r In dtData.AsEnumerable let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace(" ",“NA”)).toArray() Select dtCorrected.…