How to select ALL values that aren't in an array

Help
,
1

Hi.

I have two variables, one is strOutputDatatable, a string variable that contains values from a datatable outputted to string, and arrayCodes, an array of string values that has codes that I need to validate the datatable.

I’ve got each value of arrayCodes stored in a string variable called item and want to remove all values in strOutputDatatable that aren’t found in the item variable.

I’m wondering how would I be able to remove ALL values in strOutputDatatable using item? I assume I would use an IF statement and str.Replace, however I’m not sure how you would remove all values that aren’t found in item (which is each value contained in arrayCodes).

Thanks in advance :grinning:

2

Hi.

Well LINQ and lambda expressions are pretty powerful when used with enumerables and arrays.

item.Any(Function(x) x.Trim.ToUpper=value.ToString.Trim.ToUpper)

That will basically return True if a value is equal to any of the values in the array (you can also use .Contains or any other boolean expressions)

So if you couple that with a datatable, you can use .Where() to pull in certain rows. Or, if you use a string if you .Split it by a certain character.

For a datatable, you can do it like this:

dt1.AsEnumerable.Where(Function(r) Not item.Any(Function(x) x.Trim.ToUpper=r(column).ToString.Trim.ToUpper) ).CopyToDataTable // or .ToArray() for list of rows

So that will pull in all rows that do Not have a value in the item array.

If you use a string, you would replace dt1.AsEnumerable with str.Split() and split by probably the NewLine character, then also swap r(column) with r.Split(","c)(column)

…
You can also combine one of these methods with a For Each loop
For example,

For Each row In dt1
  If item.Any(Function(x) x.Trim.ToUpper=row(column).ToString.Trim.ToUpper)
    Delete Row

(you can also do item.Contains(row(column).ToString.Trim.ToUpper) instead of item.Any()```

However, I find the .Where() method more useful and less cluttered, but that’s just me.

Note: if you use a string instead of a datatable, you must String.Join() the array back together again, after you manipulate the data.

I hope this helps and sorry if I misunderstood any challenges you had.

Regards.

1 Like