Excel: Only get data from rows that have data in 5 columns

Help
, ,
1

I have an excel sheet with a consistent format of columns like address, name, date, email, and phone. But I can’t just do a for each activity because after all of the data there is a Totals row, a blank row, another Total row, and a summary row that is in between the data I need.

How can I parse through the rows to only get the rows with the data I need (address, name, date, email, and phone)?

Thanks in advance.

2

Hi @eonofrey,

Use the for each row activity and if you say the table format will not change simply break the for each when you encounter a row that has total in it

3

Hi Paul and thanks for getting back to me so quickly.

So you’re saying to do a For Each, with an If statement inside and a break?

I figured out a pattern. All of the rows with data I need start with an address which always starts with white space and an integer. What would my condition be to check if column 1 has an int?

4

@eonofrey

Hello, i have an idea, why don’t you use a filter data table activity ? with this you can easly choose which data to keep and which data to delete… like keeping only the rows that contains “True”…

5

Hi @eonofrey,

Get everything and then just use “Filter DataTable” activity to remove all except the ones that meet your criteria in this case that all 5 columns are not empty.

6

Like this:

image
image700×481 17.6 KB

Also attached the sample xaml file.

Main.xaml (7.2 KB)

7

@eonofrey

Sure, assuming that in the first column you fill find the string “Total” at then end of the table

You could do something like

For each row as DataRow in table.rows

   if( row(0).tostring.tolower.contains("total") then
       break
   end if

  '' insert your code here
next

The above code would work in a invoke code,
If you want to do it with activities, the break activity will help you end your for each

8

@mz3bel @rmunro This is what I am troubleshooting now. I am trying to figure out what I need to put for the value in the Filter Wizard. I want to check if column 0 Contains any integer.

9

@eonofrey

Is all rows full is there any empty ? columns too ?

10

What about if it’s greater than 0

image
image704×71 2.06 KB

11

I am only concerned with the rows where column 0 contains a number

Some rows are empty, some say “total”, some act as headers. (I don’t want to include these)

12

@eonofrey

On Data Filter Table, choose delete, “Column0” which Is Empty and and other one “Column0” which contains “total”.

This way you delete all the rows that are empty or contains “total” on column0

13

But this wont take into account the rows that are headers. Thats why I was thinking instead of deleting all of those possible cases, I can filter by only including rows where column 0 contains integer. I just have to figure out the code for that!

14

You can also use IsNumeric check this other solution I made.Main.xaml (8.3 KB)

If you have questions let me know and I’ll be happy to explain.

15

@eonofrey

Ok, are the number only integers ? If so, do the parts i told you before and add this one too, “Column0” => 0.

This will give you only the rows, containing a number.

16

@eonofrey Can you Show us the Excel File, that’ll give us a clear Idea of what to do, or an dummy Excel with that kind of format would do :sweat_smile:

17

I was able to solve the issue using filter wizard, followed by a for each. Thanks!

2 Likes
18

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