Find and Replace in Excel

Hello all, if I am working in an excel database and need to change all instances of a year to the next year (for example change all instances of 2018 to 2019) how would I go about doing that?

1 Like

Hiiā€¦

If you are looking to do the operation in datatable, first of all convert the datatable to string by using ā€œOutput Datatableā€ activity.
Then add a ā€œReplaceā€ activity with input(output of ā€˜Output Datatableā€™ activity), Pattern(ā€œ2018ā€) and Replacement(ā€œ2019ā€).
Use ā€˜Generate Data Tableā€™ activity with input(output of ā€˜Replaceā€™ activity) and pass itā€™s output datatable to write an excel file.
Hope this will meet your requirementā€¦ :slightly_smiling_face:

Warm regards,
Nimin

Hiiā€¦

If you want to do the replacement other than datatable method, you can achieve it in a single step by using VBA.
After the ā€˜Read Rangeā€™ activity inside the Excel Application scope, add ā€œInvoke VBAā€ activity and add the code file Replace.zip (341 Bytes) (.txt file)
Give the EntryMethodName as ā€œFindReplaceAllā€
#Using this method, the operation will be carryout directly on your source excel file.

Warm Regards,
Nimin

@ nimin , Hi
where i need to add the VBA code (Replace.zip) in the invoke VBA
the code inside the Zip file , where i need to add it in the uipath ?
thanks very much

Hi @nimin
new to uipath
i have also similar scenario
need to change the 0 with No and 1 with Yes in Expedite Column.

ExpediteColmn

,
can u please suggest some steps.

Thanks

Priyanka

@priyanka1991

1.Read your Excel and store into a Datatable Dt1
2.use For each row in Dt1
3.use Assign activity,create a variable var1=Cint(row(ā€œcolumn nameā€))
4.use if condition if var1=0 in Then use assign to set No and in else assign Yes
5.use Add Data Row to add each row to DT
Regards,
A Manohar

thanks for your quick reply @Manohar1
i hv done like this in if condition.
pls have a look

how sud i add each row to DT by add data row??

Many thanks

priyanka

@priyanka1991

use Build Datatable to create a DT where declare the columns you want to create
In Add Data Row just in Array row pass values as below
{row(ā€œcolnameā€),ā€¦,strNoā€¦}

Regards,
A Manohar

do i need to create all coulmns in build data table which are present in excel sheet??

If you want to print only a specific column then you can build table with that specific column as per your requirement.

yes ,just want to modify that particular column with 0 to no and 1 to yes
want to keep all columns and data as it is.
thatā€™s it.

@priyanka1991

use append range activity and maintain the specific range of column you want to change. and declare data table in it
it will change automatically with the new values

many thanks @Manohar1
it worked as u suggestedā€¦
but couldnā€™t change the value 0 to no and 1 to yes
as i attached the screenshot above for replacing the valueā€¦
i m doing wrong somewhere.
pls guideā€¦

@priyanka1991

unable to find screenshot

for your reference i will do an example and will share xaml file

sure manohar

Hi,
First read your excel sheet using the read range activity and then assign a datatable variable for it.Then create a for each row activity for that data table and inside that for each activity put an assign statement as. row(ā€œcolumn nameā€)=row(ā€œcolumn nameā€).toString.replace(ā€œ0ā€,ā€œnoā€).trim
and another assign activity as row(ā€œcolumn nameā€)=row(ā€œcolumn nameā€).toString.replace(ā€œ1ā€,ā€œyesā€).trim
Then you can write that data table into an excel using write range activity.:slight_smile:

hi vishal

after putting assign activity in for each row.
what data table u r talking about??
sud i create build data table and then add data rowā€¦
bcz as manohar suggested that procedure worked except yes /no conversionā€¦
m attaching xaml fileā€¦
pls have a lookā€¦
demoProcesses.xaml (19.4 KB)
thanks for helpā€¦

@priyanka1991

ref.xls (17 KB)

Main.xaml (10.7 KB)

1 Like

The data table which you performed for each activity.
use the same data table in the write range.

1 Like

cool
got it

Thank you so much!