Remove Empty rows in Excel

How to remove empty rows in excel by using linq queries

Thanks,!

Ex:

image

@ravisinghdas199

Can you breif remove a row based on a column or what

Read Range
WorkbookPath: “path_to_your_excel_file.xlsx”
SheetName: “Sheet1”
Range: “”
Output: dtOriginal (DataTable)

If entire row is empty then use this linq query

Assign 
dtFiltered = dtOriginal.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(field) String.IsNullOrWhiteSpace(field.ToString()))).CopyToDataTable()

If Specific Column is empty

Assign 
dtFiltered = dtOriginal.AsEnumerable().Where(Function(row) Not String.IsNullOrWhiteSpace(row.Field(Of String)("Column1"))).CopyToDataTable()

Then Use write Range activity.

Hi @ravisinghdas199

Do you mean you want to remove the rows where the Column Zip is empty

If yes

filteredTable = dataTable.AsEnumerable().Where(Function(row) Not String.IsNullOrEmpty(row.Field(Of String)("Zip"))).CopyToDataTable()

Right??

Hi @ravisinghdas199

You can use the LINQ Expression,

- Assign -> Output_dt = Input_dt.asenumerable.where(Function(X) Not String.isNullorEmpty(X("Zip").toString)).Copytodatatable()

This will remove the empty rows and store in Output_dt.

Check the below workflow for better understanding,
Sequence

Input -
image

Output -
image

Hope it helps!!

Hi @ravisinghdas199 ,

Example Workflow in UiPath Studio

  1. Read Range Activity:
  • Properties:
    • SheetName: "Sheet1"
    • Range: "A1:B10" (or leave blank to read the entire sheet)
    • Output DataTable: dt
  1. Assign Activity:
  • Properties:
    • To: dt
    • Value:
dt.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value OrElse String.IsNullOrWhiteSpace(field.ToString()))).CopyToDataTable()
  1. Write Range Activity:
  • Properties:
    • SheetName: "Sheet1"
    • DataTable: dt

Example Data Before and After

Before:

column1 columnb
aa 123
aa
bb 123
bb

After:

column1 columnb
aa 123
bb 123

Regards
Sandy

Hii @ravisinghdas199

Try this:

DT1.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value)).CopyToDataTable()

We can use

Filter DataTable

When it is about checking a particular column:
Assign Activity:
dtFiltered =

(From d in YourDataTableVar.AsEnumerable
Let chk = isNothing(d("YourColName") OrElse String.IsNullOrEmpty(d("YourColName").toString.Trim)
Where not chk
Select r = d).CopyToDataTable

When it is about that remove the row when a blank is present in any column

Assign Activity:
dtFiltered =

(From d in YourDataTableVar.AsEnumerable
Let chk = d.ItemArray.Any(Function (ia) isNothing(ia) OrElse String.IsNullOrEmpty(ia.toString.Trim)
Where not chk
Select r = d).CopyToDataTable

Handling empty result:

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