5 ways to import and update data in Dynamics 365 using Excel

In this video we look at 5 ways to import and update data in Dynamics 365 using Excel.
We'll see how to use:
* Data Import Wizard
* Make.powerapps.com
* Export/Import
* Excel Online
* Excel Add/In
For more info : www.powerplatformgeek.com/2020...

[@camillep9446]

Samuel, merci beaucoup! 3 years later and your tutorial is still very much in use. I have spent maybe 3 hours trying to successfully update records in the CRM and have not been able to do so. I watched your video again and following your instructions, it worked!

[@samuelboulangerAI]

Merci pour ton commentaire :) It's really cool to see that what I did 3 years ago is still in usefull.

[@isabelbarnard9427]

Thank you! Spent an hour searching the internet for help and this was literally everything I needed and more :) Keep up the great work!

[@samuelboulangerAI]

I'm glad it was helpful. If you have any ideas for new videos that can help don't hesitate to share :)

[@invictuz4803]

Great breakdown of all the options, very thorough walkthrough!

[@Mh-zj7og]

Really helpful video, thankyou!

[@_JuliaLauren]

This was extremely helpful, thank you!

[@samuelboulangerAI]

Happy it helps you :)

[@HemalTalaviya]

Hey Samuel, your video rocks! I've recently started using Dynamics 365, and I'm loving it. But here's the thing: I want to connect an Exported Excel file to our sales data in D365. So, whenever a new project is added to our sales opportunities, it automatically updates in this Exported Excel file I've got saved in a specific spot. That way, I can analyze everything for different products at the end of each month without needing to export a new Excel sheet every single time. Any advice on how to do this seamlessly?

[@samuelboulangerAI]

Hi there, I'm glad you love the content :) Actually, you could export in a dynamics pivot table and then update directly from excel.

[@userunknozn]

Samuel thanks a lot! This video is a life saver. I'm a very basic user of the Microsoft Dynamics CRM and updating a database of 2000 contacts is very time consuming if you do it one by one through the contact form. Could you please release a video on how to manage campaigns (and campaigns' responses) the right way? I would also love a tutorial on how to create associated fields in forms such as "interested in subject A" or "interested in subject B". Thanks a lot!

[@samuelboulangerAI]

Hi Kitten, thanks for the suggestions, I'll add it to my list :) I'm happy to hear that it helps you.

[@A1F9S8B6]

Hi Samuel. Very, very helpful video! I am interested in the Import/Export Excel method you talked about. Is this only limited to System Admins, or can this be delegated to other roles with less privileges? Could you briefly show how it can be done?

[@samuelboulangerAI]

Hi, basically any role can have the right to Import/Export. The user will need to have read/write privilege to the data he wants to import . You also need to set up the data import and data map privilege in the security role (Core records tab). Hope this help.

[@snowballtingting]

Thank you for the breakdown I wonder what if we have more than 2 contacts to share the same company and company has more than two locations will be like, when importing. Or contacts have more than one accounts of company.

[@samuelboulangerAI]

OOB you can only have one company per contact. For the locations I suppose you can use parent accounts but again you'll have to choose to which of those locations you want to assign the contact. You can obviously create a custom relationship but N:N won't be supported for import. Hope this helps.

[@bencunningham8871]

Brilliant video thanks!

[@samuelboulangerAI]

Glad you liked it!

[@newkidintown5477]

Great video ❤️ .... I have a question though..is there any way of fetching the data automatically using power automate. As I have to download to excel everyday....any advice would be appreciated ..thanks

[@samuelboulangerAI]

You can use the "Dynamics Worksheet "export option and you will be able to refresh the data directly from the Excel workbook. I hope it answers your question.

[@userunknozn]

I have been testing the PowerApps in Excel method you showed at the end of the video to update contacts but I have noticed that the Company field/column is missing. That field is visible on the contact form in Dynamics CRM. Why isn't it showing in the Excel file?

