How to delete value in datatable with Query Datatable?

Hello,

how can I empty a value in a specific column in the datatable, when the value begins with “88” without using for each?

Thanks for your help.

Regards,
SaFa

Are you trying to delete a column with a name starting with “88”? You will have to iterate over the data, whether it’s by using For Each or a While loop.

Hey @SaFa,

So you want to remove the whole column if the header has 88?
Or do you want to remove just the row which has 88 in a certain column?

To remove an entire column you could use the Filter Data Table activity which has loads of options.

If you want to only remove a row with a certain column having an 88, then you need to do a for each, else it gets really messy.

Hit me up if you need more info,

Cheers
MikeB

Hi @MikeBlades,

I dont want to remove a row, I just want to clear the value if the value begins with “88”.
In this case i dont want to use for each, it should starts with datatable.asenerumble().where……

1 Like

I would like to know more about this as well.

It would be like this:
Dim customers = From customer In dt.AsEnumerable() Where customer.Field(Of String)("Country").StartsWith("88") Select New With { .Country = "New Value" }

Hello @bcorrea,
If I apply your code to my assign activity:
From row In dtPostings.AsEnumerable() Where row.Field(Of String)(2).StartsWith(“88”) Select New With { .row = “” }
I get the following error:

What should I do?

this will return an enumerable of datarows… but now i think, you want to return the same data table and only update the rows matching that condition, but this will only return the rows matching the condition…

Is there no other solution with if condition?

(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)

Can you try this:

(From row In dtPostings.AsEnumerable() Function(row) row(2).tostring=if(row.Field(Of String)(2).StartsWith(“88”), nothing,row.Field(Of String)(2))).CopyToDataTable

you seem to be missing some parenthesis there…

Hello Samir,

there is an error in your expression. Unfortunatly it is not working for me.

@SaFa (Edited Post, Uploaded new version)
Have a look on following Demo XAML:
SaFa_V2.xaml (10.6 KB)

Demo 1: Hybrid Approach - Prefiltering with LINQ, Updating Column with For Each
Demo 2: Updating Column without For Each Activity, LINQ Statement
Demo 3: Updating all Rows/Columns

Main Purpose was to give some examples for training / exploration help for you

  • Demo 1: its a well balanced use of LINQ and essential UiPath Activities
  • Demo 2: surpressing a for each activity and using LINQ is not avoiding in total a for each. Kindly check the ForEach Method used in this statement
  • Demo 3: Is a good example on the potential of LINQ in some scenarios, as it make things handy. But also here for each is done in background

Feel free to play with it along your data. Let us know your feedback

I agree. I do like this solution, though, since it arguably simplifies the code to one activity on one line.

Hello @ppr
your answer was the solution for my problem.
I did not use Demo 1 because there was a for each activity involved.
and also I did not use Demo 2 because it scans all cells in the datatable.

Demo 2 was perfect for me because I scan only specific columns and this is my Code:

dt.AsEnumerable.Where(Function ( r) r(7).toString.Trim.StartsWith(“88”)).toList().ForEach(Sub (x) x.SetField(Of String)(6,Nothing))
dt.AsEnumerable.Where(Function ( r) r(7).toString.Trim.StartsWith(“28”)).toList().ForEach(Sub (x) x.SetField(Of String)(6,Nothing))
dt.AsEnumerable.Where(Function ( r) r(7).toString.Trim.StartsWith(“88”)).toList().ForEach(Sub (x) x.SetField(Of String)(7,Nothing))
dt.AsEnumerable.Where(Function ( r) r(7).toString.Trim.StartsWith(“28”)).toList().ForEach(Sub (x) x.SetField(Of String)(7,Nothing))

This code is working for me but maybe there is a better way to summarize my Code.

Thank you all for the help.

Sorry but this is terrible solution… understand you start by saying you dont want to use For Each, but what this code is doing is a lot worse in terms of performance, as you are looping your datatable 4 times and using a lot of conversions… you could do all this in a single For Each Row and a Switch case activity… to a lot faster…

but my monitoring shows me that this way performs much better than a for each activity.

i dont know how you did your test, but i can assure this is quite hard to believe, unless there is something very wrong in UiPath’s implementation of a For Each…
oooooooooo
Just to leave here i just tested this on my Visual Studio and the total seconds for the 2 approaches were:
for each = 0,0009966
linq 2 lines = 0,003001
Meaning for each is more than 30 times faster… and i only did linq 2 times, becase i think you are doing 4 times but looks duplicated…
Code was:
Dim dt As New DataTable()
dt.Columns.Add(“column1”, GetType(String))
dt.Columns.Add(“column2”, GetType(Integer))
For index As Integer = 1 To index = 500
dt.Rows.Add({“row” + index, 888888})
Next
For index As Integer = 1 To index = 500
dt.Rows.Add(dt.Rows.Add({“row” + index, 288888}))
Next

Dim startTime As DateTime = Now

'For Each row As DataRow In dt.Rows
’ If (row(1).ToString.StartsWith(“88”) Or row(1).ToString.StartsWith(“28”)) Then
’ row(1) = Nothing
’ End If
'Next

dt.AsEnumerable.Where(Function® r(1).ToString.Trim.StartsWith(“88”)).ToList().ForEach(Sub(x) x.SetField(Of String)(1, Nothing))
dt.AsEnumerable.Where(Function® r(1).ToString.Trim.StartsWith(“28”)).ToList().ForEach(Sub(x) x.SetField(Of String)(1, Nothing))

Console.WriteLine((Now - startTime).TotalSeconds)
Console.ReadKey()

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