cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Drrickryp
Super User
Super User

Update on importing relational data into CDS entities.

After much experimentation, I have found that currently the best way to import related data from outside of the CDS is to use the Data Import Wizard from Dynamics 365.  It can be accessed via the gear in the upper right corner in the environment that you wish to import your data into.  Capture1.PNGThen click on the down arrow next to Settings and choose Data Management, then Imports. 

 

 

 

 

 

 

 

 

 

 

 

Capture2.PNGScreenshot_1.pngCapture3.PNG Once you get to this point, check out the following blog post:   importing-data-in-microsoft-dynamics-crm/the-data-import-wizard-in-dynamics-crm/ However, I have found a few nuances that are helpful and I wanted to emphasize them. 

  1. Although the post seems to indicate that you can create an entity from the data, I have not found that this is possible. The target entities that will accept the data must be created in advance.  However, other than the Name field, if you have not created the fields in the entity, you can choose an option, Create New Field.  When you try to import the data, an error message appears that says there is no such field but when you reopen the map, there will now be a choice for the new field and you can choose it. 
  2. If you have more than one new field to add, then the error message will appear for the next new field and so on until all the new fields are created and mapped.  When all the fields are mapped, (either to an existing field or set to Ignore, then the Import wizard will complete and the data will be added. 
  3. In most cases, it will be more efficient to have custom entity fields already created before attempting to import the data. If the names of the fields match the columns in the source data and the source data is in the correct data type, then they will automatically map. 
  4. If Name field of the Parent entity is not unique, or if the foreign key in the Child table points to a field that is not the Name field of the Parent entity, then this relationship must be specified before the import is done.  
  5. By default, Lookup fields will point to the Name field in the Parent entity and this must be changed so that the Foreign key points to the Primary key of the Parent entity instead.  For this reason, it is important to keep the Primary key in the Parent table before the Import.  See https://arcticit.com/2020/06/getting-the-most-out-of-the-dynamics-365-data-import-wizard/  for details regarding how to do this. 
  6. The first article describes using Import Data Templates but I have not found these to be helpful as most of my data uses Custom entities. Apparently they are helpful when using the default entities such as Accounts and Contacts.
  7. Once all of the entities and the relationships between them have been defined using Lookup fields, it is possible to fill them all at one time by combining the source tables in a single .zip file and using that as the source file for the import.
  8. .CSV files seem to be the most efficient way to structure the source files as they are the smallest in size and there is a limit of 8mb for unzipped files and 32mb for zipped files. 
  9. For the best results, the data column headings in the source data files should exactly match the entity fields including capitalization and the data types should match as well.  Avoid using spaces and special characters in the source column headers and target entity fields.
  10. If the data in a source table exceeds the size limits, they can be split up and the rest of the data can be imported later. 
  11. Once the Import process is started, the source files will be individually mapped to the target entities. It is a good idea to name and save each map because if the import has to be done is two passes (because of large data sources), the map can be reused.  The same is true if more data will be added at a later date after the initial import. The lookup fields should be mapped between Parent and Child tables as described in the second link in #4 above.   Also if some of the data is flawed and can't be imported, the data that wasn't imported can be re-worked and imported again using the map. 
0 REPLIES 0

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (4,005)