[@samuelboulangerAI]

Hi Kitten, actually the field is called Account (AccountID). Sometimes the label of the fields on the form and the field name might me differents. Hope this help.

[@Pentadrian91]

when moving data across environments, how do you keep the "Owner" (ownerid) fields the same? usually find that the importing user is set as the owner of the records

[@samuelboulangerAI]

You're correct, if the owner field in the import spreadsheet is empty it will default to the user you selected in the second stage of the import (Select owner for imported records)

[@user-sw1ny6on6h]

Hi, I tried the export stated, edit, and re-import, but I keep getting the "a record was not created or updated because a duplicate..." error. How do I fix this? Other reports say unchecking duplication detection result in duplicate records being created.

[@RicardoFilho-fe5fs]

Is it possible to automate this process? every time a new entry is made on the CRM Dynamics 365 an excel online instance is updated?

[@accountingspb3075]

Just starting D365 this week. We are trying to figure out how to import data that has a discount that needs to be deferred. We have deferral codes set up and are calling them in the import, but in order to get them into the correct posting period, we need access to the Start Date as part of the import. We are using Table ID 81 in our import and the Start Date is not one of the fields that can be called and set. How would I get around this? Thank you for any guidance or direction you can offer.

[@samuelboulangerAI]

Hi, it seems that you're referring to D365 Finance & Operations for which I am unfortunately not familiar with.

[@accountingspb3075]

@@samuelboulangerAI No, we're actually useing D365 Business Central. It's a very interesting program and has a lot of features that we are still trying to understand.

[@sayedmostafaalem6998]

I have a question about the excel add-in. Is it possible to add a new row to the table via Macro? Or is it only possible through clicking on the "+ New". I want to import the new rows from a user-form and publish them at the end. Thank you.

[@samuelboulangerAI]

I honestly never tried, but I would assume it should work. If you try please let me know :)

[@salvadorprimo3340]

Hi. For instance, if I Import Contacd data, if I fill out the "Company name", the contact is not imported if the Compay still does not exist in the Accounts. Similar situation occurs when I import Accounts (Companys) with a Principal contact name filled out with a Contact name that still does not exist in the Contacts table. Please, Are there any way to solve this kind of relationated tables imports?

[@samuelboulangerAI]

Hi Salvador, the import let you create new records but also update existing records. In your case I will suggest to proceed like this: 1. Import contact data without filling the company name 2. Import accounts 3. Export the contacts from a view including the company name field 4. Update the company name in the exported excel file. Do not modify the layout of the excel, the reference to the contacts are stored in an hidden column 5. Re-import the contacts. You can proceed the same way for account. I hope it helps. I will try to do a quick video this week showing how to proceed.

[@salvadorprimo3340]

@@samuelboulangerAI Hi, Thanks a lot!, Good idea, I appreciate. Best regards

[@ajayv2632]

Hi, can we update data in Crm fields using Power Automate, when Varing column Excel having data is uploaded into sharepoint folder , suppose there are 10 fields in Dynamics entity. The Excel can have column only 1 or all 10 or in between 1-10. When Excel is uploaded it should trigger the flow and fetch data from Excel and update those columns in crm which are present is Excel and other columns have to be left as it is with out update. There is no fixed template for Excel, the number of columns and order of columns in Excel can vary. So how to map the columns from Excel to crm as on each update we will have different Excel template.

[@samuelboulangerAI]

Hi will suppose there's a way of making this work but I honestly didn't try it myself, so I can't confirm 100%. I'd like to help more but it will require a lot of testing.

[@ajayv2632]

@@samuelboulangerAI i have done it already will share method.

[@jeffreylumanas9502]

Hi, I found this so helpfull. If you mind, how do you import multiple SKUs in Dynamic 365 from QB?

[@samuelboulangerAI]

