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

May 28, 2018 in RPA 12,298 views

+1 vote

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 + ")".

• 3,110 points

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)

}

}

• 320 points
Hi @Venkat, seems like a good approach. But can you suggest any simpler solution.
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
answered Jun 9, 2019 by anonymous
• 140 points

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

answered Aug 2, 2019 by Shivi Singhal
@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,

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…

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

Hope this helps!

How to extract data from a website and save it to excel using UiPath?

Hi Piyush, if you want to extract ...READ MORE

How can I use text automation activities to extract data from a table and display it in UiPath studio?

Hi @Simran, if you want to extract ...READ MORE

change a specific column of an excel sheet in UiPath

1. First of all , you need ...READ MORE

Changing a specific column of an excel sheet in UiPath

You can use a For each row ...READ MORE

Robot Manager in UiPath

the robot.exe file is located in: C:\Users\USER_ACCOUNT\AppData\Local\UiPath\app-17.1.6523\Ui-Robot.exe Hope it ...READ MORE

Automation in Uipath

you can use the Database package for connecting  ...READ MORE