Excel remove empty rows

Hey guys,
Can someone advise how can I remove empty rows from excel file attached?
Filter_empty.xlsx (196.1 KB)
I used Filter Data Table activity with option of Remove rows where row in field “sFound” Is Empty and after it used Write range activity to write filtered Data table back to the excel file, however not all empty rows from excle file were removed. How can I fix it?

@Apple1
a quick look to excel did show that there are some rows where the column values have a blank
find following alternate approach to filter out the non blank rows:
RemoveBlanks.xaml (6.5 KB)

@ppr
can you explain what is done in the code:
(From d In dtData.AsEnumerable
Where Not If(isNothing(d(0)), True, String.IsNullOrWhiteSpace(d(0).toString.Trim))
Select d).CopyToDataTable
?

@ppr
can you please create and send me .xaml file using my Excel file and removing all empty rows?

@Apple1
sure it is using LINQ

(From d In dtData.AsEnumerable

  • iterate over dtData, the datarow of current loop is referenced with d

Where Not If(isNothing(d(0)), True, String.IsNullOrWhiteSpace(d(0).toString.Trim))

  • let pass the filter check if the d(0) [current loop row first column] is not null or String.Empty

Select d).CopyToDataTable

  • Select the row passing the filter and copy it to a new datatable

Find starter help here:
RemoveBlanks_1Col_ExcelDemo.xaml (6.4 KB)

Adopt the Excel path as by your environment

1 Like

@ppr it works thank you!
But when I use this code with another Excel files it leaves old rows below the range where empty rows were removed, I think I need to clear excel sheet before writing new cleaned range in exsiting file, can I do it in the same code you wrote?

@ppr
I could clean an Excel sheet with Write cell activity and everything works now.
Can you please explain what is going on in this code step by step?
(From d In DTFilter.AsEnumerable
Where Not If(isNothing(d(“sFound”)), True, String.IsNullOrWhiteSpace(d(“sFound”).toString.Trim))
Select d).CopyToDataTable

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