Remove the character if that starts with the value of particular column in excel

Help Studio
, ,
1

Hi Experts,

I hope you are doing well. I am here with another scenario, where I have a data set in an Excel file. There I have one column with some numerical data which is a sort of ID, The requirement is if the column rows have data starting with “9”, then we need to remove that “9” from there. For example

Ref_ID(Column Name)
876502
908751
128965
927861

I want the value would be like
Ref_ID
876502
08751
128965
27861

This file is having 8 sheets and ecah sheet has more than 10k records, so want to avoid any kind of excel or loop kind of solution which will take longer time. So any other way if you guys can help.

Thanks

2

@learning_sourav

Assign activity:
    DatatableVariable = (From row In YourDatatableVariable.AsEnumerable()
                        Let originalID = row.Field(Of String)("Ref_ID")
                        Let modifiedID = If(originalID.StartsWith("9"), originalID.Substring(1), originalID)
                        Select YourDatatableVariable.Clone().LoadDataRow({modifiedID}, False)).CopyToDataTable()

image
image715×628 21.8 KB

image

3

Hi @learning_sourav

Try this syntax:

Dt = (From row In Input_dt.AsEnumerable()
                        Let originalID = row.Field(Of String)("Ref_ID")
                        Let modifiedID = If(originalID.StartsWith("9"), originalID.TrimStart("9"c), originalID)
                        Select Input_dt.Clone().Rows.Add({modifiedID}, False)).CopyToDataTable()

Dt is of DataType System.Data.DataTable

Hope it helps!!

4

Hi @learning_sourav

Try this Linq:

inputDataTable.AsEnumerable().Select(Function(row) row("Ref_ID").ToString().StartsWith("9") ? row("Ref_ID").ToString().Substring(1) : row("Ref_ID")).CopyToDataTable()

OR

inputDataTable.AsEnumerable().Select(Function(row) 
                                row("Ref_ID") = If(row.Field(Of String)("Ref_ID").StartsWith("9"), row.Field(Of String)("Ref_ID").Substring(1), row.Field(Of String)("Ref_ID"))).CopyToDataTable()
5

Hi,

Can you try the following sample?

image
image720×607 23.1 KB

dt.AsEnumerable.Where(Function(r) r("Ref_ID").ToString.StartsWith("9")).ToList().ForEach(Sub (r)
    r("Ref_ID")=r("Ref_ID").ToString.SubString(1)
End Sub
)

Sample20240208-1.zip (11.5 KB)

Regards,

1 Like
6

Thanks for quick response,
I am getting this following error
Assign: Unable to cast object of type ‘System.Double’ to type ‘System.String’.

Thanks

7

@learning_sourav

Okay i understand your problem so please change the linq query

(From row In YourDatatableVariable.AsEnumerable()
                        Let originalID = Convert.ToString(row.Field(Of Double)("Ref_ID"))
                        Let modifiedID = If(originalID.StartsWith("9"), originalID.Substring(1), originalID)
                        Select YourDatatableVariable.Clone().LoadDataRow({modifiedID}, False)).CopyToDataTable()

You are getting the error because in your excel it is stored as double so i changed the query as per your requirement.Hope it helps

8

Hi, Thanks for the quick reply,

I am getting this error
Assign: Unable to cast object of type ‘System.Double’ to type ‘System.String’.

Thanks

9

Hi @learning_sourav

Try this:

Dt = (From row In Input_dt.AsEnumerable()
                        Let originalID = row.Field(Of Double)("Ref_ID")
                        Let modifiedID = If(originalID.StartsWith("9"), originalID.TrimStart("9"c), originalID)
                        Select Input_dt.Clone().Rows.Add({modifiedID}, False)).CopyToDataTable()

Regards

10

The linq is working, but there I have other columns also, that is getting replaced, and the filtered data table has only one column here, I am not sure if I am doing something wrong here.

11

Here I am getting some error like trimstart is not member of double

12

Hi @learning_sourav

Try this:

- Assign -> Output_dt = (From row In Input_dt.AsEnumerable()
                         Let Ref_ID As String = row("Ref_ID").ToString
                         Let ChangedValue As String = If(Ref_ID.Substring(0,1).Equals("9"), Ref_ID.Replace(Ref_ID.Substring(0,1),""), Ref_ID)
                         Select Input_dt.Clone().Rows.Add(ChangedValue)
                          	 ).CopyToDataTable()

Input:
MicrosoftTeams-image (7)

MicrosoftTeams-image (5)
MicrosoftTeams-image (5)822×550 79.6 KB

Output:
MicrosoftTeams-image (6)

Regards

13

@learning_sourav

Try this in invoke code and send dt as in/out argument

Input_dt.AsEnumerable().ToList.ForEach(sub(r) r("RefId") = If(r("RefID").ToString.StartsWith("9"),r("RefID").TrimStart("9"c),r("RefID")))

Cheers

1 Like
14

Hi @learning_sourav

If you have multiple columns try this code once:

- Assign -> Output_dt = (From row In Input_dt.AsEnumerable()
      Let Ref_ID As String = row("Ref_ID").ToString()
      Let ChangedValue As String = If(Ref_ID.StartsWith("9"), Ref_ID.Substring(1), Ref_ID)
      Let newRow = Input_dt.Clone().Rows.Add(row.ItemArray.Select(Function(column) If(column.Equals(row("Ref_ID")), ChangedValue, column)).ToArray())
      Select newRow).CopyToDataTable()

Regards

1 Like
15

@learning_sourav

(From row In YourDatatableVariable.AsEnumerable()
                         Let originalID = row.Field(Of Double)("Ref_ID").ToString()
                         Let modifiedID = If(originalID.StartsWith("9"), originalID.Substring(1), originalID)
                         Select YourDatatableVariable.Clone().LoadDataRow(row.ItemArray.Select(Function(item, index) If(index = YourDatatableVariable.Columns.IndexOf("Ref_ID"), Double.Parse(modifiedID), item)).ToArray(), False)).CopyToDataTable()

I have check your problem and this will work to overcome
Input
image
output
image

1 Like
16

Thank you
this approach worked…

1 Like
17

Thank you all for the quick response

some of the approaches worked well. But I need to give a solution for one, so I gave to the earliest solution.

Thanks

18

Thanks, this is working now…

1 Like
19

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