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

hey guys I'm Melana from Power Platform geek in today's video I want to answer a question I receive a lot from my customer out to easily import or update data into Dynamics 365 using Excel so today we'll look at five different options that you have to do the import or update the first one will be to use the data import wizard the second to use the make that perhaps calm interface then we'll look at how to update data using the export import method using Excel online are using the Excel add-in if you have any questions or any suggestions don't hesitate to post them under my blog post or YouTube video you can also reach me on my different social media and if you like my videos identity to subscribe to my channel for more so let's get started ok so let's start with the first option you have to import data from scratch in the system ok so I'll show you two different options for creating the excel template that you'll use to do d important so the first one will be to use an active view like this one okay so I want to import my contacts so I can use this specific view to create my templates ok the template will exactly add the same columns as in this current view or I can create my own view by clicking on create views and then add my my specific column so the advantage of it is that you'll be able to choose which cone which fills you want for your import template ok so for the example just use this one I can click on export to excel or static worksheet will do the exact same thing which is to export the current view so right now we will export all my active contacts with the exact same columns as you can see on the screen ok so it generated me an excel file my active contacts and from there I'll be able to just delete the row ok you need to enable editing and then you can just delete all the rows you don't mean ok if you don't delete all the rules what will happen if you do modification in this system it will then modify the existing record in the system so you don't want that so just delete them and now you have an empty templates from where you can and put your data so first name let's say Chan and that's it so from there I can save my excel file okay so contacts and I'll be able to use it for importing this new data so this line okay John Doe to do so we'll use the data import wizard okay but just before I'm going there I just want to show you the second option that you have to create your excel template which is to use the data import template creator okay you have two way to access it either you go there in the settings advanced settings it's the classical way of doing so okay so if you remove more familiar with this interface you can go into settings then data management and then template for data import okay second way okay which is the new way is to navigate to admin that power platform that Microsoft Dhaka which bring you to your different environments then you select the environments you want to import data and then you click the tree little dot and settings or you can click settings there one or the other will bring you to the new admin the admin center ok from there you can navigate to templates and you will have data import templates tree both will bring you to the exact same page which is the creation of your data data import okay so now you select the entity contact then you click on download now the system will create me a new excel file containing all the different columns or fields that I have on Form in the system ok so if the field is not on on one of your form or if it's not mandatory ok so it's one or the other one or the other actually it's both the field will not appear in the different columns there ok so it's showing me right now all the fields that I have on one on my forms are which is not in my form but is mandatory ok so from there I can start inputting my data and each of those cells will have the correct format to be able to import it in the system just click on the enable editing now you can see ok so this one is a text field this one is an option set it will give me the different options ok so it's really easy to use you can delete the rows you don't need so if I just want to keep first name last name and company name I can delete all the other columns okay it's have no impact on the import so again I can just write say Jane Doe and ok now I can save this file ok so both way will work to import new data in the system ok so a new contact just call it - now you want to import it obviously so to do so you will navigate back in the system and settings admin center and you will go to data management and you should see import here we go import and just change things just go back a second you have the import there where you can click on import data or you can also go and click on data import wizard both will bring you to the exact same page I don't know wireless - option but it's it's how it looks if you want to use the old way you can always whip snap a gate to your Advent settings take the management and click on import ok so it's all bring you to the exact same page okay which is the import page so that's where you'll be able to see a few your import as error or partial failure or if there's this excess ok so let's click on import data ok select your file so there's the to file we created the first one we created by exporting an active view and delete the rows and the second one we've used creating the data import template creator so I'll just have to close both of those files there you go next so this file is uploaded I can just click on next again and the system will tell me if there's on map field in the in the excel file so actually the system recognized which field should be matched to which cell in your in your Excel spreadsheet in some cases there's no match like full name has no match it's makes sense full name is not the real name is it will feel it's a concatenation field which don't really exist in the database so I can just let it as on map ok so I can click Next if you have any other field that aren't mapped this system will tell you tell you so you just can just click on show all show on map so that's the only one I have right now so it's possible that you have a template with different callings for whatever reason and the system don't know how to map them so you'll have to map them manually ok in that case I don't want to map this one I just click on next then next again you can allow our don't allow duplicate in the system so it will be based on your your specific duplication rules that you have in your system okay so normally I don't want to all duplicate so I'll just click on submit and from there it's been submitted for import and I can monitor them directly from this page so my import you can see that new contacts I had been submitted okay and I have a time step now I can use the exact same way to import the second file we created so new contact to that we created using the data that the template creator so click on next again in that case there is no on map field because that's the template that have been created by the system so you don't need to map them you just click on submit and the same thing you'll be able to monitor it from there so let's just refresh as you can see the first one has been completed the one has been processed and one has success okay if you had errors okay or partial failure you can double click on the line it will open this window from where you can monitor what are the failures so if I add failures I will see a detailed message showing me which columns which value and why it failed okay so from there you can always restart your import so exactly the same for my second one so it's been completed and it has success okay you can if you need so if you add a partial partial failures or errors in your your import and you want to start it from scratch again you can just you can click on delete all imported records from the zip file or all imported records to the entity during this important case so it will remove all the different records that has been created during the import okay so that's the easiest way to do if you have ever and your import normally edges delete all of them I correct the errors and then I redo the import depending on how you want to work okay second option you have to import data in the system it's the one I prefer it's by using the new makes that prob calm okay so you just type mink the power of calm which bring you to your customization portal okay and from there you can navigate to the entity for which you want to do an import select the entity so contactor you go and from there you'll have two options okay you can click on get data or get data from Excel if you click on get data you can import data in this entity so in contact from multiple places I just want to show you I don't want to cover that part today there's a lot to say okay but just to show you you can export from already the base from SharePoint lists from access okay so you have a bunch of options to do your import okay wouldn't for today I just want to cover the excel part so just okay so so I'll choose get data from Excel and from there I can upload a local file that I have on my desktop okay but the advantage of using this option is you can import data from blank excel file so just show you if I got my desktop I have lincoln port so now I have this excel file with the first name last name email and phone okay but it's not formatted at all okay so it's not a table it just texts with header which is not formatted okay so if I want to import this file using this medal I'd be able to do so so just click on upload blank import well upload and then it will ask me to map the fields because obviously the system don't automatically know watch fields map to which shells okay so mark mapping warning exists I just click on map fields from there I'll be able to map my different fields so I just select on map field and so those are mapped doors those are unmapped okay so now it's showing me all the fields from the system's okay which are not necessarily in my excel file okay so I can select which is this Phil whatever okay so let's go with phone so business phone I can map it to phone okay so I have from my source file fields I have a column called phone okay like I want to map with this one then I want to map the email okay so from there I can see that first name last name and phone has been mapped this one is on map so I can just select it and now all my different fields are now mapped in in my in my import so I can just click on save change and then I can click on import at the top right I was looking for it sorry go you click on end part and the import will be done at the exact same way as with the data import difference there is it will not actually actually will all load duplicate so we don't look for my duplication rules okay so we'll import whatever data I put in this in the excel will create duplicate if there's some ok so that's probably the only disadvantage of using this option I prefer it because you don't have to format the file or you do not have to to export it from the system but it's more an advancement porting data in the system now let's look out to update data in the system using Excel but you can also use those three methods I will show you to create new records in the system so the first one is it's to export and we import then it's to use Excel online and finally I'll show you how to use the Excel add-in ok so let's start with export import let's go back in my contacts you there and this one's pretty easy so you just want to click on static worksheet so you'll export your data again then oops you open the file enable editing and from there you can just update your data it's just an example and you have to pay attention to don't remove the edit column and I'll show you why if I just unidentified at you have in the system okay so that's each of those Ricker's have specific width and if you delete those columns the system will not know if to to which - which records import the data and update the data so it always have to stay there so one of those cons are to refer to the existing record in the system and the other one is to know when was the last change in the system because you don't want to overwrite a data in the system so let's say I'm modifying information and this in this file and at the same time one of my colleague is doing modification in this system the system will know because of those columns when was the last time it was updated okay so based on that it will not let the system update over what your colleague I've done in the meantime okay so let's just rewrite them and I've made my modification now I can see my father can't act modified and from there I can just go back in the system and click to import from Excel okay so there's just a little tricky things there if you click on the down arrow and click on import from CSV you'll not be able to reinforce your data because actually that's an excel file we've just created if you have a CSV that's good you can save your excel file as a CSV and then we imported but just pay attention to click on the button so the button itself there is import from Excel and this one is import from CSV okay so if you want to import an excel file click on this one not on the down arrow then you choose your file you go to active contact modified open next okay you can review the mapping if needed again I only have the full name which is not a real field which is on map so I'll let it that way and I click on finish import okay so in telling me that there's a field at our on map so that's okay I know I confirm and I'm done if you want to monitor the imports going you can navigate to the import to the import window well we're actually there and you'll be able to monitor it so if i refresh it you can see that my active contact modify has been processed now it's parsing and at some point it will import them ok so again if there's a success or failure or errors I'll be able to monitor it directly from there just see so now it's transforming so I'll let it roll and I'll show you another option in the meantime ok so that's the first option you have to update data in the system or to actually create some I could have added some rows to this excel files and the system will have created this new record in the system ok so you can use it for in both ways okay just let's refresh ok we'll let it go and come back later so if I navigate back to my active contact view now the second option you have to update your data or again to create new records is to click there on the down arrow next to export to excel and to select open and Excel online the dis method will let you edit the data directly from there so from this window and then to save it back into the system so as you can see actually there was import I've completed while I was speaking because now a B and a didn't have tests that @adam corporation case of the the import at work now if I want to I don't know let's call them back now it's called Samuel biology so if I want to rename it which don't really make sense but it's just for example I can do it right directly from there if I want to add new rows I can scroll down and I my new rows so let's say John due to weaker there already have one so John still have example.com okay so I can add row' directly from there I can also copy paste from another excel files if needs to to create new records in the system when I'm done I just click on save and that's it the the file will be submitted as the next normal excel file in the import wizard so again if I want to monitor it I can just go back there I click on OK I go on my hand port and just refresh oops and as you can see the Excel online my active contact with the timestamps has been submitted okay you can see that the one I've just done before has been completed so that's why you see that email address has been updated to test ok so let's go back there and click on where's the refresh there it is so test and test and one of I think this one will be renamed when the import will be complete ok so now the last option you have to update data in the system or to import data will be to use the Excel add-in okay so the Excel add-in will let you export Excel spreadsheet which is directly connected to the CRM so you'll be able to do the modification or to create new records directly in the Excel spreadsheet and then save it back into the CRM to do so you'll navigate to make that part calm again then you will navigate to the entity for which you want to do the import so data entities and by the way always make sure you're in the correct environment at the top right there okay so if you have multiple environment like myself make sure you're the right one okay because you can if you want to test it and you want to important the dev environment make sure you're in you're in it and you're not connected to production so you select your entity nike's contact click on edit data in excel will generate you a new excel spreadsheet with the direct link to your serum click on enable editing and now you will have this add-in which will load and let you connect directly to the system ok so from there it show me all the information that I have in this entity so I can see all my contact ok active and active whatever it is it's not the same as when we were using a view there's no filter on this spreadsheet for each of them you'll be able so for instance there I have an option set so it will show me the different option I have okay so for each of those here I have active and active there it's a lookup value so I'll be able to see the different lookup value in the system so it's really easy to use if you want to refer to look up and if you want to modify data ok usually if you want to do it in bulk depending on how you are your work could be a little a little bit trickier but to do small modification for me it's my preferred method ok so I can use let's say Jane Doe John Doe or Tendo 365 you do my modifications change is constant now it's transactional so it's a good example if you want to do bulk modification for a Content if you're using marketing so I can do any modification I want and when I'm ready to push it back into the system I just click on publish and now it's publishing in the system and that's it ok so that's really easy to use if I go back in the system and I'm looking for let's go back in marketing like to export the data directly from there I don't know why would my link you seems to be broke but whatever let's look at the data there you can see that if I'm looking for John Doe I have my John Doe 365 which has been updated by the way if you didn't know you can explore your data directly from there so you can go in the entity and the data and you'll be able to see the different information okay the only thing is that the different roles are pretty limited so maybe you're better to go in the system directly but for a quick view that's a good minute okay so that complete the different matter that I want to show you so there's five minutes you can use a file option you can use to import or modify data in the system I hope it self full if you have any suggestion of questions don't hesitate to contact me on my social media Commons directly in the video don't forget to subscribe to my channel I will release more of these video and if you have any suggestion that is the take to get in touch so thank you for watching

Channel: Samuel Boulanger

Views: 28,808

Keywords: Dynamics 365, Dynamics Marketing, Marketing, Segmentation, Segment, Customer Journey, CRM, Dynamics CRM, Power Platform, Power Apps, Microsoft, How to, Tutorial, Training, D365, powerbi, analysis, Excel, iMPORT, Data

Id: ekFDJ00p1vQ

Length: 26min 35sec (1595 seconds)

Published: Wed Jun 03 2020