Find and Replace

Help
1

Hi there,
I want to find a specific word in column A and replace it.
I already tried it with this description:

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.

And when I start the application, I get the whole file in column A (I mean every column is written in column A, separated with a comma.

image
image1635×387 17 KB

1 Like
2

did you set the column separator in formatting option when using ‘Generate Data Table’ activity?

3

Hi

Fine we can directly replace being in datatable
like
–hope you have the datatable ready
–use for each row loop and pass the above datatable variable in to it
–inside the loop use a if condition like this
row(“yourcolumnname”).tostring.contains(“2018”)
if the condition passes it will go to THEN part where use a assign activity like this
row(“yourcolumnname”) = “2019”
–then use a write rangea activity under workbook activity and mention the datatable as input and the excel file path, sheetname and range as “”
which would write the file to the excel we want with new updated value

Simple isn’t it
Cheers @Loons

1 Like
4

Hi @Loons

Use the Column separator property of the generate data table activity and assign it to the value you have used to separate the columns. As per the screenshot, it looks like its the comma (“,”).

image

it should work

5

Thank you, it works.

But I have a second problem, maybe you can help me.

I have three words in column A:
“ANN-MS-0-005-0”
“ANN-MS-1-005-0”
“ANN-MS-2-005-0”
and i need to replace them with: “UBXH02-91”
“UBXH02-92”
“UBXH02-93”

how can I do this?

And there are not always only three words, sometimes there are more.

1 Like
6

Fine
so you want to replace the term from
“ANN-MS- 0 -005-0” to “UBXH02-91 ”
“ANN-MS- 1 -005-0” to “UBXH02-92 ”
“ANN-MS- 2 -005-0” to “UBXH02-93 ”
right
@Loons

7

Yes :slight_smile:

1 Like
8

Fine will this ANN-MS remains same if so we can do it in the same way using if condition like this
along the sequence mention use a if condition and mention condition like
row(“yourcolumnname”).ToString.Contains(“ANN-MS”)
If this condition passes it will goto THEN part where we can have
row(“yourcolumnname”) = “UBXH02-9”+(Convert.ToInt32(Split(row(“yourcolumnname”).ToString,“-”)(2).ToString)+1).ToString

Simple isn’t it

Cheers @Loons

1 Like
9

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