Remove rows with no data from datatable

[FrankWilson]

Hello,

I am looping through data and populating a datatable.
However, before writing to Excel, would like to remove rows that have no matches.

In example above, grape and pear would be removed, as they had no matches.
How can i do this?
Regards

[ppr (Peter Preuss)]

We can do:

Assign Activity
dtCleansed =

(From d in YourDataTableVar.AsEnumerable
Let chk = d.ItemArray.Skip(1).All(Function (x) isNothing(x) OrElse String.IsNullorEmpty(x.toString.Trim))
Where not chk
Select r = d).CopyToDataTable

Handling emtpy results we do:

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.AsE…

[FrankWilson]

Thank you so much.
Tested and works great.
Greatly reduces output.
Appreciate the help.

[FrankWilson]

Hi PPR,

Similar question. Will it be a new search to remove the empty columns as well.
Like Thursday had no match.
Regards,

[ppr (Peter Preuss)]

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operator [HowTo] Overview Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start…

removing rows where any col is a blank:

(From d in YourDataTableVar.AsEnumerable
Let chk = d.ItemArray.Skip(1).Any(Function (x) isNothing(x) OrElse String.IsNullorEmpty(x.toString.Trim))
Where not chk
Select r = d).CopyToDataTable

[FrankWilson]

That is the same formula.
Sorry for not being clearer.
Is it possible to remove columns as well that have no matching data?
Like Thursday above?
Regards

[ppr (Peter Preuss)]

@FrankWilson

That is the same formula.

No is not the same on one we are using All on the other we are using Any.

We recommend to use the reference information and have a closer look on LINQ and its operators

[FrankWilson]

Thank you. The remove rows worked well. However, removing empty columns results in “value cannot be null” error.

[ppr (Peter Preuss)]

we prefer to have scoped 1 topic = 1 use case
just open a new topic for your second question and share with us

• sample input example data
• expected output sample
  Thanks