Boolean error to replace text using Asenumberable

Hi…

To replace the text using Asenumberable for in the assign activity…

Replace the Text Hmilk to H-Milk in the column name (Business)

image

i got above error…

Thanks
Shyam

Hi @Shyam_Pragash

Try this below expression

You can achieve this in String manipulation

“Hmilk”.Replace(“Hmilk”,“H-milk”)

Hmilk → Input String

Regards
Gokul

Hi @Shyam_Pragash ,

There Where is used to Filter results, while you are trying to make changes to the Collection itself which is why you run into this issue.

The right option here would be to go with a Select clause like so:

Dt_RR.AsEnumerable().Select(Function(a) Dt_RR.Clone.LoadDataRow({a.Field(OF String)("Business").ToString.Replace("Hmilk","H-Milk"), row(1),row(2)...},False)).CopyToDataTable()

Its not the exact solution for your problem, since I am not aware of how many columns your DataTable consists of.

A little bit of clarity on that will put us in a better position to assist you.

Kind Regards,
Ashwin A.K

Hi @Gokul001

i have the excel file in that column (Buisness) under this head more than 700 rows. i have to replace the text hmilk to H-Milk the specific column Name…

  1. Read the excel file using readrange activity…Datatable Name :Dt.RR
  2. Assign acitvity used this function.

image

Dt_RR.AsEnumerable().Where(Function(a) a.Field(Of String)(“Business”).ToString.Replace(“Hmilk”,“H-Milk”).ToString).CopyToDataTable()

Thanks
Shyam

Hi @ashwin.ashok

you said code is not working i got error… XML tag issue.

Refer the snap shot.
image

Thanks
Shyam

Hi @Shyam_Pragash

Try this below expression

(From r In Dt.AsEnumerable
Let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace("Hmilk",“H-milk”)).toArray()
Select Dt.Rows.Add(ra)).CopyToDataTable()

Regards
Gokul

Hi @Shyam_Pragash ,

Dt_RR.AsEnumerable().Select(Function(a) Dt_RR.Clone.LoadDataRow({a.Field(OF String)("Business").ToString.Replace("Hmilk","H-Milk"), row(1),row(2)...},False)).CopyToDataTable()

The {row(0),row(1),row(2)…} refers to each column in order.

If your datatable consist of 5 columns, with the “Business” column on the fourth column, this is what it shuld look like:

{row(0), row(1),row(2),a.Field(OF String)(“Business”).ToString.Replace(“Hmilk”,“H-Milk”) row(4)}

Kind Regards,
Ashwin A.K

Hi @Shyam_Pragash

Try this below expression

Dt.AsEnumerable().Select(Function(row) row("Business").ToString.Replace("Hmilk", "H-milk")).ToArray

Regards
Gokul

Please share the flow easy to understand…

My Excel (Database)
image

Thanks
Shyam

Hi @Shyam_Pragash ,

Could you try this?

Dt_RR.AsEnumerable().Select(Function(a) Dt_RR.Clone.LoadDataRow({a.Field(OF String)("Business").ToString.Replace("Hmilk","H-Milk"), a(1),a(2),a(3),a(4)},False)).CopyToDataTable()

Kind Regards,
Ashwin A.K

Hi @Gokul001

i got error

image

HI @Shyam_Pragash

Dt.AsEnumerable().Select(Function(a) Dt.Clone.LoadDataRow({a.Field(OF String)("Business").ToString.Replace("Hmilk","H-Milk")},False)).CopyToDataTable

Here is the workflow

Deletion.xaml (6.0 KB)

Regards
Gokul

Sorry, I seem to have made a mistake, here is a sample workflow:

image

Dt_RR.AsEnumerable().Select(Function(a) Dt_result.Clone.LoadDataRow({a.Field(OF String)("Business").ToString.Replace("Hmilk","H-Milk"), a(1),a(2),a(3),a(4)},False)).CopyToDataTable()

ReplaceItemsInColumn.xaml (8.1 KB)

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok @Gokul001

Both code are working fine… but i have one issue…

In my excel more 126 columns in the sheet… the issue is only header reflected but not value. it shows only empty cell…

here attached the output excel sheet.
Generate Output.xlsx (8.5 KB)

  • Excel columns may be extened depending upon the requirement…

Thanks
Shyam

Hi @Shyam_Pragash ,

Provided the column “Business” always shows up at the first index, could you try this?

(From row In Dt_RR.AsEnumerable()
Let modCol = row.Field(Of String)("Business").ToString.Replace("Hmilk","H-Milk")
Let ra = row.ItemArray.Skip(1).Prepend(modCol).ToArray()
Select Dt_result.Rows.Add(ra)).CopyToDataTable()

ReplaceItemsInColumn_v1.xaml (5.5 KB)

Kind Regards,
Ashwin A.K

1 Like

HI @Shyam_Pragash

Here is the workflow

Deletion.xaml (6.8 KB)

You can try to read only the particular column. Get the row count

Regards
Gokul

1 Like

Hello @Shyam_Pragash,

I dont know if you tried it, but why not using a for each row?

Inside the for each row, assign this column to myvar = row(“Business”).ToString

Then if myvar = “HMilk” → assign: myvar = “H-Milk”

Hi @Gokul001 @ashwin.ashok @Angel_Llull

Thanks you…

is there any note to leanring the asEnumberable and datatable functions ?

Thanks
Shyam

Hi @Shyam_Pragash ,

I’ve started penning a series on LINQ since not many know how to use it.
Its still a work in progress, and feel free to offer suggestions.

Kind Regards,
Ashwin A.K

Hi @Shyam_Pragash

Have look on the Tutorial

Regards
Gokul