LINQ for Adding a Column and Conditionally Populating it with Data

[ashwin.ashok ((TheCodingTheory))]

I have a datatable as shown below in the screenshot:

First, it should position the new column “Department” in between “Name” and “Working Hours” (i.e.,at index 2).
As you can see, I have highlighted the “ID” column, and depending on the IDs, we have to populate the newly added “Department” column conditionally:
1,4 - IT(Yellow)
2 - Accounts(Green)
3 - Finance(Blue)
Given below is what the results should look like:

Help would be most appreciated, thanks in advance!

-Ashwin A.K

[Hiba_B (Hiba Beldi)]

Hi @ashwin.ashok !
So first use:

• Read range activity inside an excel application scope
• Insert/delete column, and specify the index where you want to add it and its name
• Foreach row in your datatable, switch case (with type argument as a string or int) and inside each case you put an assign row(“Department”) = "IT "(or the other values depending on the case)
• Write range with add headers checked

If you prefer, you can send me your sample so I could send you a xaml

[ashwin.ashok ((TheCodingTheory))]

Hi @Hiba_B ,

Thank you for your response, but I know how to achieve this results using UiPath activities.
I wanted to know how this can be achieved through LINQ (Lambda or Query)
Again, thank you for taking the time to respond to my query!

-Ashwin A.K

[Hiba_B (Hiba Beldi)]

Ah sorry ! I missed the info.
Is there a specific reason to use LINQ to do it ?

[ashwin.ashok ((TheCodingTheory))]

Hi @Hiba_B ,
Just wanted to explore LINQ, seeing how it is a lot more convenient than dragging in UiPath activities.
I noticed the performance improve drastically after incorporating LINQ into a recent Excel file filtering exercise which had over 300,000 entries that took anywhere between 1-2hrs to complete processing(For Each Activity is not as fast as I wish it was). I even went for the Macro/VBA route, but that took too much time aswell.
Then I tried LINQ, and it reduced the processing time from 1-2hrs down to less than a second.

I thought the process ran into an error, but when I opened up the file, I saw the data filtered as per expectation.

I was blown away, and now I have made it my life’s mission to learn anything and everything I can about LINQ!

[Hiba_B (Hiba Beldi)]

Sure, sometimes linq is the solution

[Lakshay_Verma]

Something like this will work for you
You have to amend as per your requirement
(From dr In dt.AsEnumerable Let newRecord= New Object(){dr(1),“ID”} Select dt.Rows.Add(newRecord)).CopyToDataTable

I took reference from here

Linq - Update Values in Datatable

@noam 1.update new value for all rows in a specific column - for example update all the values in column number 4 (no matter what was the old value) to new value You can play on provided xaml with following (Col 3 is updated with NewValue String) (From d In dtData.AsEnumerable Let ra= New Object(){d(0),d(1),“NewValue”} Select dtResult.Rows.Add(ra)).CopyToDataTable for second request have a look here: Reset_StatusCol_MultiContains_KwordList.xaml (8.0 KB)

[ppr (Peter Preuss)]

@ashwin.ashok

There are several Options. But lets create an empty datatable with the target column structure - dtResult

Option 1 - the lookup dictionary
Create a lookup dict (by datatable Input, manually…). Take the ID AS Key and the Department aber AS value - dictLK

New dictionary(of String, String) from {{“1”,“IT”},{…

Assign Activity
Left Side: dtResult
right Side

(From d in dtData.asEnumerable
Let dept = dictLK(d(0).tostring.trim)
Let ra = new Object(){d(0),d(1),dept, d(2)}
Select nr= dtResult.rows.add(ra)).CopyToDatatable

Option 2 could be a Join Statement in LINQ retrieving the Department Info from a Second table

[ashwin.ashok ((TheCodingTheory))]

@ppr

(From d in dtData.asEnumerable
Let dept = dictLK(d(0).tostring.trim)
Let ra = new Object(){d(0),d(1),dept, d(2)}
Select nr= dtResult.rows.add(ra)).CopyToDatatable

Wow, this works! Thanks alot @ppr !

[ashwin.ashok ((TheCodingTheory))]

Thank you @Lakshay_Verma ,
I appreciate your help!

[Lakshay_Verma]

This guy @ppr is genius.