Help with Excel Automation

Help Studio
, ,
1

Hello, I am new to UiPath. I am trying to create a process to read an excel table, add a column called “Total Revenue,” this column value is “Price per Unit” times “Quantity Sold”, then filter the table to remove any rows where “Total Revenue” <500 and write the new table to a new excel sheet. I am trying to use the Excel Process scope to complete this. Please help!

2

Hi @JosiloM

- Excel Application Scope
  - Read Range (read your original table)
  - For Each Row
    - Assign
      - Row("Total Revenue") = Convert.ToDouble(row("Price per Unit")) * Convert.ToInt32(row("Quantity Sold"))
  - Filter Data Table
    - Input: DataTable from the previous step
    - Output: Filtered DataTable
    - Condition: Total Revenue < 500
  - Write Range (write the filtered DataTable to a new sheet)

Provide the sample Excel so that we can help you with the workflow.

Hope it helps!!

3

SalesData.xlsx (9.7 KB)
The Excel file
image
is attached.

What would I put for the Drive Item?

4

Hi @JosiloM
Are you using gsuite activities or you need it in normal Excel activities.

Regards

5

Hi,

I am using Normal excel.

6

@JosiloM
I will give you the workflow in a short while. Download UiPath.Excel.Acticities and use those activities.

Hope the process which I gave is right as per your question

Regards,

7

Hi @JosiloM

Please find the below workflow file.

BlankProcess15.zip (67.0 KB)

After filtering
O/P:

image
image735×422 7.49 KB

Hope it helps!!

8

Method 1:
Step 1:Use Read range activity to read the excel
Step 2:Add data column activity and give the name of the new column i,e(Total Revenue)
Step 3:Assign dt1=dt1.AsEnumerable.ToList.ForEach(Sub (x) x(“Total Revenue”)=Convert.ToDouble(x(“Price per Unit”)) * Convert.ToDouble(x(“Quantity Sold”)))
Step 4:Assign dt1=dt1.AsEnumerable.Where(Function(y) not Convert.ToDouble(y(“Total Revenue”).ToString)<500).CopyToDataTable
Step 4:Write Range new dt1

Method 2
Step 1:Use Read range activity to read the excel
Step 2:Add data column activity and give the name of the new column i,e(Total Revenue)
Step 3:Use for each row
Assign
to:row(Total Revenue)
value:Convert.ToDouble(row(“Price per Unit”)) * Convert.ToDouble(row(“Quantity Sold”))
Step 4:Filter data table
Input datatable from previous step
select remove radio button with condition Total Revenue < 500
Step 5:Write Range new dt after filteration

9

Hi
Try This

Excel Application Scope: Begin by using an Excel Application Scope activity to open your Excel file. Specify the file path and check the “Visible” option if you want to see the Excel application while the robot is running.

Read Range: Inside the Excel Application Scope, add a Read Range activity to read the data from your Excel sheet into a DataTable variable. Make sure to specify the sheet name and the range of cells you want to read.

Add Data Column: Next, add a Add Data Column activity to your DataTable to create a new column called “Total Revenue.” Set the DataType to System.Double if it’s a numeric value.

For Each Row in Data Table: Use a For Each Row activity to iterate through each row of the DataTable. Inside the loop:

a. Calculate the “Total Revenue” by multiplying the “Price per Unit” and “Quantity Sold” columns for each row and store the result in the “Total Revenue” column for that row like “CurrentRow(“Revenue”) = CDbl(CurrentRow(“Price_per_Unit”)) * CInt(CurrentRow(“QuantitySold”))”.

Filter Data Table: After calculating the “Total Revenue” for each row, use a Filter Data Table activity to filter the rows where “Total Revenue” is less than 500.

Write Range: Finally, use a Write Range activity to write the filtered DataTable to a new Excel sheet. Specify the sheet name and range where you want to write the data.

Thank you

10

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