Replace "-" to "_"

Help Studio
, , , ,
1

Hi,
I have one column Name Services. column have many rows of data . data like 1234567890 some rows have 123-456-789. now i want to change 123_456_789. pls solve this one.
Regards

2

Hi @MD_Farhan1

You can use the Replace method to achieve this. Here’s a workflow:

→ Read Range: Use the “Read Range” activity to read your Excel file into a DataTable.

→ For Each Row: Iterate through each row in the DataTable.

→ Assign Activity: Use the “Assign” activity to modify the value in the “Services” column. Use the Replace method to replace ‘-’ with ‘_’. For example:


-Assign -> row("Services") = If(row("Services").ToString().Contains("-"), row("Services").ToString().Replace("-", "_"), row("Services").ToString())
  1. Write Range: Finally, write the modified DataTable back to your Excel file using the “Write Range” activity.

This way, you iterate through each row, replace the ‘-’ with ‘_’, and update the DataTable accordingly.

3

Hi Mkankatala,
I have so many rows so foreach is very slow. any other solution.

4

Try the below linq in assign activity.

yourDataTable.AsEnumerable().ToList().ForEach(Sub(row) row("Services") = New String(row("Services").ToString().Select(Function(c) If(c = "-"c, "_"c, c)).ToArray()))

Hope it helps!!

5

I Got this error in assign value
Argument ‘Value’: BC30491: Expression does not produce a value.

6

@MD_Farhan1

Dont consider the above syntax please use the below expression:

output_dt= yourDataTable.AsEnumerable().Select(
    Function(row) yourDataTable.Clone().Rows.Add(
        row.ItemArray.Select(
            Function(col) If(col.ToString().Contains("-"), col.ToString().Replace("-", "_"), col)
        ).ToArray()
    )
).CopyToDataTable()

output_dt is of DataType System.Data.DataTable.

Hope it helps!!

7

output_dt means? what datatable variable or anything? above you not mention column name also?

8

Hi @MD_Farhan1

Considering the input to be like below:

image
image645×748 7.84 KB

After using the below query:

out_dt= yourDataTable.AsEnumerable().Select(
    Function(row) yourDataTable.Clone().Rows.Add(
        row.ItemArray.Select(
            Function(col) If(col.ToString().Contains("-"), col.ToString().Replace("-", "_"), col)
        ).ToArray()
    )
).CopyToDataTable()

Output:

image
image758×759 9.13 KB

Regards

9

above code is working but change all column .but i want only specific column.

10

@MD_Farhan1

Try the below syntax:

output_dt= yourDataTable.AsEnumerable().Select(
    Function(row) yourDataTable.Clone().Rows.Add(
        row.ItemArray.Select(
            Function(col, index) If(index = yourDataTable.Columns.IndexOf("Services") AndAlso col.ToString().Contains("-"), col.ToString().Replace("-", "_"), col)
        ).ToArray()
    )
).copyToDataTable()

output_dt is of DataType System.Data.DataTable.

Regards

11

i got error Cannot assign from type ‘System.Object’ to type ‘System.Data.DataTable’ in Assign activity ‘Assign’.

Regards

12

@MD_Farhan1

Try deleting the Assign activity and reassigning it again. It’s working fine at my end

Regards

13

Thank You So Much now Working Fine :index_pointing_at_the_viewer: :+1:

1 Like
14

pls guide me another Two bug. one is how to delete specific Column using column name?

15

I hope you find the solution for your query. Make mark my post as solution to close the loop. @MD_Farhan1

You can use the below syntax to delete the specific column based on the column name.

yourDataTable.DefaultView.ToTable(False,"Services")

Happy Automation!!

1 Like
16

Hi,
this one is not working.

17

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