All about Datatable - UiPath

News Tutorials
,
1

Hi All,

Datatable - variable type is the most commonly used variable type by all UiPath users

And it’s been the most often asked queries in forum especially on its manipulation

So I would like to have this post as a repository for those who are looking for expressions related to Datatable

Let’s go one by one keeping dt as name of datatable variable

1. Get number of rows in a datatable dt

dt.Rows.Count (output - int32 type)

2. Get number of columns in a datatable dt

dt.Columns.Count (output - int32 type)

3. Get specific row index - inside For each row

dt.Rows.IndexOf(CurrentRow)
(Output- int32 type)

4. Get specific column index if column name is known

dt.Columns.IndexOf("Columnname")
Or
dt.Columns.IndexOf(ColumnIndex)
(Output - int32 type)

We can also get column name with index in vice versa like this

Str_columnname = dt.Columns(int_columnindex).ColumnName.ToString

Note : Count usually starts from 1 and Index Starts from 0, for first row or column

5. Get excel column position based on Excel column name

  • this will be useful when someone want to write in excel, each cell one by one in a column
  • for that first read the excel with read range activity and get output as dt
  • now to get any column position like “A1” or AA1”

For “A1”, “A2”, “A3”,……

use this expression in Cell Range property of Write cell activity kept inside a For each row activity

Convert.ToChar(65 + dt.Columns.IndexOf("Columnname") ).ToString + (dt.Rows.IndexOf(CurrentRow)+2).ToString

(Output will be “A1”, “A2”,…. String type)

As we are inside the for each row loop, CurrentRow will increment automatically and thus write cell activity will write the value in each cell one by one in a particular column. That column is mentioned with column name.
2 in rowindex is because index starts with 0 so 0+2 will be A2, so that bot starts to write from A2 as A1 is header in excel
If there is no header at all then mention as +1 instead of +2

65 usually denotes “A” - ASCII code
Converting that to Char will give the equivalent alphabet

For AA1, AA2, AA3,……

Same with a little change in expression

Convert.ToChar( (65 + dt.Columns.IndexOf("Columnname") ) - 26).ToString + (dt.Rows.IndexOf(CurrentRow)+2).ToString

(Output will be “AA1”, “AA2”,…. String type)

6. Remove duplicate records considering whole table

We can use a simple activity named Remove Duplicate records activity

7. Remove duplicate records in one column

Use a assign activity like this

dt= dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)("Columnwithduplicatevalues")).Select(Function(g) g.First).CopyToDataTable

(Output will dt - datatable type)

8. Get only certain columns from a datatable

Use a assign activity like this

dt = dt.DefaultView.ToTable(FALSE, "Columnname-1")

If you want specific multiple group of columns from dt

dt = dt.DefaultView.ToTable(FALSE, "Columnname-1", "Columnname-2", "Columnname-3",….., "Columnname-N")

If you want to specific or multiple group of columns from dt with distinct records

dt = dt.DefaultView.ToTable(TRUE, "Columnname-1")

8. a. Get Columns Name (into array of string) from a datatable

