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
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
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
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
31. Basic linq queries
This post looks good and detailed
Have a view on this for more ideas
Search in UiPath forum as
Hope this will be helpful 
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
