LINQ for Adding a Column and Conditionally Populating it with Data

I have a datatable as shown below in the screenshot:
Source

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:
Result

Help would be most appreciated, thanks in advance!

-Ashwin A.K

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 :slight_smile:

1 Like

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

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

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!

Sure, sometimes linq is the solution :joy:

1 Like

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

1 Like

@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

2 Likes

Wow, this works! Thanks alot @ppr !

1 Like

Thank you @Lakshay_Verma ,
I appreciate your help!

1 Like

This guy @ppr is genius.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.