arr_ColumnNames = (From dc In DT.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray()

(Output will array of string type)

8. b. Get Columns Name as UpperCase and Trimmed (into array of string)

arr_ColumnNames = (From dc In DT.Columns.Cast(Of DataColumn) Select dc.ColumnName.ToString.ToUpper.Trim).ToArray()

(Output will array of string type)

9. Search for a records in a datatable having specific text in it

dt = dt.AsEnumerable().Where(Function(a) a.Field(of String)("yourColumnname").ToString.Contains(“your text value”)).CopyToDatatable()

(Output- datatable type)

10. Search for records with certain filter condition

There are four different methods to filter a datatable
Have a view on this doc

https://www.google.com/amp/s/www.uipath.com/community/rpa-community-blog/four-ways-to-filter-data-table-in-uipath-studio%3Fhs_amp%3Dtrue

11. Get Maximum value of a column in datatable

Use a assign activity like this

int_max = dt.AsEnumerable().Max(Function(row) cint(row("Columnname")))

(Output - int32 type)

12. Get Minimum value of a column in datatable

Use a assign activity like this

int_min = dt.AsEnumerable().Min(Function(row) cint(row("Columnname")))

(Output - int32 type)

13. Get Average value of a column in datatable

Use a assign activity like this

int_avg = dt.AsEnumerable().Average(Function(row) cint(row("Columnname")))

(Output - int32 type)

13. a. Get Sum value of a column in datatable

Use a assign activity like this

int_sum = dt.AsEnumerable().Sum(Function(row) cint(row("Columnname")))

(Output - int32 type)

14. Get top N rows in a datatable

Use a assign activity like this

dt = dt.AsEnumerable().Take(5).CopyToDataTable()

(Output - datatable type)

15. Skip first N rows in a datatable

Use a assign activity like this

dt = dt.AsEnumerable().Skip(5).CopyToDataTable()

(Output - datatable type)

16. Insert a column at last in a datatable

We can use ADD DATACOLUMN activity to add a column atlast to a datatable

17. Insert a column in certain position in a datatable

We can use set ordinal method to change the position of a column

image

The DataColumn.SetOrdinal(Int32) method changes the ordinal or position of the DataColumn to the specified ordinal or position.

Or

We can use INSERT/DELETE COLUMNS activity to insert at desired position

18. Delete rows and delete columns in a datatable

We can use the already available activities like Delete Rows and Delete Columns activities

19. copy a datatable

Use a assign activity like this

dt_New = dt.Copy()

(Output- datatable type)

20. Clone table structure of a datatable

Use a assign activity like this

dt_new = dt.Clone()

(Output- datatable type)

21. Clear all data in a datatable

We can use CLEAR DATATABLE ACTIVITY

This activity will remove all the data in a datatable but keeps the datatable header
It won’t remove the header of that datatable

22. Compare two Datatables

Use a assign activity like this

For Common Values

dt = dt1.AsEnumerable().Intersect(dt2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

  • For UnCommon Values*

dt = dt1.AsEnumerable().Except(dt2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

23. Merge two Datatables

We can use Merge Datatable Activity

24. Add blank row a datatable

Use ADD DATAROW ACTIVITY where in DataRow property mention as
DataTableName.NewRow and in Datatable property mention your datatable name

25. Add a row with values to a datatable

Same - use a Add Datarow activity where we have a property named ArrayRow
Pass the values you have in a array format like this
{“value-1”, “Value-2”, “Value-3”,….,“Value-N”}

26. Sort a column in datatable in ascending or descending order

We can use Sort Datatable activity

27. Lookup a datatable with another one

We have a specific activity in UiPath to perform this
Lookup Datatable activity

28. Convert a datatable to string variable

Use OUTPUT DATATABLE ACTIVITY to get a datatable as a string variable

For more details on lookup and clear datatable activity, have a view on this doc
https://docs.uipath.com/activities/docs/manage-data-tables

29. Combines rows from two tables by using values common to each other

We can use JOIN DATATABLE ACTIVITY according to a Join rule, which is specified in the JoinType property
Have a view on this for complete details

30. Update value in a specific column of a datatable

We can use Update Row Item activity
For more details
https://docs.uipath.com/activities/docs/update-row-item

31. Basic linq queries

This post looks good and detailed
Have a view on this for more ideas
Search in UiPath forum as
https://forum.uipath.com/t/howto-first-start-with-linq-vb-net/318964

Hope this will be helpful :slight_smile:

Questions

For questions on your retrieval case open a new topic and get individual support

Feedback

Share the scenarios and solution or approach used to solve that, here below to this post which would definitely help others looking for similar scenario

Happy new year

Cheers

80 Likes
2

What a list @Palaniyappan! Cool!

One suggestion - it might be worthwhile putting the code snippets in the code blocks with these quotes ", rather than these ones “ ”. The latter ones used to be problematic in the past when directly copied over to Studio.
So, instead of:
int_max = dt.AsEnumerable().Max(Function(row) cint(row(“ColumnName”)))

Something like this:
int_max = dt.AsEnumerable().Max(Function(row) cint(row("ColumnName")))

12 Likes
3

Awesome bro

3 Likes
4

Great content. Very helpful. Thanks for sharing.

4 Likes
5

Very true… it’s actually the rich text editor on this forum that transforms those… it’s a tedious job to reverse fix that.

3 Likes
6

Excelent set of tutorials! Pin this on top and half of the forum questions are answered.
There are a few options you mention though, using vb code to solve which can also be directly done by using the native UiPath activities. I see that a lot on the forum lately, everybody trying to solve things with code.

While powerfull, the fact that these questions are so often returned here are that a lot of RPA devs have limited code knowledge, and rely heavily on the click and drag activities available to them.

(Example: in my team most RPA devs are financial experts who migrated to RPA instead of having an ICT background)

Nonetheless… great, great reference list. My compliments!

6 Likes
7

Thanks

8

Congratulations

9

Thank you

10

Hi @loginerror

Done the editing as suggested :raised_hands:t2:

Cheers

2 Likes
11

Thanks @kirankumar.mahanthi1 @Robinnavinraj_S

3 Likes
12

Yeah that’s certainly right

We get into expression only if it takes a complex scenario and I hope it is required as it demands at times

But there are many custom component made on similar note in our UiPath rpa marketplace - on those linq expressions or commonly used expressions for datatable manipulation

The one I found really cool is this one

Cheers @Jeroen_van_Loon

2 Likes
13

Amazing content… @Palaniyappan :100: :raised_hands:

2 Likes
14

Wow @Palaniyappan, Great Stuff! Simply Awesome!! :100:

2 Likes
15

Good

17

Awesome list, @Palaniyappan!
One thing though, the header or code in items “11. Get Minimum value of a column in datatable” and “12. Get Maximum value of a column in datatable” might be swapped.

2 Likes
18

Thanks @viniciussm
Changes updated

3 Likes
19

Thanks for the information sir

20

Awesome sir

21

Superb sir

1 Like