Deleting Empty Rows Using LINQ Cast Function

[NILESH.BOT369 (Nilesh)]

Hello Everyone,

I am trying to delete empty rows from DataTable - “DT” initialized as shown in below screenshot,

I have written code as below using LINQ Cast function,

(From row In DT
From col In DT.Columns.Cast(Of DataColumn)
Where Not String.IsNullOrEmpty(row(col).ToString)
Select row
).CopyToDataTable

But, this giving me error as,

Am i missing anything?

[vrdabberu (Varunraj Dabberu)]

Hi @NILESH.BOT369

Can you try the below syntax:

(From row In DT.AsEnumerable()
 From col In DT.Columns.Cast(Of DataColumn)()
 Where Not String.IsNullOrEmpty(DirectCast(row(col), String))
 Select row
).CopyToDataTable()

Regards

[NILESH.BOT369 (Nilesh)]

Hi @vrdabberu , thank you for your response,
Its still giving the error,

[Anil_G (Anil Gorthi)]

@NILESH.BOT369

1. Easiest would be filter datatable
2. Here is linq dt.AsEnumerable.Where(function(x) Not IsNothing(x(0)) AndAlso Not String.IsNullOrEmpty(x(0).ToString)).CopyToDataTable

Cheers

[vrdabberu (Varunraj Dabberu)]

Hi @NILESH.BOT369

Can you try the below syntax:

DT.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(cell) cell Is DBNull.Value OrElse cell.Equals(""))).CopyToDataTable()

Input:

Output:

Regards

[NILESH.BOT369 (Nilesh)]

Great, this works Perfect!, Appreciate, thank you.

[vrdabberu (Varunraj Dabberu)]

You’re welcome @NILESH.BOT369

Happy Automation!!