Hi everyone, I have 2 excels eg 1st and 3nd excel
1st excel having capacity column and having integers eg 20 etc rows
2nd excel column capacity having 10 etc rows
I want to subtract from 1 Excel row value to another oe value and finally result dt send to queue
This is my requirement, can any one help me here
Thank you
Hello @panguluri_saritha, try something along these lines:
- Read Excel Sheets:
- Use the “Read Range” activity to read the data from both Excel sheets into separate DataTables.
- Iterate and Subtract:
- Iterate through the rows in both DataTables simultaneously, and subtract the values in the “capacity” column of the first DataTable from the corresponding values in the second DataTable.
- Create a new DataTable to store the results of the subtraction.
- Create a Queue:
- In Orchestrator, create a queue where you’ll send the results.
- Add Items to the Queue:
- Inside the loop where you perform the subtraction, for each result, add an item to the queue with the result as a field.
Cheers! 
1 Like
Excel_Dt1 has 20 rows
Excel_Dt2 has 10 rows
Excel_Dt3 is the output of Excel_Dt1-Excel_Dt2
Excel_Dt3=Excel_Dt2.AsEnumerable.select(function(x,i) Excel_Dt3.Rows.Add(Math.Abs(Cint(x(0))-CInt(Excel_DT1(i)(0))))).copytodatatable
*Create a Queue in Orchestrator
*For each row in Excel3 inside loop
Add queue Item Activity
Hope it Helps!!
hi, thank you, tried this but enumerable:maths not declared error is occuring, can you suggest anything here or is there any other way
@sasi_poosarla
Hi thanks for your quick response, actually I am failing at 2nd step, can you tell me brief how to do 2nd step (how to subtract from 1 st excel to 2nd excel)
@rodrigo.simao
can you please share the error screenshoot
math function…
you added s ,remove s
Check this out:
Step 1: Read Excel Sheets
- Use the “Excel Application Scope” activity to specify the path to your first Excel file.
- Inside the scope, use the “Read Range” activity to read the data from the first Excel sheet into a DataTable (let’s call it
dtFirst).
Repeat the above steps to read data from the second Excel sheet into another DataTable (let’s call it dtSecond).
Step 2: Subtract Values Now, you need to iterate through the rows of both DataTables and perform the subtraction. You can use a “For Each Row” activity for this purpose. Here’s how you can do it:
- Add a “For Each Row” activity to your workflow.
- For the “Input DataTable,” select
dtFirst(the DataTable from the first Excel sheet).
- Inside the “For Each Row” loop, add an “Assign” activity to perform the subtraction:
- To: Create a new variable to store the result (e.g.,
result), and set the variable type toInt32. - Value: Use the expression
row("capacity").ToStringto get the value from the first Excel sheet’s capacity column.
- Add another “Assign” activity after the previous one to subtract the corresponding value from the second Excel sheet:
- To: Create another variable to store the result (e.g.,
finalResult), also of typeInt32. - Value: Use the expression
CInt(result) - CInt(dtSecond.Rows(rowCounter)("capacity").ToString)to subtract the value from the second Excel sheet.rowCounteris the index of the current row in the loop.
Step 3: Create a New DataTable To store the results of the subtraction, you can create a new DataTable using the “Build DataTable” activity:
- Add a “Build DataTable” activity.
- Define the structure of the new DataTable with a single column (let’s call it “Result”) of type
Int32.
Step 4: Add Items to the Queue Now, you can add the results to the queue as you mentioned:
- Use an “Add Queue Item” activity inside the “For Each Row” loop.
- Set the queue item data as
finalResult(the result of the subtraction).
Cheers!
Is it possible that the some variable you are using is not in the right scope?
no all are in scope checked again…
Try putting that variable in the main scope and see if it works.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.