5.3 In-Memory DataTable Transformations & Column Operations

5.3 In-Memory DataTable Transformations & Column Operations

While LINQ queries excel at filtering and querying data rows, enterprise automations frequently demand structural modifications to DataTable objects. Developers must dynamically add computed columns, modify data types, consolidate disparate vendor reports through table merges, and eliminate duplicate records prior to queue dispatching or database staging.

Executing these structural changes efficiently requires deep mastery of the ADO.NET architecture under the hood of UiPath Studio. This section covers schema cloning versus deep copying, dynamic expression columns, missing schema collision policies during table merges, and high-performance DataView operations for sorting and deduplication.

---

1. Schema Cloning vs. Deep Copying vs. Variable Aliasing

A frequent source of bugs in enterprise UiPath workflows is the improper duplication of DataTable variables. Developers often confuse structural replication with data replication, or unwittingly introduce reference aliasing bugs.

The Variable Assignment Trap (dt_Target = dt_Source)

In .NET, DataTable is a reference type. Assigning one variable to another (dt_Target = dt_Source) does not create a new table. Instead, it copies the memory reference pointer. Both variables point to the exact same object on the managed heap. Any row added, edited, or removed through dt_Target immediately alters dt_Source.

Structural Replication: dt.Clone()

The .Clone() method creates a new, independent DataTable with the identical schema as the source table, but zero rows:

• It duplicates all DataColumn definitions (column names, DataType, AllowDBNull, DefaultValue, MaxLength, and Expression).
• It duplicates primary keys, table constraints, and extended properties.
• It allocates no row storage, making it the ideal pattern for initializing filtered target tables.

Deep Replication: dt.Copy()

The .Copy() method creates a completely independent deep copy of the table, replicating both the structural schema and all DataRow elements:

• All rows are cloned into the new table.
• Row states (DataRowState.Added, Modified, Unchanged, Deleted) and row versions (DataRowVersion.Original, Current) are preserved.
• Modifications made to rows in the copied table have no effect on the original source table.

' VB.NET: Demonstrating Clone vs Copy
Dim dt_EmptyTemplate As DataTable = dt_RawData.Clone() ' Schema only; 0 rows
Dim dt_BackupSnapshot As DataTable = dt_RawData.Copy()  ' Schema + all rows

---

2. Adding and Configuring Columns Programmatically

UiPath provides the visual Add Data Column activity, but configuring columns in code via an Invoke Code activity or within expressions grants precise control over column types and positions.

Specifying Native System Types

When adding columns, developers should assign strongly typed System.Type definitions rather than accepting default object representations:

' VB.NET: Adding strongly typed columns to a DataTable
dt_Transactions.Columns.Add("ProcessingTimestamp", GetType(DateTime))
dt_Transactions.Columns.Add("TaxRate", Type.GetType("System.Double"))
dt_Transactions.Columns.Add("IsAudited", GetType(Boolean))

// C#: Adding strongly typed columns to a DataTable
dt_Transactions.Columns.Add("ProcessingTimestamp", typeof(DateTime));
dt_Transactions.Columns.Add("TaxRate", Type.GetType("System.Double"));
dt_Transactions.Columns.Add("IsAudited", typeof(bool));

Modifying Column Ordinal Positions

By default, new columns are appended to the far right of the table. To position a column at a specific zero-based index (for instance, making a generated status column the first column in an export file), use .SetOrdinal():

' Move the "Status" column to the first column position (index 0)
dt_Transactions.Columns("Status").SetOrdinal(0)

Renaming and Deleting Columns

' Rename an imported header
dt_Transactions.Columns("Old_Vendor_Num").ColumnName = "VendorID"

' Remove temporary staging column
dt_Transactions.Columns.Remove("TempCalculation")

---

3. Dynamic Computed Expression Columns (DataColumn.Expression)

One of the most powerful and underutilized features of ADO.NET in UiPath automations is the DataColumn.Expression property. Setting an expression turns a column into a computed virtual column:

• Values are calculated dynamically by the ADO.NET engine whenever row values change.
• Calculations execute in native compiled code without procedural looping across rows.
• Computed columns do not consume storage space for raw cell values.

Arithmetic and Text Expressions

' Arithmetic calculation across numeric columns
dt_Orders.Columns("SubTotal").Expression = "Quantity * UnitPrice"

' Discounted total with conditional tax
dt_Orders.Columns("FinalTotal").Expression = "(Quantity * UnitPrice) * (1 - Discount) + TaxAmount"

' String concatenation combining names
dt_Staff.Columns("FullName").Expression = "LastName + ', ' + FirstName"

Conditional Logic (IIF Expressions)

The expression syntax supports the IIF(expr, truepart, falsepart) conditional operator, allowing complex status classification without a single visual activity:

' Classify transactions based on threshold
dt_Orders.Columns("AuditCategory").Expression = "IIF(FinalTotal > 50000, 'Tier 1 Review', IIF(FinalTotal > 10000, 'Tier 2 Review', 'Standard'))"

Aggregates with DataTable.Compute

