How to claculate numbers from a "table extract" activity

[pj.ortega]

Hi everyone, I´m a new member of this forum and trying to get better in this UiPath world ^^. got a problem about how to calculate diferents amounts from a “table extract” activity to then add it to an excel. I´m actually try diferents ways to do it but never even close to finish . Then solution must take a table from a website and then filter it with just EUR, then get a variable with the numbers but without “,” after all of this i need to search how to convert the variable to use some activity that allow me to do the math ^^. Any help will be priceless. Thanks to everyone and sorry for any mistakes in my english ^^. Read u soon!!

[Palaniyappan (Palaniyappan P)]

Hi

Welcome to UiPath forum
Have a view on this thread where it has all the functionality of how to add, min, avg and many other functionalities of using datatable

All about Datatable - UiPath

Hi All, Datatable - variable type is the most commonly used variable type by all UiPath users And it’s been the most often asked queries in forum especially on its manipulation So I would like to have this post as a repository for those who are looking for expressions related to Datatable Let’s go one by one keeping dt as name of datatable variable 1. Get number of rows in a datatable dt dt.Rows.Count (output - int32 type) 2. Get number of columns in a datatable dt dt.Columns.Count (output…

For your,

1. extract the datatable with Table extraction and do use any of the functions from above thread as you need

Cheers @pj.ortega

[Vikas_M]

Hey @pj.ortega ,
Extract the table by using the Extract Table Data activity (Data scraping /Table Extraction also works)

• ``For Each Row in data table` activity
  • Inside for loop place an Ifactivity Condition:row(“YourCurrencyColumnName”).ToString() = “EUR”`
• Assign: row(“AmountColumn”) = row(“AmountColumn”).ToString().Replace(“,”, “”)`
• Assign: totalAmount = totalAmount + Convert.ToDouble(row(“AmountColumn”))`
• Write Range Activity(to write the DataTable to an Excel file)

Remember to replace the column names with Your table columns

Hope it helps you!

[Parvathy (PS Parvathy)]

Hi @pj.ortega
=> Extract a table from a website: Use Extract Table Data to extract the table from the web page and store it in an variable say dtExtractedData

=> Filter the table to only include rows with EUR: Use the below syntax to filter the data with currency EUR and store the output in a variable say filteredTable.

filteredTable = dtExtractedData.AsEnumerable().Where(Function(row) row.Field(Of String)("CurrencyColumn") = "EUR").CopyToDataTable()

Note: filteredTable is of Data type System.Data.DataTable. Replace CurrencyColumn with the actual column name containing currency information.

=> Extract and format the numbers: Use a “For Each Row” activity to iterate through dtFilteredData. For each row, use an “Assign” activity to parse and format the amount:

row("AmountColumn") = row("AmountColumn").ToString().Replace(",", "")

Note: Replace AmountColumn with the actual column name containing the amounts.

=> Perform calculations on these numbers: This below query calculates the sum of the amounts in the filtered DataTable (dtFilteredData) and stores the result in the totalAmount variable as a Double.

totalAmount= dtFilteredData.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("AmountColumn")))

Note: totalAmount is of Data type System.Double. Replace AmountColumn with the actual name of the column containing the amounts.

=> Write the results to an Excel file: After the loop, use the “Write Range” activity to write the results to an Excel file:

• Input DataTable: dtFilteredData
• Excel File Path: Specify the path to your Excel file.
• Sheet Name: Specify the name of the Excel sheet where you want to write the data.

Hope it helps!!

[Parvathy (PS Parvathy)]

Hi @pj.ortega

If you find solution for your query please mark it as solution to close the loop.

Regards

[Nitya1 (Nitya Tomar)]

Hi @pj.ortega

Here’s a step-by-step guide:

1. Use the “Data Scraping” activity to extract the table data from the website.
2. After scraping the data, you’ll have a DataTable. You can use a “Filter Data Table” activity to filter it to only include rows where the currency is EUR. Here’s how:

• Drag and drop the “Filter Data Table” activity.
• In the properties panel, specify the DataTable from the Data Scraping as the input.
• Use a filter expression like [Currency] = 'EUR' to filter rows with EUR currency.

3. To remove commas from the numbers in the DataTable, you can use a For Each Row activity to iterate through the rows and clean the data. Add the following steps inside the loop:

• Assign activity: Create a new variable to store the cleaned value (e.g., CleanedAmount).
• Use an expression like row("Amount").ToString.Replace(",", "") to remove commas and assign it to CleanedAmount.
• Update the “Amount” column in the DataRow with CleanedAmount.

4. Now that you have a DataTable with cleaned data, you can use a For Each Row activity to iterate through the filtered and cleaned DataTable and perform calculations. For example, you can sum the amounts and store the result in a variable:

• Create a variable (e.g., TotalAmount) to store the sum.
• Use an Assign activity inside the loop to update TotalAmount like this: TotalAmount = TotalAmount + Double.Parse(row("Amount").ToString).

5. Finally, you can use the “Write Range” activity to write the result (TotalAmount) to an Excel file. Specify the cell where you want to write the result in your Excel sheet.

Thanks!