How to delete entire row in excel if the specific column is empty

[Nur.ain]

Hi everyone,

I have following for filter and delete rows (excel activity) and tick for “has header”. It works but the result has been deleted with the header. Is it because the header start from Row “A3”? Cannot use specific rows or index because the data might be huge and did not know what the index might be empty.

Do you have any solution on this? If header start from A3 and can delete the entire last row where the column is empty.

Input:

Expected result:

Thanks

[Palaniyappan (Palaniyappan P)]

Welcome back to forum

Have a view on these threads where similar discussion happened earlier with solutions mentioned

How to delete entire Row if a specific column has empty cells

Hi @Tarif_Mohammad , As you have already tried with Filter Datatable Activity and Couldn’t Get the Expected Output, We can Give a Try on Linq Operations. Assuming that you already read the excel sheet using Read Range Activity and Get the Output as Datatable. We can filter the Datatable to Keep only the Rows needed as per your Requirement . Let’s Say we have Datatable variable, DT, Then we can use the Below Linq Expression in an Assign Activity: rowArray = DT.AsEnumerable.Where(Function(x)N…

Remove Empty Rows From DataTable

Hi @Joshikumarav, Use this code 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 information. Regards, Arivu

Hope this helps

Cheers @Nur.ain

[supermanPunch (Arpan)]

@Nur.ain

If header start from A3 and can delete the entire last row where the column is empty.

Hi @Nur.ain ,

Could you let us know what were the activities used ? Was it Workbook or Excel activities ?

With Read Range Workbook activity we get the proper datatable output when Range is kept blank.

From Implementation and Debug :

The Input Excel sheet :

[supriya117 (Supriya Allada)]

Hi @Nur.ain

Read range workbook (originalDt)
Try this linq query in assign:

filteredDt = originalDt.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

Write Range Excel (filteredDt)

[Nur.ain]

My workflow like this. I’m using Excel activities

[Nur.ain]

I try do as the as you given but the result not as expected. My header not start from A1. It start from A3.

The result should be as below screenshot:

I think if header start from A1:C1, yes the result as expected. Did I need to change or set anything to start from A3:C3?

[Nur.ain]

I try and the result different because my header start from A3:C3

[Palaniyappan (Palaniyappan P)]

@Nur.ain

Did I need to change or set anything to start from A3:C3?

While writing with write range activity yu can mention the start range u want
There just mention the first cell from where u want to start like “A1” or “A3”

@Nur.ain

[Nur.ain]

I did the cell A3:C3 the result was like did not happen anything.

Result:

[lrtetala (Lakshman Reddy)]

Hi @Nur.ain

How about the following?

(From row In DT.AsEnumerable()
          Where Not String.IsNullOrEmpty(row.ItemArray(1).ToString())
          Select row).CopyToDataTable()

I/P:

O/P:

Hope this helps!!

[Nur.ain]

Yup, correct! But, I want to use the same “Sheet1”. If I do write range for sheet 2 yes it did it.

Do you have any way to do in the same Sheet?

[supermanPunch (Arpan)]

@Nur.ain

Do you have any way to do in the same Sheet?

Hi @Nur.ain ,

For Writing to the Same sheet or overwrite the data existing, sometimes the data to be written is lesser than the ones present in the Excel sheet. In these cases, we would require to clear the sheet contents at first and then write the Data to the sheet.

For this one of the methods is mentioned below :

Delete Excel data range without Excel Installed

@kasey.betts , Assuming that we have the Data already available in Datatable, which Represents the Data in the Excel, we can use it’s Column Count and Row Count information to Clear the Contents using Write Cell Activity. For Example : Let the Datatable be DT We get the Column Letter of the Excel by using the Column Count information as follows : columnLetter = UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(DT.Columns.Count+1) The above would help us get the Col…

We could also create empty rows and add/append the empty rows to the Datatable according to the total rows present in the Existing sheet and then write the datatable to the same sheet. Topics related to creating empty rows :

Creating Empty Datatable

We have understood so far An existing Worksheet is to update the new data is less and old rows are not deleted When the idea is about creating a datatable with empty values and rows count of old data give a try at the following Read range - dtOld Assign Activity: dtCleaner = Enumerable.Repeat(dtOld.NewRow, dtOld.Rows.Count).CopyToDataTable

[Palaniyappan (Palaniyappan P)]

@Nur.ain

I did the cell A3:C3 the result was like did not happen anything.

mention the range just as “A1” then it will start from first cell and not “A3”

we dont need to mention fully like “A3:C3”
Just mention the first cell within double quotes

@Nur.ain

[copy_writes (chethan)]

HI @Nur.ain

Please follow the below Steps.
Step1: use Filter data table Option
Step2: select remove in that provide the column name and and is empty.

Delete Empty Row.xaml (8.1 KB)

If you are dynamic cell is empty then you have to look and put the if condition and remove the rows or delete row activity and delete