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
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())
- 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.
Hi Mkankatala,
I have so many rows so foreach is very slow. any other solution.
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!!
I Got this error in assign value
Argument ‘Value’: BC30491: Expression does not produce a value.
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!!
output_dt means? what datatable variable or anything? above you not mention column name also?
Hi @MD_Farhan1
Considering the input to be like below:
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:
Regards
above code is working but change all column .but i want only specific column.
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
i got error Cannot assign from type ‘System.Object’ to type ‘System.Data.DataTable’ in Assign activity ‘Assign’.
Regards
Try deleting the Assign activity and reassigning it again. It’s working fine at my end
Regards
pls guide me another Two bug. one is how to delete specific Column using column name?
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!!
Hi,
this one is not working.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.