Remove empty row in the excel


#1

Hi,

I have a requirement to delete an empty row which is anywhere in excel while iterating entire table data and store the non empty rows into another excel.I tried to check whether the row is empty or not but unable to find any condition.Give me any solution for this.


#2

@amkmukesh788,

Try with macro coding in Excel.

regards,
Vikas Reddy


#3

You have plenty solutions for this. First would be to read the excel data and dump it into a datatable (with the read range activity). Do do a simple loop or a Select query and get the non-empty rows.
Second one would be to automate excel application as any other UI app by enabling filters, filter the data by non-blanks and copy the remaining rows into the desired file

rgds,
Teo


#4

I would avoid the option suggested by @vikas_reddy_Vicky because this would mean you need to maintain code in two places. In general, only use Excel macros as a last resort.


#5

Hello,

as mentioned before there are several to do this, the one i give under has the advantage to handle white spaces if you need to.

You can use a ‘For Each Row’ with a ‘If Activity’ then ‘Remove Row’

‘row’ being your row on the For each row

string.IsNullOrWhiteSpace(string.Concat(row.ItemArray).ToString)


#6

I doubt that this will work. Does it?

The reason explained here - the collection you iterate through can not be modified within the iterator

However, @aksh1yadav method here works too, just use RemoveDataRow instead of InvokeMethod


#7

Yes you are right, omitted this detail, Thank you for spotting that

The solution you quoted works to check if 1 or several specific cells are empty but let’s say if you want to keep the row partially empty, it would not work i believe.

Or else you would need a quite long Select.

then to do so i would use Assign on a New List(of Datarow), (because i love LINQ :wink:

Having the assign value of : dt.Rows.OfType(of DataRow).Where(Function(r as datarow) string.IsNullOrWhiteSpace(string.Concat(r.ItemArray).ToString) =false).toList()

This should remove only the fully empty(or white space) rows and can be use for any type of datatable

What you think about that ?


Isnothing(datarows) not working
#8

could this be a new feature in UIPath? ie. to have a checkbox in Read / Write Range activity similar to “Ignore empty rows”


#9

What about If you want to only take rows where a certain column is empty? So check whether column 10 is null or not, and if it is then take those rows for further proccesing. I’ve been trying Row(10).IsNullOrWhiteSpace(String…Whatever ending I make up but as My VB is pretty ordinary I haven’t been able to work it out. Any Pointers guys?
Ryan


#11

Hi All,
Firstly, i appreciate the answer given by Vicky, Vikas Reddy.
Reason is, RPA solutions should be able to minimize complexity not maximize.

it is obvious that you guys are good in .net and other nested commands. but just a macro to delete a empty row will suffice.
Automation is supposed to give quick and easy solution, so people will be able to -

  1. Understand
  2. feel comfortable
  3. use the solution in their automations.

If i have a rubber band i will use it, i will not see how long it will stretch. because it will snap.

for such small problem such complex coding is not necessary, i think.
Well there will be scenarios where complex coding is required, i think they should be used there.

Keep it simple. Happy Automation.

Vivek


#12

Cheers gave it a go, (!DBnull.Value.equals(row[10])) in the condition
space in my IF block but it’s telling me Identifier expected. I assume !
or Value stand in place of something?


#13

Hey @Ryan_Madden

Logical Negation like above operator is not supported in expression editor. You can use like this as well:

NOT DBnull.Value.equals(row[10])

Regards…!!
AKsh


#14

Hi My Friend,

i get in the console output when i use your advice, the error is : “For each row : Collection was modified; enumeration operation might not execute.”


#15

That’s right, I provided (I think) another approach on my next post on this topic.

Having the assign value of : dt.Rows.OfType(of DataRow).Where(Function(r as datarow) string.IsNullOrWhiteSpace(string.Concat(r.ItemArray).ToString) =false).toList()

Let me know if there is any issue with that

Cheers


#16

I realize this post is a bit old, but in case anyone is searching for help I figured I’d post my approach. Sorry I can’t upload an actual .xaml due to company restrictions.

This way is a bit more customizable as it can filter on specific columns. I have it saved as a code snippet, which I’d highly recommend.

Put the following in a sequence:

1. Assign Activity: data_to_del (array of DataRow) --> datatable.Select("[COLUMN] is null")

     * This stores the empty rows of [COLUMN]  in an array of DataRows. Multiple columns can be included in the select statement

2. For each row in data_to_del (be sure to update the TypeArgument property to System.Data.Datarow)
Remove data row

This is just a quick sequence that can be added almost anywhere and is easily customizable to quickly remove empty rows from a datatable. It can also be modified to search for specific values other than null values by modifying the select statement in step 1.


Excel Automation process