how can I remove first 4 char from below data table using query
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()
Get cell value
Replace 740_ with “”
Write cell value
Read sheet to dt
loop through dt
Replace value and write cell
Please try below steps,
Read the excel sheet data in the datatable variable name dt.
Drag and drop the invoke code activity.
In edit arguments, create a variable dt, set direction as in/out and pass value as dt.
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_”,“”))
Why overcomplicate it with Invoke Code? Just use an Assign inside a For Each to change the value in each row.
It will reduce the numer of activities to use and will done in single activity.thats why
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
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
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.
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.
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
I got the error message
Invoke code: Exception has been thrown by the target of an invocation.
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?
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.
still no changes on the data “740_” still exist
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_”,“”))
Reconcile_removechar.xaml (8.9 KB)
tests.xlsx (8.6 KB)
still did not change
See how much trouble it is to help them get this code correct?
Activities would be easier.
@Abang_Jamuri_Abang_Shoker - please check this…
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
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.
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.