LINQ to update columns in data table

[besakkiappan46]

Hi,

I have a requirement where I have to update all the values in a specific column in a datatable. I.e. I have a column “TIN” which has value like “81919010” , I have to update it with 819-19-010. This should be done to all the values in that column. I can’t use for each row since the record count is very high.
Is there any LinQ I could use?

P.S. I am using Windows, C# and not VB

Thanks in advance

[arjunshenoy]

Hi @besakkiappan46, welcome to the Community.

Please use the following query in the Invoke Code activity:

C#:

{
    yourDt.AsEnumerable.ToList.ForEach(row =>
    {
        row("TIN") = System.Text.RegularExpressions.Regex.Match(row("TIN").ToString().Trim(), @"(\d{3})(\d{2})(\d{3})").Groups[1].ToString() + "-" + System.Text.RegularExpressions.Regex.Match(row("TIN").ToString().Trim(), @"(\d{3})(\d{2})(\d{3})").Groups[2].ToString() + "-" + System.Text.RegularExpressions.Regex.Match(row("TIN").ToString().Trim(), @"(\d{3})(\d{2})(\d{3})").Groups[3].ToString();
    });
}

VB.Net:

yourDt.AsEnumerable.ToList.ForEach(Sub(row)
row("TIN") = System.Text.RegularExpressions.Regex.Match(row("TIN").ToString.Trim,"(\d{3})(\d{2})(\d{3})").Groups(1).ToString+"-"+System.Text.RegularExpressions.Regex.Match(row("TIN").ToString.Trim,"(\d{3})(\d{2})(\d{3})").Groups(2).ToString+"-"+System.Text.RegularExpressions.Regex.Match(row("TIN").ToString.Trim,"(\d{3})(\d{2})(\d{3})").Groups(3).ToString
End Sub)

You can use the following tool to convert VB.Net code to C#, for your reference:

Code Converter C# to VB and VB to C# – Telerik

Hope this helps,
Best Regards.

[besakkiappan46]

Thanks for the response Arjun.
Does this code insert “-” after 3rd and 5th character in string? Because, That is my requirement.
Also, What should be the Input argument for Invoke Code activity? It should just be my Datatable name on both sides ?

[arjunshenoy]

@besakkiappan46

Yes, it does insert the character ‘-’ as per your requirement.

Regarding arguments for the Invoke code, you can send the same data table in/out direction, so that the modified data will be saved in the same data table.

Hope this helps,
Best Regards.

[Yoichi]

Hi,

Can you try the following?

dt.AsEnumerable().ToList().ForEach( r => r["TIN"] =r["TIN"].ToString().Insert(5,"-").Insert(3,"-"));

Sample20230508-6CS.zip (3.0 KB)

Regards,

[besakkiappan46]

I get an error in the Invoke Code Arjun,

Error CS0119: “DatatableExtensions.AsEnumerable(Datatable) is a method, which is not valid in the given context at line 2”

[esakki1612]

This is the error I get arjun
I am not getting any error when I change it to VB in the invoke code and copy the VB code . Have you tried running the VB code as well ? Yielding the same result?

[supermanPunch (Arpan)]

Hi @besakkiappan46 ,

Maybe an alternate using Data Column Expressions could also work for your case. It does have the increase in the number of Steps to perform the operation and we could perform few selected operations, SUBSTRING being one of them which is your required operation.

However, do check if it fits your case.
DT_UpdateColumnValues_CSharp.zip (3.4 KB)

[Anil_G (Anil Gorthi)]

@besakkiappan46

Welcome to the community

You can try this in assign…this would convert whole of the column

Dt.Columns("ColumnName").Expression = SUBSTRING([ColumnName],0,3) + "'-'" + SUBSTRING([ColumnName],3,2) + "'-'" + SUBSTRING([ColumnName],5,3)

Cheers

[besakkiappan46]

The VB code which you shared is working as expected Arjun. C# faced an issue.

But just a small glitch in the Vb code.
You have provided fixed lengths right? 2,3,3. (i.e. 8 in total)
It isn’t working if the length of the value is more than 8. Irrespective of the length of the column value, I want it to put “-” after 3rd and 5th char.
Can you tweak the VB code a bit and let me know?

[arjunshenoy]

@besakkiappan46

Well, in that case, you can use the following query:

VB.Net:

yourDt.AsEnumerable.ToList.ForEach(Sub(row)
row("TIN") = row("TIN").ToString.Trim.Insert(3,"-").Insert(6,"-")
End Sub)

This will get you the desired result no matter the length of the value from the column “TIN”.

Hope this helps,
Best Regards.

[besakkiappan46]

I changed the Regex to this (\d{3})(\d{2})(\d+) Arjun. It worked.

[arjunshenoy]

@besakkiappan46

Glad you were able to figure it out. Happy Automation!

Best Regards.

[besakkiappan46]

Thanks a lot for your timely help Arjun
Happy Automation

Cheers,
Esakki