Removing empty rows from excel sheet or datatable

hi can you tell me the simplest way that how to remove empty row from excel sheet ??
i have implemented the below way but i want simplest way from below solution
abc.xlsx (11.4 KB)
Main.xaml (16.1 KB)

Hi @supriyad,

Refer the below code to remove the empty row from the Data Table.

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

Refer below link for more

7 Likes

@supriyad Check attached file

Main_updated.xaml (18.5 KB)

1 Like

use the new activity filter data table

4 Likes

Hi Akila/Supriya,

There is no need of hard code to remove empty rows from excel sheet or dataTable.
You can simply remove empty records using Filter DataTable activity.

Kindly refer the below image for reference. FilterDT

Regards,
Abhinav:smiley:

5 Likes

@supriyad According to your Excel file you can also check this.Remove_Empty_rows.zip (31.4 KB)
Thanks,
Apurba

1 Like

This helped me. Thanks !

Hi @preetith,
Most welcome dear. If you have got the solution, please mark the post, which you think your solution as ‘Solution’ and close the topic.

Thanks & Regards,
Apurba

Hi I have same issue.Can we do this without using read range.because I have large data.Read range activity executing from last 6 hours.But did not got data in data table.I have 6,00,000 rows in excel.In which data contain only first 15,000 rows.So I want to delete rest of rows
@Lahiru.Fernando

Hi @indra,

How can i handle if i need to delete both empty rows and columns in a excel, please can u suggest.
Thanks,
Vikram.G

Hi All,

You can use the below Linq query to delete rows which has empty cells in a datatable
Use assign activity and assign the below query to a new Datatable.

Datatable.AsEnumerable.where(Function(row) row.ItemArray.All(function(col) not string.IsNullOrEmpty(col.ToString))).CopyToDataTable

3 Likes