Filter Blank rows in Excel using Linq

[Manii_K]

Hi All,

I need to filter Blank rows from Column “Posted” and Move Blanks to “Not Posted” Sheet and remaining to “Posted” Sheet, Anyone pls help Thanks

[ashokkarale]

@Manii_K,

Use this Select query to get non posted rows.

dtNonPosted = dtInput.Select("[Posted] = ''").CopyToDataTable

User Write range activity to write the dtNonPosted to desired sheet

[Sanjay_Bhat]

Hello @Manii_K

Get data to a data table say dt_new
Posted Use this query: dt_new.AsEnumerable.Where(Function(x) Not String.IsNullOrWhiteSpace(x(“Posted”).ToString)).CopyToDataTable

Not Posted : dt_new.AsEnumerable.Where(Function(x) String.IsNullOrWhiteSpace(x(“Posted”).ToString)).CopyToDataTable

Thank You

[Manii_K]

Thanks Ashok and Sanjay

My Data is in Datetime format so so mentioned queries not working

Can you guys pls help on this

[Manii_K]

Thanks Sanjay, Its not Working Posted Query is bringing the Empty rows also

[Manii_K]

Thanks ashokkarale, Its not working, Showing Operation cannot be perform for Datetime format, can you pls suggest anyother

[Sanjay_Bhat]

Can you write the query to a new posted sheet not the input posted sheet then the query should work
testforforum.zip (10.2 KB)

Thank you

[ashokkarale]

Ok @Manii_K then use Filter Datatable activity.

[Nitish_Singh]

You can give a try to this code.

Create two datatable. postedTable & notPostedTable.

postedTable = originalTable.AsEnumerable().
Where(Function(row) Not IsDBNull(row(“Posted”)) AndAlso row.Field(Of DateTime)(“Posted”) <> DateTime.MinValue).CopyToDataTable()

notPostedTable = originalTable.AsEnumerable().
Where(Function(row) IsDBNull(row(“Posted”)) OrElse row.Field(Of DateTime)(“Posted”) = DateTime.MinValue).CopyToDataTable()

Here row.Field(Of DateTime)(“Posted”) <> DateTime.MinValue : Ensures that the date is not the default DateTime.MinValue, which could represent an empty date.

[Manii_K]

Thanks Sanjay Its working