Remove Empty Rows From DataTable

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!

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()

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?

Hey Alex,

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

Thanks
Joshi

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

10 Likes

I just solved this using your code! Perfect man!

1 Like

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

Hi I have same issue.Can we do this without using read range.because I have large data.Read range activity executing from last 6 hours.But did not got data in data table.I have 6,00,000 rows in excel.In which data contain only first 15,000 rows.So I want to delete rest of rows.

simply use the filter datatable and remove the rows which have empty primary column field.
Write back the datatable to another sheet
*Do not write into the same sheet as it will cause inconsistencies

Thank you very much! It was eay solved by the use of an assign activity.

can you tell me what is dtO.Tables(0)???

Thank you so much. I’ve been beating my head on the keyboard over this :slight_smile:

1 Like

This helped me today after hours of searching for the answer. Thank you!

Hi @kristen.roney,

Welcome to Uipath forum :slight_smile: