Run report, export into excel, parse and return specific data

[ThatRealEstateG]

Hi everyone,

I am trying to run a report from my work system and pull specific information from it. The report comes in as a normal .xlsx or .csv. Is there a way to tell PA to recognize the email with the attachment and have it save the spreadsheet into a table, then look for specific data on the table and report that back to me?

[Rhiassuring]

Yep, totally.  Does the email always have the same subject when it sends it to you? That would be one way to mark it. 

To answer the rest, you'll need to give us a bit of an example around your incoming data and your existing table, and also what kind of specific data you're looking for. Like, what kind of scenarios?

Cheers,

Rhia

[ThatRealEstateG]

Yes, the email would always have the same subject (i set that when running the report).

I run a report that provides details on a home purchase. (buyer name, lot, price...etc) there is a bunch of columns. But I only need to flag certain details. I would want to tell the system what price to look for and report back to me prices on other homes which are higher than the one i tell it, and what their lot number are so I can quickly filter out any homes which are lower price.

[Rhiassuring]

Sure. 

So you can say "When a new email arrives" as a trigger, and set the Subject filter to whatever that "same subject" is every time. 

Then, you need to put your attachment somewhere. OneDrive maybe? 

It will turn into an apply to each because it doesn't know if there will be more than one or not. 

From there, you can use the Excel actions to work with the Excel file you've just created, and the Excel file you want to move things into. 

[ThatRealEstateG]

Which format would work best? none of them export as a table. So what you're saying is, I would have to have it save the attachment to a pre-saved table and once it saves it will trigger the next step?

[Rhiassuring]

Hmm. I think Excel would work best. 
No, it doesn't need to be a table, it's just easier -- the Excel that you want to put the data INTO needs to have a table, though. 
Can you give me an example of the data? 
Like, this Excel Output might have columns like: Unimportant1 Unimportant2 HousePrice HouseSeller Unimportant3

And your Excel sheet where you collect everything might have ID HousePrice HouseSeller HouseSoldDate etc - and your table name might be Houses or something. 

(Just to give some context so I can work through it in a way that'll make sense to your situation.) 

[ThatRealEstateG]

It looks like when I run it as a normal excel file there is a lot of blank spaces.... (screenshot below)

when i run a csv file it removes all the extra spaces. this seems like it would make it easier to parse correct?

[Rhiassuring]

In that case, yes, haha. 

Which columns do you need to pull into your other spreadsheet?

[ThatRealEstateG]

I need lot_block, planName(or “plan” would work), sales_agreement_amount, estimated_coe_date

i need it to tell me which plan matches my subject property plan, make sure the estimated_COE_date is within the last 6 months and give me the ones that meet those first criterias and tell me the ones who’s prices are higher 

[Rhiassuring]

And, when you say "tell me the ones" - in what format? Do you want an email with a table? Do you want it to create an Excel table with just these ones? Do you want the flow to automatically override the one with the higher prices?

Here's the structure so far. In this flow, I've gotten the CSV, removed the header values, removed any nulls, split it by each row, then gotten the table of data from your master (to compare to) - then inside the for each, split by comma again, turn it into JSON so it matches Excel, parsed it so it's easier to use, and then filtered based on planName = plan name in your master. 

The next step is to create the output, but we need to know exactly what that looks like.

I'm using CSV content from OneDrive but of course you'd get it from ... well, wherever yours is, or whereever you put it.

Split: split(outputs('Get_file_content_-_getting_CSV_data')?['body'],decodeUriComponent('%0D%0A'))

Exclude Headers: skip(outputs('Compose_-_split_by_line'),1)

When bringing in your Excel data from the master, it'd be good if you can filter it to relevant data. Not sure what would work in there - if you have a status of open / closed... if you have anything you can filter out, you could put that into the Filter Query.

The "Apply to Each" is manual, looking at the body of the filter array.

Split: split(items('Apply_to_each'),',')

JSON: outputs('Compose_-_split_by_comma')?[2] - each one has a # of where it is in the array

Then after the Parse, I'm just filtering the outputs of Excel based on the current PlanName we're looking at to see if there's a match. 

As soon as we do that, we have the data we need to work with. Just need to know what to do with it.

R

[ThatRealEstateG]

Wow, you made this seem so easy... haha. I will start to work on this and get it ready for the last step. 

I don't need anything fancy with the pulled data, just for it to list (excel, in rows on a email, what ever the simplest method of output is)

List view:

Plan (to match my subject property) | Lot_Block | Sales_Agreement_Amount

From there I can look in my system at the top 3 results and pull the rest of the details I need. I just need a quicker way to find those top 3 comparisons.

Also, now that I'm thinking about it, I also need the 3 closest to the subject property in case they aren't higher. 

So maybe just having it output the results in order from highest to lowest would be the easiest way.

So to answer your question, if it exports into an excel table that would be easy enough. 

[Rhiassuring]

"Top 3" - What qualifies them as being top 3? The biggest gaps between your sheets $ and the incoming $? Or just highest $ overall? 

And, how do we identify the "3 closest to the subject property" - is it based on Lot Block? 

[ThatRealEstateG]

Highest overall, and closest in dollar amount. But if we just have it sort from highest price to lowest price it will be the top 3 i'll use.

I was caught up earlier, but need to ask. Above your list of items, am i missing something at the top? I need to start with "when a new email arrives"

Then go into what you sent? Sorry, i'm still new at this. So thank you so much for your patience! 

[ThatRealEstateG]

Hi Rhiassuring 🙂 Just checking back in on this. I wanted to make sure i had this started correctly

[Rhiassuring]

Hey there, 

You kind of do, yes - your filename should be a variable that you construct, unless it's okay for the new CSV to override the old one each time. 

As for the rest of the flow -- It makes a lot more sense to do this through typical Excel capabilities, rather than parsing it all through PA. It can be done, of course, but I think even using the Run Script action in Excel might get what you need much faster.  Unfortunately, I'm not a person who can Excel script. 

In any case, I've got to the point in the flow where we have an output array that shows us A) where there is a match between the CSV & master spreadsheet planName B) shows us the estimated value in the master spreadsheet vs CSV. 

As I'm building for demo, I didn't even include the filtering out of dates beyond the first 6 months - as I'd need to translate the CSV dates into real, sortable dates first.  The next step would be around the dollar sorting - which I'm still confused about your needs on. Three top highest - sure, no problem; from the spreadsheet, or the CSV? I'm guessing CSV. But then you also said closest in dollar amount - between the CSV & spreadsheet? How does that relate to the top 3? 

Some more requirements gathering needed 😉 

In any case, here is the rest of what was built - (made some changes) -  but yeah, quite a bit of work to get it right down to what you are looking for. 

PS: There are definitely cleaner ways to do this but I'd want to ensure all requirements are understood first, then spend time thinking about the best way - this is just demoing some of the different components.