Delete empty rows in excel

Help Studio
, ,
1

My data was coming from database and writing in the excel with empty rows, i need to delete empty rows in excel. Why because i cant move to next process with being this empty rows.
1.Bulk data is there with empty rows need to delete empty rows.
2.after removing if i open excel sheet need to see with out empty rows. Then only i can proceed my further process…
Please help guys…
Thank you

2

Can you share the sample input excel file with the empty rows @Aregala_Yedukondalu

3

Hi @Aregala_Yedukondalu ,

Could you try with the below Expression :

DT = DT.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or String.IsNullOrWhitespace(field))).CopyToDataTable()

Here, DT is the datatable retrieved from Database.

To Handle errors with Direct CopyToDatatable, Check the below post :

4

Thanks
But i am using C# Can u help in this…

5

Sure… Here is the sample file
Book2.xlsx (9.0 KB)

6

Hi @Aregala_Yedukondalu you can also use the filter data table activity to remove the empty rows.

7

@Aregala_Yedukondalu ,

Could you Check with the updated Expression below :

DT.Rows.Cast<DataRow>().Where(row=>!(row.ItemArray.All(field=>field == null | String.IsNullOrEmpty(field.ToString())))).CopyToDataTable()
8

Hello @Aregala_Yedukondalu
You can use the Filter Data table, While data get from the database is stored in a Data Table variable.
Filter the data table using the not empty conditions and save it in a another Data table variable and write into excel sheet.

image
image1039×716 29.1 KB

10

@Aregala_Yedukondalu ,

Could you try writing the Datatable to a different Sheet ? Sheet2 ?

Check if the data in Sheet2 is appearing properly, we could then decide if you want to modify the Sheet1 data itself or if you could consider the Sheet2 as the Output sheet.

11

In sheet2 data appearing properly but i need in sheet 1 only
Thanks…

12

@Aregala_Yedukondalu ,

If that is the case, then we can perform a Clear Data operation using Write Cell Activity first on the Sheet, then you can use Write Range Activity on the Same sheet.

If you have access to Modern Excel Activities, then you could try with the below Approach as well using Clear Sheet/Range/Table Activity.
image

If modern activities are not preferred, then you could check with the below post on using Write Cell Activity to Clear the Sheet.

13

I posted a sample sheet in real scenario there are multiple headers and multiple sheets are there…
Thanks…

14

@Aregala_Yedukondalu
Find the XAML attached here, the Same concept can applicable for all sheets, read the range and pass the DT by removing the empty vales, it will remove and filter the data.
Forum_FilterEmptycell.zip (14.3 KB)

15

Thank you so much… now its working fine @supermanPunch

1 Like
16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.