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.
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
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?
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”…
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.
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
@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.
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)
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
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!
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.
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.
@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 
I was able to solve the issue using filter wizard, followed by a for each. Thanks!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
