Skip to content

Import data into D365FO

In this article, we will show you how you can easily import data into Microsoft Dynamics 365 for Finance and Operations (D365FO) using the Data Management Workspace and the Data Import Export Framework, known as DIXF.

In this article

  • Data Management Workspace
  • Data entities
  • Import data
  • Mapping fields
  • Import settings
  • Sequencing
  • Handling errors
  • Improve import speeds
  • Import data again

Data Management Workspace

The Data Management Workspace is where you will find everything to do with importing and exporting data via DIXF.

Go to Workspaces Data Management

To access the Data Management Workspace, you will need to be assigned one of the following security roles:

  • System Administrator
  • Data Management Administrator
  • Data Management Migration User
  • Data Management Operations User

On the left, you will see tiles where you can create data projects and configure settings. In the middle, you will find all your data projects and on the right you will be able to view the history of your data jobs.

D365FO Data Management Workspace

Data entities in D365FO

Think of a data entity like a layer that sits on top of multiple tables in a database. An entity collects data from all of these tables and presents them to you in a single view. For example, the data relating to a customer is spread across many tables in the D365FO database. The customer entity sits on top of all of these tables giving you the ability to easily import and export customer data in just a few clicks.

Pro Tip: Data entities not tables

In D365FO, you can only import and export data using data entities. You cannot import and export data directly to tables like you can in previous versions of the software (AX2009 and AX2012).

You can find a list of all the available data entities by going to Workspaces Data Management > Data Entities

Data entity tile in the Data Management Workspace

Here you will find a list of all the entities you can use in the Data Management Workspace

Data entity list in D365FO

If you cannot see any entities here, then you need to refresh the entity list. To do this, go to Workspaces Data Management > Framework Parameters

Framework parameters tile in the Data Management Workspace

Select the Entity Settings tab and click Refresh Entity List.

Refresh entity list in D365FO

Import data into D365FO

1. Pick your company

Firstly, make sure you are in the right company (or “legal entity”). The Data Management Workspace is company-specific, so you can only import and export data from the legal entity you are in.

Pick legal entity in D365FO

2. Create a import data project

Next, it’s time to create a import data project. A data project will contain all the entities you want to export. Go to Workspaces Data Management. Then click on the Import tile.

Create a export data project in D365FO

Make sure you are in the Enhanced View.

Enhanced View in DIXF

Give your import data project a name and description (optional).

Import data project in DIXF

3. Upload a file

A data project can be used to import multiple data entities at the same time.

To upload a file, click Add File. Select the entity you want to import and then choose what format the file is in (e.g. Excel). Make sure the Skip Staging flag is set to No. Then upload your file.

Uploading Excels files with multiple Sheets

If you have an Excel file with multiple Sheets, then D365FO will ask you which Sheet you would like to upload.

Add file to import data project in D365FO

The entities you have added will appear in the Selected Entities FastTab.

4. Mapping

The alert icon in the View Map column tells you that some of the fields in your uploaded file are not properly mapped to the staging entity fields.

What is a staging entity?

When you import data into D365FO via DIXF, it first goes through a staging entity and then into the respective target entity. The staging entity captures any errors, allowing us to resolve them, before we push them to the “target” (i.e. the D365FO database). This helps keep the database clean and tidy.

To map the fields, click the alert icon in the View Map column.

D365FO provides two screens to map your fields to the staging entity. Using these two screens you can map the column headers of your uploaded file to the staging entity fields.

The first screen is the Mapping Visualisation tab. Here you can map the fields by dragging and dropping lines.

Mapping in DIXF

The second screen is the Mapping Details tab. Here you can map your uploaded file to the staging entity by using the drop down fields.

Mapping details in DIXF

Once all your fields are correctly mapped, click Save.

If all the fields in your uploaded file are mapped, then the alert icon will be replaced with a black file icon, in the View Map column on the Select Entities FastTab.

Successful mapping in DIXF

Pro Tip: Automatically map files

If the headers in your file have exactly the same name as the staging fields, then D365FO will automatically do the mapping for you.

5. Sequencing

Next, let’s talk about sequencing. If you have multiple entities in a single import data project, you will to sequence them. In other words, you will need to decide what order the entities are imported.

For example, unless a customer exists, you cannot import a customer’s contacts. Therefore, if you had an import data project that included both customers and contacts, you would have to import customers first and then contacts.

What is sequencing?

If you have multiple entities in a single import data project, you will need to decide what sequence (or order) the entities are imported.

Entities are imported in sequence. The order is controlled by the values in the Execution unit, then the Level in execution unit, and finally the Sequence.

