Sum rows from DataTable

Agentic Automation
,
1

Hello,

I need to get the total sum of the column of a DataTable. My Data Table has just a columm type String, such as " 20130 EUR". I want to obtain the total of this columm in format number without the “EUR”. Can I use some activity for this?.Thanks

2

Hi @Vanesa_Garcia_R

Just trythis,(Change var. accordingly)

total = yourDataTable.AsEnumerable().Sum(Function(row) CInt(row(“YourColumnName”).ToString.Trim.Replace(" EUR", “”)))

Happy Automation

2 Likes
3

Please, can you explain in more detail the activities I should use? Thank you.

1 Like
4

Hello @Vanesa_Garcia_R ,

The solution provided by @prashant1603765 , will definately works. just in case if you want to have a dynamic apporach i.e., not just EUR but anything apart from numbers, then you can use below logic

1. Create a variable of type string. Ex: Total
2. Select Assign activity
3. Total = YourDatatable.AsEnumerable().Sum(Function(row) CDbl(System.Text.RegularExpressions.Regex.Match(row.("YourColumnName").ToString(), "[\d.,]+").Value)).ToString()

With this you should get total of respective column

1 Like
5

@Vanesa_Garcia_R
Use an Assign activity . total should be of type Int or Double (if needed) .

6

total is type Double. In “Assign” activity I use your expression:

total = yourDataTable.AsEnumerable().Sum(Function(row) CDbl(row(“YourColumnName”).ToString.Trim.Replace(" EUR", “”)))

I get Error ERROR Validation Error BC30512: Option Strict On disallows implicit conversions from ‘Boolean’ to ‘Double’. The selected value is incompatible with the property type

7

@Vanesa_Garcia_R

Might be quotes issue check by past at Notepad
Check this,
total = yourDataTable.AsEnumerable().Sum(Function(row) CDbl(row(“YourColumnName”).ToString.Trim().Replace(" EUR", “”)))

8

Hi @Vanesa_Garcia_R

Sample datatable input

A table displays two columns titled "Amount" and "Column2," with numerical values in euros on the left and corresponding alphabetical codes on the right. (Captioned by AI)
A table displays two columns titled "Amount" and "Column2," with numerical values in euros on the left and corresponding alphabetical codes on the right. (Captioned by AI)570×194 5.37 KB

This image shows a process in a software workflow where data is first built into a table and then a total value is calculated and stored by summing a specific column (Amount) from the data table. (Captioned by AI)
This image shows a process in a software workflow where data is first built into a table and then a total value is calculated and stored by summing a specific column (Amount) from the data table. (Captioned by AI)715×266 8.01 KB

Total variable type is System.Double

total = dt1.AsEnumerable().Sum(Function(row) CDbl(System.Text.RegularExpressions.Regex.Match(row("Amount").ToString(), "\d+(\.\d+)?").Value))

Output
image

If your Column is having null values use this

dt1.AsEnumerable().Sum(Function(row) If(System.Text.RegularExpressions.Regex.Match(row("Amount").ToString(), "\d+(\.\d+)?").Success, CDbl(System.Text.RegularExpressions.Regex.Match(row("Amount").ToString(), "\d+(\.\d+)?").Value), 0.0))

Check
Do mark it as a solution if it helps you !
Happy Automation :innocent:

9

Your answer is also correct. Thank you so much. You’re very kind.

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.