Replace Activity to remove special character

Help Studio
, ,
1

Hi All,

I am comparing two excel sheets and trying to find match. I have used replace activity to convert the date in both the sheets in one format and replace special characters. However, the replace activity is not working as expected. Please find the below screenshot.

If((ColumnDisc(column.ColumnName)=“Start_date"Or ColumnDisc(column.ColumnName)=“End_date”),DateTime.ParseExact(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Trim,{“M/d/yyyy”,“M-d-yyyy”,“MM/dd/yyyy”,“MM-dd-yyyy”},System.Globalization.CultureInfo.InvariantCulture).ToString(“ddMMyyyy”),CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.replace(”$.“,”“).Trim) = If((column.ColumnName=“Start Date” Or column.ColumnName=“End Date”),DateTime.Parse(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Trim).ToString(“ddMMyyyy”),dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Replace(”$.“,”").Trim)

I am trying to convert the date in both sheets to ddMMyyyy and replace the special character i.e. “.” and replace dollar sign $.

image
image877×208 4.87 KB

image
image1260×239 6.3 KB

2

Hi @marina.dutta

I could see there are some typo error. May be you could try this code:

If ((ColumnDisc(column.ColumnName) = "Start_date" Or ColumnDisc(column.ColumnName) = "End_date"), DateTime.ParseExact(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Trim, {"M/d/yyyy", "M-d-yyyy", "MM/dd/yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture).ToString("ddMMyyyy"), CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Replace("$.", "").Trim) = If ((column.ColumnName = "Start Date" Or column.ColumnName = "End Date"), DateTime.Parse(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Trim).ToString("ddMMyyyy"), dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Replace("$.", "").Trim)

Hope it helps

3

@Parvathy

The replace function did not worked

4

@marina.dutta

Try this once:

If ((ColumnDisc(column.ColumnName) = "Start_date" Or ColumnDisc(column.ColumnName) = "End_date"), DateTime.ParseExact(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Trim, {"M/d/yyyy", "M-d-yyyy", "MM/dd/yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture).ToString("ddMMyyyy"), CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Replace("$", "").Replace(".", "").Trim) = If ((column.ColumnName = "Start Date" Or column.ColumnName = "End Date"), DateTime.Parse(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Trim).ToString("ddMMyyyy"), dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Replace("$", "").Replace(".", "").Trim)

Regards

5

@Parvathy

I tried but the replace the function did not work

6

Hi @marina.dutta

Try this syntax

If ((ColumnDisc(column.ColumnName) = "Start_date" Or ColumnDisc(column.ColumnName) = "End_date"), DateTime.ParseExact(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Trim, {"M/d/yyyy", "M-d-yyyy", "MM/dd/yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture).ToString("ddMMyyyy"), System.Text.RegularExpressions.Regex.Replace(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString, "[$.]", "").Trim) = If ((column.ColumnName = "Start Date" Or column.ColumnName = "End Date"), DateTime.Parse(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Trim).ToString("ddMMyyyy"), System.Text.RegularExpressions.Regex.Replace(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString, "[$.]", "").Trim)

Regards

7

@Parvathy

Thanks a lot

1 Like
8

You’re welcome @marina.dutta

Happy Automation
Regards

9

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