How to find the sum of 2 columns of an excel and display it in a 3rd column using UIPATH

Question

I want to read an excel file which have 2 columns with a few rows (integer values). I need to add a 3rd column in the excel and find the sum of the 1st 2 columns in that 3rd one. need to do this in UIPATH. Please help me

Answer 1

you can use the following:

1. Use of DataTable. So you first read your Excel file by using an Excel Application Scope activity with Read Range, then for each row read (the For Each Row activity), you use an Assign activity to get the sum of row(0) and row(1), the values of the first and second items in the row, and save it to a variable; lastly, write the value in this variable back to the Worksheet at the next column at ("C" + rowNumber) e.g. C1, C2, C3.

2. Write straight to the Excel Worksheet at the next column (let's say, column C), with the formula; e.g. write your Excel file by using an Excel Application Scope activity with For Each Row and Write Cell, the content can be set to something like "=SUM(A" + rowNumber + ":B" + rowNumber + ")".

Answer 2

1. First use the excel application scope to get the data as data table.

2. use For Each row Activity to get the value of column A and Column B:

For Each row of Data-table{

Add collection Activity (to add the sum of Column A + Column B)

note- In property panel , add the list name and value as (ROW(0)+ROW(1)).TOSTRING

}

Count=0

for each item for list 

{

excel application Scope{

In Write Cell activity( use range as  : "C"+(Count+2).tostring , In value box: List(item).tostring)

}

}

Comments

Hi @Venkat, seems like a good approach. But can you suggest any simpler solution.

Answer 3

1.  Excel Application Scope (Activity) : In Properties, Enter the workbook path (Ex : "F:\UI Path Foundation Material\Files\sum of numbers.xlsx")

2. Read Range (Activity): In Properties,Enter SheetName = "Sheet1", Uncheck Headers if  column header is not given, Enter Output DataTable (Ex : OutputDataTable)

3. Assign Counter (Activity) =1

4.For Each Row (Activity) in OutputDataTable

          Read Cell (Activity): In Properties,,Enter Cell  = "A" + Counter, SheetName = "Sheet 1", Output result = "AValue"

           Read Cell (Activity) : In Properties,,Enter Cell  = "B" + Counter, SheetName = "Sheet 1", Output result = "BValue"

          Assign (Activity) CValue = AValue+BValue

          Write Cell (Activity): In Properties,Enter Range = "C"+Counter, Value = CValue

         Assign (Activity) Counter = Counter+1

Answer 4

These are the steps to add two columns and write it into third column :

1. Create a variable ExcelPath and assign path of the excel sheet , Use Excel Application Scope activity and use ExcelPath variable and save the output in a new dataTable variable

2. Use read range and give the sheet name.

3. create a new variable Counter of Int32 then use assign activity and use(counter=2) 
Note : if you dont have header then assign counter =1
4. use for each row (item = dataTable)
5. use read cell activity and in properties section give cell = "A"+CStr(counter) and save the output in a new variable AValue of double type.
6. use read cell activity and in properties section give cell = "B"+CStr(counter) and save the output in a new variable BValue of double type.
7. now use write cell activity and give sheet as sheet name and range as ""C"+CStr(counter)" and in value use "Cstr(AValue+BValue)"
Note : Cstr is used for converting double into String in vb.net
8. use assign activity and assign counter=counter+1

Comments

@Shivi, very well explained answer. Thank you for sharing this solution.

---

Can you show where you have the CValue variable please ?

---

Hi Pul, I think CValue is CStr(AValue + BValue) i.e.:

Assign (Activity) CValue = AValue+BValue

---

Great example. Thanks.

---

Thank you Abha. you are right.

---

Hi @Pul, glad your issue is cleared. Feel free to ask any other questions you have on Community.

---

Hi,

I am getting error "Could not read cell from address A0"

---

Which sequence of activities are you using? Are you using UiPath.Excel.Activities.ExcelReadCell activity? it can only be used inside the Excel Application Scope activity.

---

HI I am new to RPA can i read a column by its name? my column position is not fixed and i want to locate it by its name in my process… I tired to put the Column header in the Range but I’m getting error as it needs cell number…but i cant put cell number i need to find it by its name…is there a way to achieve thiss…

---

Try Using read range dt

Dt.Select(“column name<>’’”). copytodatatable ()

Hope this helps!