How to change a particular column of an excel sheet in ui path

Question

I have an excel sheet that has a data like this

Position        URL       Company  Location
Developer  /xyz/123/xyz    ABC       US
Manager    /xyz/132/asd    ASD       US
Tester     /xyz/142/asf    ABD       US

I wish to capture all the url from this sheet and add "www.xyz.com/" before them and replace the old url with the new url so that the sheet becomes like this

Position        URL                  Company  Location
Developer  www.xyz.com//xyz/123/xyz    ABC       US
Manager    www.xyz.com//xyz/132/asd    ASD       US
Tester     www.xyz.com//xyz/142/asf    ABD       US

The work flow that i have till now is 1) Open excel application scope 2) Use read range 3) Use for each row to fetch the url 4) Use assign activity to create a new variable and save the new url in it

But i am not able to understand how to replace it

Would appreciate any help

Say if this string (`/xyz/123/xyz`) is `cell B1`, you can just use `"www.xyz.com/"&B1` (maybe type this in `cell D1`) to get your answer. — ian0411, Oct 23 '17 at 03:50
@ian0411 this will affect only B1, and rest of the B2,B3..etc won't get affected — Sam, Oct 23 '17 at 07:55

score 1 · Answer 1 · answered Oct 24 '17 at 18:57

I'm gonna start from your third point since you have the datatable from read range (With Add headers checked preferably).

With a For each row you can loop through the datatable and replace the value in column URL using an Assign activity like this:

Next, use Excel application scope again. Look for the activity Write range (System > File > Workbook > Write range) and enter the datatable. I recommend you to check Add headers.

This will add the datatable to the Excel file selected.

score 1 · Answer 2 · answered Oct 30 '19 at 09:30

Changing the value of a particular cell in excel can be done without much efforts in G1ANT and don't forget to add addon xlxs to your robot. Here, is the sample code for your problem statement.

xlsx.open ♥environment⟦USERPROFILE⟧\path\filename.xlsx
xlsx.getvalue row 1 colname a result ♥val
dialog ♥val
xlsx.setvalue value ‴www.xyz.com/♥val‴ row 1 colindex 1 result ♥val
dialog ♥val

score 0 · Answer 3 · answered Oct 23 '17 at 13:07

On cell E2, enter this formula:

="www.xyz.com/"&B2

And as indicated in the pic, double click that black point and the formula should be copied down to the end automatically. Or you can drag it down if you want. Try and let me know if this is what you are looking for.

How to change a particular column of an excel sheet in ui path

3 Answers3