Remove Empty Rows From DataTable

Help
,
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!

How to delete Blank/Empty rows in an Excel
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

12 Likes
How can i delete rows that can be empty or not
How to remove a row from a Excel Data Table?
Reading large csv file and need to change the date formats on output
How to delete entire row in excel if the specific column is empty
6

I just solved this using your code! Perfect man!

1 Like
7

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

8

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.

9

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

10

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

12

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

13

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

2 Likes
14

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

1 Like
15

Hi @kristen.roney,

Welcome to Uipath forum :slight_smile:

16

can i have c# code to delete empty rows in dt?