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.
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
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.
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
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 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
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 -
Understand
feel comfortable
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.
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?
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()
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.