Remove Empty Rows From DataTable

datatable
excel

#1

Hello,

I found an older post with about the same question and I wonder whether some new solution has risen.

I basically have an excel sheet and I want to delete all empty rows. I tried reading it into a DataTable and removing every empty row but I just can’t find the right way.
It seems strange to me that something so simple and useful is so hard to do. Maybe it would make sense to add it as a feature in the next UiPath version.

Thanks!


#2

The following code will work.

dtO.Tables(0).AsEnumerable().Where(Function(row) row.ItemArray.All(Function(field) field Is Nothing Or field Is DBNull.Value Or field.Equals(""))).ToList().ForEach(Sub(row) row.Delete())

dtO.Tables(0).AcceptChanges()


#3

Hey @BikashPanigrahi thanks for the answer. I’m not sure how to use that code. Should it go in an Invoke code action? I guess dtO is the name of the Data Table. Is there anything else that has to change according to the case?


#4

Hey Alex,

did you achieve this through the above-mentioned function? Please do share your approach.

Thanks
Joshi


#5

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


How to remove a row from a Excel Data Table?
#6

I just solved this using your code! Perfect man!


#7

@alex.sparos I guess the final goal is to delete the empty rows in the Excel Sheet not in DT…