If entities have the same Execution unitLevel in execution unit, and Sequence then they will be imported at the same time.

Sequencing in DIXF

In the image above, the first entities to be imported have a Level of 30 and a Sequence of 20. Once these are imported, D365FO will import the next entity in the sequencing (the entity which has a Level of 30 and a Sequence of 22). The last entity to be exported will be the Customer details V2 entity which has a Level of 500 and a Sequence of 10.

Auto-sequencing

D365FO can sequence entities for you, this is known as auto-sequencing. To auto-sequence your import data project, click Standard View.

Standard view in DIXF

Then click Entity Sequence.

Auto-sequencing in D365FO

In the bottom right hand corner of the screen, click Auto-Sequence.

Auto-sequencing in D365FO

Once D365FO has worked its magic, click OK and return to the Enhanced View.

Enhanced view in Data Management Workspace

Your entities should now be sequenced correctly and ready to be imported.

6. Import settings

On the Selected Entities FastTab, scroll to the right and you will see three columns:

  1. Fail Batch on error for execution unit
  2. Fail batch on error for level
  3. Skip staging
Import settings in D365FO

Fail batch on error for execution unit stops the import of the other entities if there is an error in the Execution Unit.

Fail batch on error for level stops the import of the other entities if there is an error in the Execution Level.

Skip staging imports the data directly into the target entity, missing out the staging entity. We do not recommend this, it’s not best practise.

If you cannot see these three columns, then right click on the Selected Entities grid and click Personalize: EntitiesGrid.

Personalise in D365FO

Next, click Add a Field.

Add field in D365FO

Select the fields and then press Insert.

Insert field in D365FO

7. Import now

To import the entities, go to Import Options in the ribbon and click Import Now.

Import now in DIXF

8. Handling errors in staging

Once the import is complete, D365FO will redirect you to the Execution Summary page. This page shows you the status and stats of your import, including: how many records have been created, updated, failed to import, and how many are held in staging.

Handling import errors in D365FO

To view the errors, click View Staging Data.

To see all the errors, type the word Error in the filter box. Click on the row and you will see the error message for that record at the bottom of the screen.

Error in staging

Click Edit and then amend the data in the staging table.

Editing data directly in staging

You can either amend the data in the staging table. Or, instead, you can add reference data manually in D365FO before you re-import the records that have a status of error. For example, in the image above, the Customer Group 400 does not exist in D365FO – that’s why we got an error. So instead of changing the customer group of this record, I could go to Modules Accounts Receivable > Setup Customer Groups and add 400 as a customer group.

Once you have resolved all the errors, click Copy Data to Target.

Copy data to target in DIXF staging

Next, select Criteria from the Run For drop-down field and set the Rows with Previous Errors flag to Yes. Then click OK.

Then click Run.

Run copy data to target

If you have resolved all the errors, then the status of every record in Staging should be Completed.

Staging to target entity completed

Click Refresh on the Execution Summary screen, and you will see that the entity has a status of Succeeded.

Execution Summary page in D365FO

Do this for all the entities in your project until every status is Succeeded.

Execution Summary page in D365FO (succeeded)

You have now successfully imported your data. Well done!

Improving import speeds

It can sometimes be a slow process to import large amounts of data. However, there is a way to speed things up.

When you click Import Now you run the import sequentially. By clicking Import in Batch, you break the import down into a series of batch jobs that can run parallel to one another – meaning your data gets imported faster.

Import in batch DIXF

To set this up, go to Workspaces Data Management Framework Parameters.

Framework parameters tile in Data Management Workspace

Click on the Entity Settings tab and then click Configure Entity Execution Parameters.

Entity execution parameters in Data Management Workspace

Search for a entity, type a number in the Import Threshold Record Count column and the Import Task Count.

D365FO Entity Import Execution Parameters

In the image above, the Import Threshold Record Count is 200 and the Import Task Count is 100 for the Products V2 entity.

In this example, if you were to click Import in Batch as opposed to Import Now, you were importing records using the Products V2 entity and your uploaded file contained more than 200 records; D365FO would create 100 import batch jobs and run them in parallel – massively improving the speed at which the records are imported.

When you use Import in Batch you can track the status of the import batch jobs by going to Common Inquiries Batch Jobs My Batch Jobs.

Import data again

You can easily import data again by rerunning your data project, go to Workspaces Data Management. Select your data project and then click Load Project.

Import data again using DIXF

Then go to Import Options in the ribbon and click Import Now or Import in Batch.

Import now or import in batch
Published inData Management