i have three columns and i need to sum three columns and want the result in data table so i can write range so how to do this by using linq query sample attachments added
Assign activity:
resultDataTable = (From row In yourDataTable.AsEnumerable()
Let sum = row.Field(Of Double)(“Column1”) + row.Field(Of Double)(“Column2”) + row.Field(Of Double)(“Column3”)
Select yourDataTable.Clone().Rows.Add(sum)).CopyToDataTable()
→ Use read range workbook activity to read the excel and store in a datatable. Ex → dtInput
→ Take an assign activity to write linq expression
- Assign -> dtOutput = dtInput.AsEnumerable()
.Select(row => new
{
Column1 = row.Field<int>("Column1"),
Column2 = row.Field<int>("Column2"),
Column3 = row.Field<int>("Column3"),
Final = row.Field<int>("Final")
})
.Select((row, index) => dtOutput.Rows[index]["Final"] = row.Column1 + row.Column2 + row.Column3)
.CopyToDataTable();
→ Use write range workbook activity to write the dtoutput to excel
Note : In the Column1, Column2, Column3 change it as your column names.
Hope it helps!!
=> Use Read Range Workbook to read the excel and store it in an datatable.
=> Use the below query in Assign activity:
dt= (From row In dt.AsEnumerable()
Select newRow = dt.Clone().LoadDataRow({row("Material1"), row("Material2"), row("Material3"), row.Field(Of Double)("Material1") + row.Field(Of Double)("Material2") + row.Field(Of Double)("Material3")}, False)).CopyToDataTable()
=> Use Write Range Workbook to write the data back to excel
Input:
Output:
workflow:
xaml:
Sequence5.xaml (7.5 KB)
Hope it helps!!
Regards
Getting error like this
Assign: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.
Remove the double quotes and try to give the double quotes
can you write sample
(From row In DT1.AsEnumerable()
Let sum = Convert.ToDouble(row(“Hi”)) + Convert.ToDouble(row(“Hello”)) + Convert.ToDouble(row(“He”))
Select DT1.Clone().Rows.Add(row(“Hi”), row(“Hello”), row(“He”), sum)).CopyToDataTable()
Did you try this:
=> Use Read Range Workbook to read the excel and store it in an datatable.
=> Use the below query in Assign activity:
dt= (From row In dt.AsEnumerable()
Select newRow = dt.Clone().LoadDataRow({row("Material1"), row("Material2"), row("Material3"), row.Field(Of Double)("Material1") + row.Field(Of Double)("Material2") + row.Field(Of Double)("Material3")}, False)).CopyToDataTable()
=> Use Write Range Workbook to write the data back to excel
Input:
Output:
workflow:
xaml:
Sequence5.xaml (7.5 KB)
Hope it helps!!
Regards
Thanks for the effort reply but im getting error like this
Also i forgot to mention i have numbers like float so that would make difference?
Try this once:
dt= (From row In dt.AsEnumerable()
Select newRow = dt.Clone().LoadDataRow({row("Material1"), row("Material2"), row("Material3"), row.Field(Of String)("Material1") + row.Field(Of String)("Material2") + row.Field(Of String)("Material3")}, False)).CopyToDataTable()
Regards
im getting output like this but these datas are not present in the input file also i need only last column so could you help on this
sorry i dont have latest excel… also i want to achieve this without using excel so thats y im looking for linq query anyway really thanks for the reply
1 Like
Hi @sathish_Kumar6
=> Use Read Range Workbook to read the exce3l and store it in an datatable say dt
Input:
=>Use the below linq query in assign acitivity:
dt= (From row In dt.AsEnumerable()
Let sum = row.Field(Of Double)(0) + row.Field(Of Double)(1) + row.Field(Of Double)(2)
Select newRow = dt.Clone().LoadDataRow({row(0), row(1), row(2), sum}, False)).CopyToDataTable()
=> Use Write Range workbook to write the data back to excel.
Output:
Workflow:
xaml:
Sequence5.xaml (7.5 KB)
Hope it helps!!
Regards
1 Like
hey thanks it works is there anyway i could get only final column alone like i need only sum column alone
1 Like
If you find the solution for your query please mark it as solution to close the loop.
Happy Automation
Regards
ok thanks for the support
You can use the below code in Invoke Code activity:
out_dt = New DataTable
out_dt.Columns.Add("Final", GetType(Double))
For Each row In dt.AsEnumerable()
Dim sum = row.Field(Of Double)("Material1") + row.Field(Of Double)("Material2") + row.Field(Of Double)("Material3")
Dim newRow = out_dt.NewRow()
newRow("Final") = sum
out_dt.Rows.Add(newRow)
Next
Below are the Invoked arguments:
Workflow:
Output:
Regards
1 Like