3. RPA Challenge - LINQ to extract values from datatable

Random and other categories RPA Challenge
1

Hi All,

Can someone help me with how to extract values from datatable using LINQ. I have a csv which I am reading and creating a datatable. CSV has two columns A and B. Column A defines ID’s and column B defines role.

Example
Column A Column B
1 Admin
2 Approver
2 Deletion

and so on… ID 2 can have multiple values defined as role.

I can do that with other method but want to know with use of LINQ.

Regards,
Ashish Manjrekar

2 Likes
2

hey @Ashish32229

Please go through with this link and let me know :slight_smile:

Thanks& Regards…!!

3

Hi aksh,

I have gone through that however it’s very basic and I need for Datatable.

Regards,
Ashish Manjrekar

4

Hi @Ashish32229

Please not that they were deliberately not allowed on DataTables, it’s just that DataTables pre-date the IQueryable and generic IEnumerable constructs on which Linq queries can be performed.

Both interfaces require some sort type-safety validation. DataTables are not strongly typed. This is the same reason why people can’t query against an ArrayList, for example.Query DataTable using LINQ in C#

For Linq to work you need to map your results against type-safe objects and query against that instead.

You can check this article as well:, May be it will help you on this.

Regards…!!

3 Likes
5

This is a good exercise. Moved to Automation Challenge :slight_smile:

3 Likes
6

below Linq should get all the roles for a specific id:

(((From dr As Datarow In dt
Where Convert.ToString(dr(“ColumnA”))=“2”
Select Convert.ToString(dr(“ColumnB”))).ToList()).Distinct()).ToList

This returns a List Of String
Is this what you wanted?

2 Likes
7

We need an working XAML uploaded :slight_smile:

8

Hello, this is the result I got.

ID : 1 Role in company : Analyst,
ID : 2 Role in company : Medical,Engineer,Accountant,IT,Specialist,
ID : 3 Role in company : Analyst,
ID : 4 Role in company : Scientist,Advisor,
ID : 5 Role in company : Scientist,

Please take a look and let me know if this count for linq, the thing is that I’m just using dataTable.Select("ID = " & idTolook) but not where or those type of things lol.
.3. RPA Challenge - LINQ to extract values from datatable.zip (3.2 KB)

3 Likes
9

Hi everyone,

Please find attached a working workflow using linq to extract values from a DataTable :wink:

RPA Challenge - LINQ to extract values from datatable.zip (3.2 KB)

11 Likes
10

Very elegant solution @Masire ! :smiley: :smiley:

1 Like
11

Thanks @richarddenton :+1:

12

Assume the Datatable is DataTableObject having two columns “column1” and “column2”.

Below are certain examples of LINQ on this Datatable -

DataTableObject.AsEnumerable.Where(Function(dr) Convert.ToInt32(dr(“column1”)) = someString).Select(Function(dr) Convert.ToString(dr(“column2”))).ToArray()

DataTableObject.AsEnumerable.Where(Function(dr) Convert.ToInt32(dr(“column1”)) = someString).CopyToDataTable

13

With CSV file saved and having the first cell filled with string : ID - there is issue to reopen csv file - it looks for sylk file and the content is messed up.

MS Office Bug :wink: just avoid ID in first cell.

14

Hi Masire,

Can you please explain the in your assign activity you used as " from dr As datarow in dtData select convert …

sorry to bother as I did not understand that.

Thanks
Hari.

17

its too complicated i think!!!

18

Thank you for your post! Since I was unable to find any c# code to accomplish something similar…I am providing an example of getting the Sum of a column in a DataTable.

MyDataTable.AsEnumerable().Select(p=>Convert.ToDecimal(p["MyColumnName"])).Sum()
1 Like
19

3 posts were split to a new topic: Compare and join list matches and differences