How to update data in datatable using linq

Agentic Automation
,
1

Having data table and need to update Filename column using other columns data in data table using linq query.

Ex:

Input:

Invoice PO Filename
123 AC1
152
135 234

Output

Invoice PO Filename
123 AC1 123_AC1.xlsx
152 152_WithoutPO.xlsx
135 234 135_243.xlsx
2

@Manaswini_UI

please try this

dt = dt.AsEnumerable.Select(function(x) dt.LoadDataRow({x("Invoice"),x("PO"),x("Invoice").ToString+"_"+If(String.IsNullOrEmpty(x("PO").ToString),"WithoutPO",x("PO").ToString)+".xlsx"},false)).CopyToDataTable

cheers

3

giving error collection was modified; enumeration operation might not execute

4

@Manaswini_UI

can you please show a screenshot

alternately try using below in invoke code send dt as in/out argument

dt.AsEnumerable.ToList.ForEach(sub(r) r("Filename") = r("Invoice").ToString+"_"+If(String.IsNullOrEmpty(r("PO").ToString),"WithoutPO",r("PO").ToString)+".xlsx"))

cheers

5

Hi @Manaswini_UI

First Clone your dt using an assign activity
dt_Cloned = YourDT.Clone

And then use the linq given by Anil

YourDt= YourDt.AsEnumerable.Select(function(x) dt_Cloned.LoadDataRow({x("Invoice"),x("PO"),x("Invoice").ToString+"_"+If(String.IsNullOrEmpty(x("PO").ToString),"WithoutPO",x("PO").ToString)+".xlsx"},false)).CopyToDataTable

Try this!

6

Please kindly find the below two approaches and let me know what worked for you or any issues you have faced

** Using C# LINQ (UiPath Assign Activity)**

Approach 1 — Update rows directly

dt.AsEnumerable().ToList().ForEach(
    row => row["Filename"] = 
        string.IsNullOrWhiteSpace(row["PO"].ToString())
        ? $"{row["Invoice"]}_WithoutPO.xlsx"
        : $"{row["Invoice"]}_{row["PO"]}.xlsx"
);

This Approach will
Updates the existing datatable
Handles empty PO values
No new datatable needed

Approach 2 — Using LINQ Select (creates a new DataTable)

dt = dt.AsEnumerable().Select(row =>
{
    var invoice = row["Invoice"].ToString();
    var po = row["PO"].ToString();

    row["Filename"] = string.IsNullOrWhiteSpace(po)
        ? $"{invoice}_WithoutPO.xlsx"
        : $"{invoice}_{po}.xlsx";

    return row;
}).CopyToDataTable();

Please kindly try these two approaches and let me know.

Thanks,

7

Below query can be used to update datatable columns using other column values.

Step1:
Create a new dt_Temp and clone the dt_Original.
Assign: dt_temp = dt_Original.clone()

Step2:
Use assign activity:
Dt_Original = (From row In dt_Original.AsEnumerable()
Let inv = row(“Invoice”).ToString
Let po = If(String.IsNullOrEmpty(row(“PO”).ToString), “”, row(“PO”).ToString)
Select dt_Temp.Rows.Add(inv, row(“PO”), inv + “_” + po + “.xlsx”)
).CopyToDataTable()

Note: Only the columns mentioned in Object will come in the resultant DT.

8

updatedDt = (From m In dtMain.AsEnumerable()
Join o In dtOther.AsEnumerable() On m.Field(Of String)(“po”) Equals o.Field(Of String)(“po”)
Let newRow = dtMainClone.Rows.Add({m.Field(Of String)(“invoice”), m.Field(Of String)(“po”), o.Field(Of String)(“filename”)})
Select newRow).CopyToDataTable()