If your UiPath automation chokes on blank lines in Excel you are not alone. Empty rows are the little gremlins of RPA they hide in spreadsheets and break your happy flow. This short tutorial shows reliable ways to detect and remove empty rows from an Excel sheet using DataTable operations and core UiPath activities so your automation runs like it was fed coffee.
Use Read Range from the Excel or Workbook package and store the result in a variable named dt
or any sensible name that future you will thank you for. Make sure the spreadsheet has named columns or at least predictable headers. Working with a DataTable is cleaner than string parsing drama.
The cleanest approach is to use DataTable filtering and then CopyToDataTable. If one column defines whether a row is empty use a Select expression and replace the original table with the filtered result.
dt = dt.Select("Not (ColumnName Is Null Or ColumnName = '')").CopyToDataTable()
This keeps rows where ColumnName is not null and not empty. It is fast and elegant when one column determines whether a row is meaningful.
If emptiness means several columns are blank build a combined filter. For example if ColA and ColB both must be blank to count as an empty row use an expression like this and then CopyToDataTable.
dt = dt.Select("(Not (ColA Is Null Or ColA = '')) Or (Not (ColB Is Null Or ColB = ''))").CopyToDataTable()
If you need to consider a row empty only when all relevant fields are blank build the expression with And instead of Or. If CopyToDataTable fails because no rows match wrap the logic with a check on the result length to avoid runtime exceptions.
Sometimes filters are awkward for mixed types or complex blank rules. In that case use a loop that deletes rows from the bottom up. Deleting from bottom to top avoids skipping rows when indexes shift. This works both inside a DataTable loop or by using Delete Range or Delete Row activities against the Excel sheet.
dt.Rows.Count
dt.Rows(i)
and call dt.Rows.RemoveAt(i)
when the row is emptyTracking indexes avoids the classic off by one chaos and keeps your automation deterministic. This approach is more code heavy but gives fine control for edge cases.
Use Write Range to overwrite the original sheet or write to a new sheet if you want a rollback. If presentation matters use AutoFit on the range after writing because stakeholders will judge success by neat columns.
Fixing empty rows is one of those boring but essential RPA chores. Do it once cleanly and your downstream logic will stop staging dramatic exits. Now go remove those blank rows like the automation hero you are.
I know how you can get Azure Certified, Google Cloud Certified and AWS Certified. It's a cool certification exam simulator site called certificationexams.pro. Check it out, and tell them Cameron sent ya!
This is a dedicated watch page for a single video.