Hi Jeffrey, I must say that I don't really know the structure of SKU's in QB. If you want, you can contact me on LinkedIn (www.linkedin.com/in/samuelboulanger/) with more info and I'll try to help you.

[@isabelahurduzea6733]

How can I import multiple contacts for one company?

[@samuelboulangerAI]

Hi Isabela, you add the company in the "Company name" column. You could add multiple lines for the same company, one for each contact. Hope this help!

[@jeromeleonardalmario5371]

Can this be implemented to manipulate data from excel , save the file and automatically update Dynamics data?

[@jeromeleonardalmario5371]

what I meant was Data Migration from excel to auto populate data in dynamics

[@samuelboulangerAI]

@@jeromeleonardalmario5371 Technically yes, but I'll need to have more details on what you want to achieve. You can definitively export data in excel, manipulate it and then re-import it.

[@Mychannel90488]

Pls make more videos on crm entity field mapping

[@samuelboulangerAI]

Hey Sonali, sure I'll be happy to. Can you please give me more details on your specific use cases so I can create a tutorial? You can share here or reach out on Linked In www.linkedin.com/in/samuelboulanger/

[@kjomalley13]

You NEED to clear the values in the first three rows of the template after you export a view. These contain the GUID to the record. You just overwrote the record when you imported John Doe!!

[@samuelboulangerAI]

Hi Kyle, thank you for your comment. You're right if you want to create new records you need to clear the three first column. However, if you want to update existing records you should not clear them.

[@kjomalley13]

@@samuelboulangerAI Love the videos. I only bring it up because I have overwritten client data by forgetting to clear the first three columns. Keep the content coming SO helpful!

[@samuelboulangerAI]

@@kjomalley13 Thank you for the feedback I'll try to be more precise in the next video. If you have any suggestions of tutorials that can be helpful, don't hesitate to share. I'll be more than happy to help.

[@BiaxialBigfoot]

When I import an excel file to update information existing in dynamics 365 those rows error out as duplicates. So their account numnber for example is the same but the email is changing but it still errors and doesnt update email. What should I change to fix that?

[@samuelboulangerAI]

Hey Lucas, did you ?: 1.Export the file from D365 2. Modify the data without removing the hidden column and don't change the worksheet structure 3. Reimport it It looks like the system is trying to recreate the records, that's why you'll get this error message.

[@BiaxialBigfoot]

@@samuelboulangerAI So this file is a file I receive from a client that has existing members and new memebrs info on it. The existing records will sometimes have an address change or something. I'm not exporting because not all of the information is in CRM yet. The import includes both completely new accounts and changes to existing accounts.

[@samuelboulangerAI]

@@BiaxialBigfoot You will need to create two files. One for the creation and one for the update. 1. Export the contacts from D365, then validate which ones already exist using formulas. 2. Update the contacts that exist in both spreadsheet and reimport the file. The unique identifier of the contacts is in an hiden column in the file you exported from D365. 3. Create a second excel file containing the contacts who doesn't have a correspondance in the previous step. This will create new contacts. Hope this help.

[@toddgardner1623]

I'm struggling to update existing records. I've tried numerous ways and the system continuously tries to create new records and gives me an error. There is no way to flat-out choose "update" despite including those 3 initial columns. I've followed your steps exactly - what could I be doing wrong?

[@toddgardner1623]

One difference I notice is when I go to upload my changes, the system asks me to specify a record owner but in your video it does not ask for this

[@toddgardner1623]

nevermind...it appears to be working despite the system telling me I have 100% errors due to existing records. It will still update those existing records despite showing these errors.

[@toddgardner1623]

there are duplicate records that did not get updated despite having the GUID

[@samuelboulangerAI]

Have you found why it wasn't working? Normally, if the GUID is there it should works

[@toddgardner1623]

@@samuelboulangerAI yes thank you. I've found the trick is if you are important records with the GUID, you should go to that object and click Import from Excel. For some reason the same file does not work if you go to settings -> import data and choose the object from there.