Query remove first 4 character

Help Studio
1

Hi,

how can I remove first 4 char from below data table using query

image

I would like to remove all chracter starting with “740_” in invoiceno column and get the output as
GM-XXX for all data in datatable

I did try to use below command but did not get the output

queryremove4char=DTPi.AsEnumerable().Orderby(Function(a) a.Field(of string)(“invoiceno”).ToString.Replace(“740_”,” ”) ).CopyToDatatable()

Thank you
Regards
Jamuri

2

Get cell value
Replace 740_ with “”
Write cell value

OR

Read sheet to dt
loop through dt
Replace value and write cell

3

Hi @Abang_Jamuri_Abang_Shoker

Please try below steps,

  1. Read the excel sheet data in the datatable variable name dt.

  2. Drag and drop the invoke code activity.

  3. In edit arguments, create a variable dt, set direction as in/out and pass value as dt.

  4. In edit code type the following,

dt.asenumerable.where(function(row) row(“invoiceno”).tostring.startswith(“740_”)).tolist.foreach(sub(row) row(“invoiceno”)= row(“invoiceno”).tostring.replace(“740_”,“”))

Thanks

1 Like
4

Why overcomplicate it with Invoke Code? Just use an Assign inside a For Each to change the value in each row.

1 Like
5

It will reduce the numer of activities to use and will done in single activity.thats why

6

we do see a LINQ which is ordering on invoiceno by not using begining 740_

Which output you got? Validation message, not right ordered… Please tell us details

7

Hi,

dt = dt.AsEnumerable().Where(Function(a) a.Field(of string)(“yourcolumnname”).ToString.Replace(“your old value”,” your new value”).ToString).CopyToDatatable()

hope this will work

1 Like
8

It’s easier to follow an automation that uses regular activities, even if it’s 2 or 3 instead of 1. When writing automations you have to consider that others will have to maintain it in the future, so writing code that’s easy to understand is very important.

1 Like
9

Yes @postwick you are correct when we deal with a simple workflow,but when comes to large workflows which already got a huge number of activities in it, the more number of activities we use the workflow will become more complex and difficult to maintain in the future.

Though it’s my perspective, thanks for the suggestion will remember that in future.

Thanks

10

This code is invalid.

Where() method is used to filter dt, not transform.

Following code could be used in an assign statement.

dt2 = dt1.AsEnumerable().Select(Function(row) dt2.rows.add(row("col1").ToString.Replace("from","to"))).CopyToDatatable()

But IMHO best approach is proposed by @prasath_S

Cheers

1 Like
11

Hi Prasath

I got the error message

Invoke code: Exception has been thrown by the target of an invocation.

image

image

image

12

With try catch the invoke code and exception.InnerException.Message you can get details on the exception

it looks also that the DataTableVar was not passed, so we expect a reference not set exception.

But the more important thing is:

  • Your initial LINQ was doing a particular ordering
  • this LINQ is doing a filtering / replace

What you want to achieve?

13

Pass the dt variable and make sure the column name matches with the excel.

I use invoiceno because that was my column name,use with the one you have.

Thanks

14

image

still no changes on the data “740_” still exist

15

Try this code I can see that you hae. dtpier has variable name instead of dt.

dtPier.asenumerable.where(function(row) row(“invoiceno”).tostring.startswith(“740_”)).tolist.foreach(sub(row) row(“invoiceno”)= row(“invoiceno”).tostring.replace(“740_”,“”))

Thanks

16

Reconcile_removechar.xaml (8.9 KB)
tests.xlsx (8.6 KB)

still did not change

1 Like
17

See how much trouble it is to help them get this code correct?

Activities would be easier.

18

@Abang_Jamuri_Abang_Shoker - please check this…

image
image1567×740 50.9 KB 

dtInput.AsEnumerable().Where(Function(row) row(2).ToString.StartsWith("740_")).ToList.ForEach(Sub(row) row(2) = row(2).tostring.replace("740_",""))

You can change row(2) with row(“invoiceno”)

If you don’t have large # rows then you can easily do the conventional method suggested by @postwick

1 Like
19

Thanks prasath17,
manage to get the output. I need write it back to the file. my mistake did not realize that.

thanks again for your help.
I need this method of query because it involve more than 100 lines that need to edited.

Thank you

20

Yes @postwick activities are easier I agree ,but this is the best i can think of both time saving as well as minimize the complexity of project.

Glad that worked in the end.

Thanks