How to extract the empty rows in an excel sheet

Help Studio
1

Hi,

I have an excel sheet where “Name, Location and Designation” are as headers. I want to extract the entire rows based on the Location if there is any empty cell either in Location or Designation.

image

How can we do it. please help me.

Regards,

2

Hi @raju_alakuntla

I guess you mean that you want to remove the rows where location or Designation is empty.
You can use a filter Data Table Activity, delete all rows from your sheet and then enter the new DataTable which you’ll get as an output of Filter Data Table activity.

Thanks

3

Hi @raju_alakuntla ,

You can use the filter datatable activity and Keep the rows where Designation is present or Remove where not present.
Please let me know if you are facing any issue.

4

No @adiijaiin ,
I want to send a mail separately based on the location if any cell in the “Location or Designation” column is unfilled.

In the mail I need Name, location and designation.

Lets consider if D3 is unfilled, then I need to send a mail to Hyderabad team that D3 is unfilled along with the Name, Location and Designation.

likewise I need to send mails to other location teams but if there are multiple unfilled cells for a particular location is there then I need to send a list of persons Names instead of sending multiple mails.

Regards,

5

@raju_alakuntla So You mean you need the name column which has either of designation and location column empty to send mail?

6

Hi @raju_alakuntla
Question: If you don’t have a location filled, to which team would you send this mail to.

You can filter and keep the rows where location is empty or designation is empty.

So you’ll have the list with you. Now based on that lets say designations aren’t filled for some records you can get a list of cities by getting just the city column and placing a remove duplicates logic so you’ll have unique cities names.

Now you can loop thru CityDT over the records and filter it separately and send it as a list.

Thanks

7

yes @tazunnisa.badavide

8

@raju_alakuntla In that case the below xaml would help help you!
new1.xaml (10.5 KB)
Please Let us know if it works!

Regards,

9

can you please send it in a zip format as I am not able to open this xaml

Regards,

10

newPr.zip (57.6 KB)
Regards,

11

@raju_alakuntla Any Updates…

12

Hi @raju_alakuntla ,

You Can try this Query

Input_Dt.AsEnumerable.where(function(a) a(“Location”).ToString=“” or a(“Designation”).ToString=“”).CopyToDataTable

Cheers,

13

Getting this error

For Each Row in Data Table: Collection was modified; enumeration operation might not execute.

regards,

14

@raju_alakuntla

hi man

dt.asenumerable.where(function(r) r(“Location”).tostring.equals(“”) or r(“Designation”).tostring.equals(“”)).copytodatatable

15

if possible can u send xaml

16

If possible can we connect on google meet now.

17

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.