How To Import and Export Data in Microsoft Dynamics 365

Mastering data is a necessary feature in Microsoft Dynamics 365. This guide will dive into Excel Imports and Exports for Customer Engagement Apps and Model-Driven Apps. This is especially useful if you plan to shift data from systems that support Excel exports or if you aim to input records from Excel files on your computer. Understanding data formatting and mapping is crucial, allowing for smooth information transfers. Effective data management ensures accurate insights, smooth operations, and enhanced customer interactions, making it vital for maximizing the potential of Microsoft Dynamics 365. Dive in with us to sharpen your data management prowess and elevate your business operations.

Via Advanced Find:

1.    Open Advanced Settings.

2.    Utilize the Advanced Find feature to search for the view to download specific records based on your criteria.

3.    Once the desired records appear, click on "Export to Excel" to generate an Excel file with the search results.

Via the App’s Interface on the Views Screen:

1.    In Dynamics 365, either use existing views or create new views to display the data you want. You can see how to create views in another tutorial with video “How to Create a Personal view in Dynamics 365”.

 
2.    Alternatively, simply open an existing view. To do this, click on the table you wish to export, which can be found in the left vertical menu within any Microsoft Dynamics App.

3.    In the view, click on the ellipsis (...) and select "Export to Excel" to export the visible records to an Excel file.
 

Prepare Your Data in Excel:

1.    Open Excel and create a spreadsheet with your data. Confirm that your data is in a format supported by Dynamics 365 (common formats include Excel, CSV, and XML).

 
2.    Ensure the data is accurate and adheres to the required format. For instance, you should not have more than one row for the header, avoid merged columns, and ensure all essential data is added to the first list.

 
3.    If you have downloaded a template or exported data from MSDyn 365 – then you don’t need to worry about the previous two items.

 
Open Imports:

1.    Navigate to Advanced settings.
2.    Proceed to Settings drop-down > Data Management.

 
3.    Choose Import Data.

 
Download a Data Template (Optional):

1.    Click on “Excel Templates” followed by “Create an Excel Template.”

 
2.    Select the table into which you wish to import data. Additionally, select a view to manage the column set. Prepare the corresponding view so that this column set closely matches the Excel file you plan to import.

 
3.    Click on "Download Template." This action will download an Excel file with pre-defined columns that align with the fields in the chosen entity.

 
4.    Optionally you can press “Excel Templates” and upload recently downloaded template to save it and reuse in future.

Fill in the Excel Template (Optional):

Now, if you haven’t downloaded data from the Dynamics 365 to update it and you want to procced the file from another system, or which was sent by your colleague or client. You should adapt it to standard system template at first.

1.    Open the downloaded Excel template.

2.    Copy your data from the original Excel file and paste it into the corresponding columns of the template. Ensure that data types and formats are consistent with the template.

 
3.    Important: Do not delete or rename the first row or the initial three columns in the Excel Template; they are essential.
4.    Save the Excel template containing your data. You will upload this file during the import process.

 
If you aren't working with an Excel Template, ensure your file has proper formatting:

•    Ensure your data conforms to the format and structure of the target entity in Dynamics 365. 

Upload and Map the Excel Template:

Important note. Firstly you should import records from tables, that are used in lookups. That is logical that firstly we import the entries that we will need to refer to in the next iterations. If you are importing just one table, ignore this note.

1.    Return to the Import Data wizard in Dynamics 365.

 
2.    Click “Import Data” and then "Choose File" and select your previously populated Excel template file. The mapping process will be automatic.

 
3.    If you haven't created any Excel Template, or aren’t using exported file right now, you can select the target table and establish data mapping during this import process.

4.    Review the mapping to ensure that columns align with the corresponding fields in Dynamics 365 accurately.

5.    Validate the data to identify any potential errors or discrepancies.

6.    Decide if you will allow duplicates, select the owner of imported records, and press Submit.

 
Submit Import:

If all details are accurate, initiate the import job.

Monitor Progress:

Keep an eye on the import job's progress within the Data Management area. You can do that in the import view, there is an information that displays the amount of imported records and failures. You can refresh the subgrid to see the progress in real time.

 
 
Review Import Summary:

1.    After the import is completed the status will change. You can open the record and see more details. Also, you can check information about errors.

2.    Congratulations, if there is no errors, the import is completed. If there are, follow instructions in the error details and try once again.

This guide has offered a comprehensive overview of the Excel import and export mechanisms within Dynamics 365, providing invaluable insights for those looking to elevate their data management techniques. Remember, the key lies in meticulous preparation and a clear understanding of data formats and mapping. With these tools at your disposal, you're well on your way to maximizing the benefits of Microsoft Dynamics 365 for your business operations.