Iterate datatable and update column value by iterating the list using linq query

[Demo_User]

I have datatable and need to update the all value of a particular column with different values

The values to be updated is in the list

List count varies during run time

Datatable-io_dt
List-lstupdatevalue

io_dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“ColumnB”) = lstupdatevalue.AsEnumerable.Tolist.where(function (x) row(“ ColumnB”).Tostring.replace(x,””).tostring
End sub

This is not working as expected

For example

This the datatable
Input -
Io_dt =
ColumnA ColumnB
1234. Tender.@
1235 Timple,₹

Lstupdatevalue={@,₹,!,.,}

Ouput required

ColumnA ColumnB
1234 Tender
1235 Timple

[Anil_G (Anil Gorthi)]

@Demo_User

What value in the list maps to what row in the datatable?

If the pist count is less or more than row count what to do?

Cheers

[Demo_User]

Always the list will have atleast one value

For all the values of row all the same values in the list to update

For example

This the datatable
Input -
Io_dt =
ColumnA ColumnB
1234. Tender.@
1235 Timple,₹

Lstupdatevalue={@,₹,!,.,}

Ouput required

ColumnA ColumnB
1234 Tender
1235 Timple

[Anil_G (Anil Gorthi)]

@Demo_User

If i understand correct you want to replace all the values in the list in the datatable cells with empty strings?

If yes try this in invoke code and send dt as in/out argument

Dt.AsEnumerable.ToList.ForEach(sub(r) r.itemArray.Select(function(x) listvar.Select(function(y) x.ToString.Replace(y,""))))

Cheers

[Darwin_Harishjoan_A]

Hi @Demo_User ,

Please check the below Solution,

Linq query: (From row In Dt_Build
Let cnt =New Object(){row(“A”).ToString, System.Text.RegularExpressions.Regex.Match(row(“B”).ToString,“\w”).ToString.Trim}
Select CloneDt.Rows.Add(cnt)).CopyToDataTable

My input DT(Build Dt)

Source Code

Output :

[Demo_User]

I need to update only the particular column

[Ajay_Mishra (Ajay Mahendra Mishra)]

Hey @Demo_User I got you!

Here is the Output:

In Below Example I have 3 Columns My Logic can be use for multiple column as well(Whole Datatable)

LinQ:

CurrentRow.Table.Columns.Cast(Of DataColumn).AsEnumerable().Select(Function(x) x.ToString).ToArray().Where(Function(y) Regex.IsMatch(CurrentRow(y).ToString,"[^\w\s]")).ToArray.AsEnumerable().Select(Function(z) CurrentRow.Table.Columns.Item(z).Ordinal).ToArray

Note: Pass above LinQ in ForEach

Steps:

Screenshot for your reference:

Attaching .xaml for your reference:
UpdateColumnValue_Sequence.xaml (11.5 KB)

Input Output File:
Input.xlsx (9.2 KB)

If you have any query then let me know!

Regards,
Ajay Mishra