Using linq

Help Assistant
1

I want to replace the cost center value from sheet1 with cost center sheet 'suceesor cost center, value and sheet1 profit centre value with sheet profit centre sucesor value using linq and write the sucessor cost center and process center in New dt

16721236861193059901244622730528
167212368611930599012446227305281920Ă—1440 276 KB

1672123728317546386510817503799
16721237283175463865108175037994000Ă—3000 1.44 MB

16721237718862162576183858498950
167212377188621625761838584989504000Ă—3000 1.7 MB

2

To replace the cost center and profit center values from Sheet1 with the corresponding values from the “successor” sheets using LINQ in UiPath, you can use the following steps:

  1. Load the data from Sheet1 and the “successor” sheets into DataTables.
  2. Use the LINQ query to join the data from the two tables based on the cost center and profit center values.
  3. Use the Select method to create a new DataTable that includes the successor cost center and profit center values.
  4. Use the ToList method to convert the query result to a list of objects.
  5. Loop through the list of objects and update the values in the original DataTable (Sheet1).

Here is an example of how you could use LINQ to replace the cost center and profit center values in Sheet1 with the corresponding values from the “successor” sheets:

’ Load the data from Sheet1 and the “successor” sheets into DataTables
Dim dtSheet1 As DataTable = LoadDataFromSheet1()
Dim dtSuccessors As DataTable = LoadDataFromSuccessorSheets()

’ Use LINQ to join the data from the two tables based on the cost center and profit center values
Dim query = From r1 In dtSheet1
Join r2 In dtSuccessors
On r1(“Cost Center”) Equals r2(“Cost Center”) And r1(“Profit Center”) Equals r2(“Profit Center”)
Select New With {
.CostCenter = r2(“Successor Cost Center”),
.ProfitCenter = r2(“Successor Profit Center”)
}

’ Convert the query result to a list of objects
Dim list As List(Of Object) = query.ToList()

’ Loop through the list of objects and update the values in the original DataTable (Sheet1)
For Each item In list
Dim row As DataRow = dtSheet1.Rows.Find(item.CostCenter)
If row IsNot Nothing Then
row(“Cost Center”) = item.CostCenter
row(“Profit Center”) = item.ProfitCenter

1 Like
3

Hi,

Hope the following will help you.

image
image1192Ă—693 40.8 KB 

Dim dictPC As Dictionary(Of String,String)=dtPC.AsEnumerable.ToDictionary(Function(r) r("Old Profit Center").ToString,Function(r) r("Successor Profit Center").ToString)
Dim dictCC As Dictionary(Of String,String)=dtCC.AsEnumerable.ToDictionary(Function(r) r("Old Cost Center").ToString,Function(r) r("Successor Cost Center").ToString)

    dtSheet1.AsEnumerable.ToList.ForEach(
    Sub(r)
	    r("Profit_Center")=dictPC(r("Profit_Center").ToString)
	    r("Cost_Center")=dictCC(r("Cost_Center").ToString)
    End Sub
    )

Mock sample

Sequence4.xaml (7.4 KB)

Regards,

4

16721327829066224661176421689145
167213278290662246611764216891454000Ă—3000 1.22 MB

Getting this error

5

HI,

Can you share content of $exceptionDetials in Locals panel?

Regards,

6

Can u please help me with any xaml code that will be helpful thanks

7

Linq2.xaml (9.3 KB)
Data.xlsx (11.7 KB)
Try this