How to Update Column Values Using LINQ in UiPath

Introduction:

In UiPath, working with DataTables is a core part of automating tasks, especially when dealing with Excel files. The provided workflow illustrates how to read data from an Excel sheet, modify specific column values using LINQ, and then write the updated data to a new sheet.

 In this article, we’ll break down each step of this process and explain how it works. Input data:

Step 1: Read Range Activity

• Activity Used: Read Range Workbook
• Purpose: This activity reads data from the specified Excel file (Input.xlsx) and sheet (Sheet3) into a DataTable (InputDT).
• Details: The Range property is left blank to read the entire sheet. This will load all the data into InputDT, which will be processed in the next steps.

Step 2: Clone the DataTable Structure

• Activity Used: Assign
• Purpose: The goal here is to create an empty clone of the original DataTable. This will allow us to add modified rows without affecting the original data.
• Expression:

ClonedDT = InputDT.Clone

• Explanation: Clone() creates a copy of the DataTable’s structure (column names and types) but without any data. The ClonedDT will be used to store the updated rows.

Step 3: Modify Column Values Using LINQ

• Activity Used: Assign
• Purpose: This step uses LINQ to iterate through the rows in the InputDT DataTable, modify specific column values, and add the updated rows to the ClonedDT.
• Expression:

Array_list = (From row In InputDT 

              Let x = NewObject(){ row("Name").ToString, 

                                    row("Sub").ToString, 

                                    (CInt(row("Marks").ToString) + 50).ToString } 

              Select ClonedDT.Rows.Add(x)).CopyToDataTable()

• Explanation:
  • From row In InputDT: Iterates through each row in the InputDT.
  • Let x = New Object(){...}: Creates a new object array that holds the modified data:
    • row("Name").ToString: Keeps the value of the “Name” column unchanged.
    • row("Sub").ToString: Keeps the value of the “Sub” (Subject) column unchanged.
    • (CInt(row("Marks").ToString) + 50).ToString: Adds 50 to the integer value of the “Marks” column and converts it back to a string.
  • Select ClonedDT.Rows.Add(x): Adds the modified row to the cloned DataTable.
  • CopyToDataTable(): Converts the modified collection of rows back into a DataTable (Array_list).

Step 4: Write Range Activity

• Activity Used: Write Range Workbook
• Purpose: The updated DataTable (Array_list) is written to a new sheet (Sheet4) in the same Excel file (Input.xlsx).
• Details: The Write Range activity will overwrite any existing data in Sheet4 with the updated DataTable content.

Output:

Conclusion:

This workflow demonstrates how to efficiently modify and copy data from one DataTable to another using LINQ in UiPath. By reading data from an Excel sheet, adjusting values dynamically, and writing the results to a new sheet, you can automate complex data manipulation tasks with ease.

Also Read – How To Filter Columns By Using LINQ Query Syntax In UiPath