Here are a couple of handy DataTable.Select queries that I found useful in managing data tables. I have previously discussed about How to filter multiple dynamic values in a datatable column but this time I'm going to collect my most used ones for future reference.
I found this VB.net methods extremely helpful in minimizing the amount of Activities in my Workflow. Also, sometimes the Filter DataTable Wizard doesn't work and I found these methods more accurate.
OutputDT = DataTable.DefaultView.ToTable(true,"ColumnName")
Returns a datatable with distinct values from a specific column. This is similar to running a Remove Duplicate Rows activity except you can directly specify which column to affect.
DataTable.Columns(4).ColumnName = "New Column Name"
Renames the 3rd column with the value specified in "New Column Name"
DataTable = (From x In DataTable.AsEnumerable() Order By convert.Tostring(x("ColumnName1")), convert.ToString(x("ColumnName2")) Select x).CopyToDataTable
Returns a data table sorted in ascending order by 2 columns. This is similar to running a Sort Data Table activity except that you can use multiple sorting columns.
OutputDT = DataTable.Select("[Column Name] IN ('Value1', 'Value2', 'Value3')").CopyToDataTable
This will return rows where the specified column contains the indicated values. Similar to filtering a table from Excel.
StringArray = (From row In DataTable.AsEnumerable() Select Convert.Tostring(row(“Column Name”))).ToArray()
This returns a String array containing all values in a given column
Related: How to filter multiple dynamic values in a datatable column
Just a few so far but will add to these as I go along learning UiPath better! :)
How to select distinct/unique values in a column
OutputDT = DataTable.DefaultView.ToTable(true,"ColumnName")
Returns a datatable with distinct values from a specific column. This is similar to running a Remove Duplicate Rows activity except you can directly specify which column to affect.
How to rename DataTable column name
DataTable.Columns(4).ColumnName = "New Column Name"
Renames the 3rd column with the value specified in "New Column Name"
How to sort DataTable using multiple columns
DataTable = (From x In DataTable.AsEnumerable() Order By convert.Tostring(x("ColumnName1")), convert.ToString(x("ColumnName2")) Select x).CopyToDataTable
Returns a data table sorted in ascending order by 2 columns. This is similar to running a Sort Data Table activity except that you can use multiple sorting columns.
How to query your DataTable using Select method
OutputDT = DataTable.Select("[Column Name] IN ('Value1', 'Value2', 'Value3')").CopyToDataTable
This will return rows where the specified column contains the indicated values. Similar to filtering a table from Excel.
How to convert a DataTable column to a String Array
StringArray = (From row In DataTable.AsEnumerable() Select Convert.Tostring(row(“Column Name”))).ToArray()
This returns a String array containing all values in a given column
Related: How to filter multiple dynamic values in a datatable column
Just a few so far but will add to these as I go along learning UiPath better! :)