Remove Empty rows in the data table


#1

New Microsoft Excel Worksheet (3).xlsx (8.2 KB)

I want to delete the empty rows from the data table and need to write into another excel without any empty rows


#2

you can just loop the data table and check the length of the row item and write it into the new sheet or you can write the select statement with the isnotnull condition and write the result into the target excel


#3

Hi @skrishna,

Use this code to remove all column empty in the row.

DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

If you want to remove the row based on the particular column value

DataTableName=DataTableName.Select("ColumnName <>''").CopyToDataTable()

Regards,
Arivu


If excel cell empty
#4

@skrishna
Use this Query
(From row in dt.AsEnumerable()
where ( From p in dt.AsEnumearble()
where string.Is null or empty((string.Join(",",p.ItemArray)).Replace(",",""))
Select p).ToArray.Count<1
Select row)

You will get it in an array of DataRows, you can convert to datatable by using dt.CopytoDataTable.
Here dt is your datatable variable.

Regards,
Mahesh


#5

thanks, For the answer , My problem solved :+1:


#6

try this :How to Delete or Remove Data Row using a WHILE LOOP


#7

@arivu96 : I want to delete the row base on one column which contains null value.
I used below method to delete those rows
DataTableName=DataTableName.Select(“ColumnName = ‘’”).CopyToDataTable()

But it did not work.
Could you please Suggest.


#8

Hi Suchi,

Datatable_Name.Select(“Column_Name <>’’”).CopyToDataTable()

I guess this should work.


#9

Had the same problem. Solved with your solution. Thanks :+1: