Create Opportunity in CRM from an Excel file

Find more post on my blog.

Remember that the Community is all about sharing knowledge, experience, and best practices with others.

Early this year a wrote an article on how to send an email with an attached excel file containing all leads created during the week, kind of like a summary report of all new leads.

This time I wanted to go the other way around, and actually create CRM Opportunities from an excel file.

The Scenario

Let's say that we have an excel file containing potential opportunities. This file might have been sent from partners all over the world as they don't have access to our CRM system.

But the file contains 200 rows of records, so we have to either created them one by one, or take the information we need, create our own template, and use it for bulk import in CRM.

But what happens if for some of the records we don't have an Account or Contact in CRM? The import will fail for these records and we have to create the Accounts/Contacts first before we can try to import them again.

The solution

I came with the idea to create a process using Power Automate which actually goes through an excel file, and checks if for the given email address (this is what I'm using as a unique identifier for Accounts and Contacts) there is an Account and Contact, if yes creates a new opportunity and add them there. If there isn't an existing Account and Contact, then it creates one and then proceeds and creates an opportunity and adds the newly created Account and Contact.

Here, I want to use the opportunity to say BIG THANK YOU to @Paul Murana who helped me a lot with the filter expression for checking whether the account exists or not. Please, take a moment and visit his blog as there a lot of power automate scenarios he described and might help you.

Let's begin

First, let's start with a trigger point. In my case, the flow will be trigger via a button as whenever our salespeople want to upload the opportunities from the file provided by our partners, all they need to do is start it using the Power Automate Mobile App

Then we need to Get rows, basically, go to each row from the excel file.

These are the rows. You can add as many as you need, but for this article, I kept everything simple and added only the required fields for Account, Contact, and Opportunity entity in our CRM.

TIP: When building complex flows, always keep it simple and break the flow into small parts. Once the first part is working, add the next one, and so on until you finish the entire flow. That way is easy for troubleshooting potential problems with logic.

The next stage of the process is to check in CRM for existing Account and Contact in CRM using the email address from the excel file.

But we also need to make sure that the flow confirms if they exist or not, so we will add an extra step to get this confirmation.

So we add a step List records, and right after that Compose step.

For the List record step, we select the Environment, Entity Name (the one we will be searching for), and the filter criteria. For us, this is the email address from the excel file, and we want to search for it in the emailaddress1 filed in CRM.

For the compose step we need to insert the following formula:

if(greater(length(body('Get_Account')?['value']),1), true, false)

So basically we are asking the flow to return true of Account exist, and false if it doesn't.

I'm going the same but this time for the Contact entity as I also wanna check for existing Contact.

If you remember my advice, you should stop here and test the flow, before going deeper into the logic. So let's test it and see the Compose step Output results.

As we can see for the first row the flow returns details about the account found in CRM. And the Compose step Output returns a true value.

Now for the non-existing one, as we can see there is not Output details, meaning that the COmpose will return a false value.

Before we move forward with the flow, I have to mention something VERY IMPORTANT:

If you are referring to a lookup field in your excel file, such as Account, Contact. Currency, etc. you need to have a get step for each of the lookup entities, as we did for the Account.

Here you can see that I have quite a lot of lookups to check using the filter query and the value from the excel file.

So far so good. It's time to tell the process now what to do with the true and with the false values.

This is easy, we are using a Condition step with two filters (as I'm checking for existing account and contact)

Condition one is the Output for Compose for Account to be true or for condition two the Output for Compose of Account to be also true.

You can group them together and say that both have to be true, but I'm using OR in this scenario.

Now if either one of the above conditions is true, I want to create a new Opportunity.

We select the environment and entity name first and then start adding the rest of the required information.

The string values we can get directly from the excel file as they are not referring to another entity. That values are Estimated Close Date, Estimated Revenue, Topic, etc.

However, for the lookup one, we've already Get them using the List records step, and filter them from what we have in the excel file.

So the only thing left here is to add them to the opportunity.

When using Lookup values make sure AWAYS you are using the unique identifier of the record in your system or (GUID).

I know most people will rush to put there the Name or email address, but trust me this is not a unique identifier for the system, it is the GUID.

So for Account, I put Account filed from the Get Account step, for Contact, Contact option from Get Contact step, and ...... you got the idea.

One more important thing to REMEMBER, in case you are adding a specific owner, apart of the fact that you need to have a List Records step to Users entity, when adding the output to the Opportunity, you need to select an owner type, as the owner can be either user or Team. If you have a team the List record will be to the team, for User will be Users entity.

Now for the false values from our Condition step, we need to first create Account and Contact, and then Create a new Opportunity. Everything here is the same as what we did for the Opportunity, but we need to create an Account and Contact first.

First the Account.

Then the Contact.

And last but not least, the Opportunity.

That's pretty much everything. If we test it we can see the following:

For the one that row where the Account and Contact already exist in CRM, the process just moved forward and created a new Opportunity.

For the second row, where neither the Account nor the Contact exists, the process creates a new Account and Contact and then add them to a new Opportunity.

Feel free to contact me if you have any ideas on how we can improve this flow or suggestions for other automatization I can write about.