For a single total or average over the whole table, or over a filtered subset, use the Compute method instead of adding a column:

' Total of the Amount column for one department
Dim itTotal As Object = dt_Expenses.Compute("Sum(Amount)", "Department = 'IT'")
' Number of open orders
Dim openCount As Object = dt_Orders.Compute("Count(OrderID)", "Status = 'Open'")

Compute returns an Object (or DBNull when no rows match), so convert the result before using it in arithmetic.

---

4. Transforming Row Values: LINQ vs. For Each Row vs. Invoke Code

When updating existing column values (e.g., trimming whitespace from every cell in a column, converting string dates to standardized ISO formats), developers must balance performance against implementation complexity:

1. For Each Row in Data Table Activity: Suitable when updating rows requires invoking UI elements, orchestrator queue calls, or external REST APIs. However, for pure in-memory data sanitization across 20,000+ rows, the activity execution loop introduces significant lag.
2. LINQ Projection into Cloned Table: Projects rows into modified structures and materializes via .CopyToDataTable(). Fast, but requires recreating row instances.
3. Invoke Code loop: Updates rows in place inside one activity, which avoids the per-row activity overhead of For Each Row.

' VB.NET inside Invoke Code (argument: in_Customers As DataTable, direction In)
For Each row As DataRow In in_Customers.Rows
    If Not row.IsNull("Email") Then
        row("Email") = row("Email").ToString().Trim().ToLower()
    End If
    If Not row.IsNull("Phone") Then
        row("Phone") = System.Text.RegularExpressions.Regex.Replace(row("Phone").ToString(), "[^0-9]", "")
    End If
Next

---

5. Merging DataTables and MissingSchemaAction Policies

The Merge Data Table activity (or native dt_Target.Merge(dt_Source) method) combines rows from a source table into a target destination table. When the schemas of both tables do not align perfectly, the MissingSchemaAction property dictates how the engine resolves discrepancies.

' Method signature
dt_Target.Merge(dt_Source, preserveChanges:=False, missingSchemaAction:=MissingSchemaAction.Add)

The Four MissingSchemaAction Enum Behaviors

The preserveChanges Parameter

When merging tables that possess a primary key, existing records are updated rather than appended. The preserveChanges boolean parameter controls row version priority:

• preserveChanges = True: Retains local changes made in the target table, ignoring incoming source updates for conflicting rows.
• preserveChanges = False (Default): Overwrites target table rows with incoming source data.

---

6. High-Performance Sorting and Deduplication with DataView

Every DataTable contains a DefaultView property returning a System.Data.DataView instance. The DataView provides high-speed native sorting, row filtering, and projection capabilities without requiring complex LINQ syntax or external loops.

In-Memory Sorting with DataView.Sort

Sorting a DataTable via its DefaultView uses the view's own index inside ADO.NET, so no workflow loop is needed. The sorted view can then be converted back to a DataTable via .ToTable():

' VB.NET: Sort table by Priority ascending, then by Amount descending
dt_Transactions.DefaultView.Sort = "Priority ASC, Amount DESC"
Dim dt_Sorted As DataTable = dt_Transactions.DefaultView.ToTable()

In-Memory Filtering with DataView.RowFilter

The RowFilter property applies expression-based filtering equivalent to an SQL WHERE clause:

' VB.NET: Filter rows using RowFilter
dt_Transactions.DefaultView.RowFilter = "Status = 'Open' AND Amount >= 5000 AND OrderDate >= #2026-01-01#"
Dim dt_Filtered As DataTable = dt_Transactions.DefaultView.ToTable()

Column Projection and Deduplication: ToTable(isDistinct, ...)

The .ToTable() method features an overload that accepts a boolean isDistinct flag and a ParamArray of column names:

' Signature:
dt.DefaultView.ToTable(isDistinct As Boolean, ParamArray columnNames As String())

This single method call performs two critical operations simultaneously:

1. Column Filtering (Projection): It extracts only the specified columns, discarding all others.
2. Deduplication (Distinct): If isDistinct is set to True, it eliminates duplicate rows, retaining only unique combinations of the selected column values.

' VB.NET: Extract unique combinations of CustomerID and Region into a new table
Dim dt_UniqueClients As DataTable = dt_Sales.DefaultView.ToTable(True, "CustomerID", "Region")

' C#: Extract unique combinations of CustomerID and Region into a new table
DataTable dt_UniqueClients = dt_Sales.DefaultView.ToTable(true, "CustomerID", "Region");

---

7. Memory Management and Enterprise Cleanup Patterns

DataTable objects can hold a large amount of managed memory, including the indexes behind views, sorting, and primary keys. In long-running unattended automations processing millions of rows, keeping large tables alive longer than needed can lead to excessive memory consumption.

• Call dt.Clear() to remove all rows while retaining table schema.
• Set dt = Nothing (or null in C#) once data processing is complete to allow the garbage collector to reclaim generation 2 heap memory.
• When processing massive files in batches, initialize tables within localized inner scopes rather than maintaining global workflow variables.