How to Easily Import Data into Dynamics 365 Business Central

Learn how to import data into Dynamics 365 Business Central, or modify existing records, using the Configuration Package. This blog discusses the areas in Dynamics 365 Business Central that you can use this import functionality, outlines step-by-step instructions on how to import data, and includes a video with instructions at the end of the article.

Importing Data into Dynamics 365 Business Central

There are many situations you are likely to come across where you have a lot of data that you would like to import into the system, rather than keying it in manually one record at a time.

A benefit with Dynamics 365 Business Central is that it comes with an import tool out of the box. The tool is called a Configuration Package, which allows you to create an import package where, once created, you will be able to import data from Excel into any table in Business Central. Once the configuration package is setup, you will have Excel templates that can be used again and again to import data into Business Central.

The list below highlights some of the popular areas where the import function can be used:

• New GL Accounts
• New Customers
• New Vendors
• New Items
• Sales Header
• Sales Line
• Purchase Header
• Purchase Line
• General Journal Line
• Item Journal Line
• Sales Price
• Purchase Price

The list of tables that can use this function, and the creative ways you can create import files, is numerous.

Modifying Data in Dynamics 365 Business Central

The configuration package can also be used to modify fields in existing records. For example, you can use it to mass block a group of vendors or put numerous invoices on hold. Instead of keying in those changes one at a time, you can import those modifications from Excel into Dynamics 365 Business Central using the Configuration Package.

How to Import Data into Dynamics 365 Business Central

In this example, we will be importing new Customer records. The following steps will be the same for other records, the main difference is what the table number will be.

Click on one of the steps below to jump ahead to a specific section:

• Step 1: Determine the Table Number
• Step 2: Create the Configuration Package
• Step 3: Review the Template’s Included Fields
• Step 4: Export Excel Template and Complete with Data
• Step 5: Import Excel File and Apply the Data
• Step 6: Review Imported Data
• How to Deal with Errors in Configuration Package
• Video Instructions

Step 1: Determine the Table Number

The first thing we need to determine is which table the data is going into. Navigate to your Customer window, then in the top-right corner of Business Central click ‘?’ and then select ‘Help and Support’.

In the Help and Support window, under Troubleshooting, click ‘Inspect pages and data’.

This will open the ‘Page Inspection’ side pane which displays information for the page you are on. The key thing to look for is the Table number. In our example, the Table number is 18, denoted by ‘Customer (18)’.

Once we’ve taken note of the table number, you can close out of the Page Inspection pane.

Step 2: Create the Configuration Package

The next step is to create the Configuration Package. In the search bar, type in ‘config packages’ and select ‘Configuration Packages’.

Once in the Configuration Packages window, click ‘New’. Now we will set up a new Configuration Package Card for customers.

Type a name in the ‘Code’ and ‘Package Name’ area. Then, in the Table ID column, click on the box that shows as “18”.

A table will open where we can search the tables that we want to import. For example, search “customer” in the search bar and you will see the Table ID is 18.

Step 3: Review the Template’s Included Fields

Before exporting the template, we want to see which fields will be included. Once we have the relevant Table ID, make sure the current ID is mentioned in the “Table ID” screen. Once you’ve verified, click on the number shown in the “No. of Fields Available” column.

That will bring up the main window that shows all the included fields for the table. By default, every table will have fields already included, shown by a grey checkmark in the Included Fields column. This means that those fields will be included and added to the exported template.

If you’re only looking to import into certain fields or columns, you can click ‘Clear Included’ at the top of the window and that will clear all the checkmarks. Then in the ‘Included Field’ column, you would manually click on the fields you want to include in your template. You may need to click the Edit List button before the page will accept clicks in the Include column.

Close out of the Fields window when you are satisfied with your ‘Included Field’ selections.

Pro Tip: The “Move Up” and “Move Down” options on the top menu can be used to change the column number sequence. This is useful when we want to enter the information in the exported excel file in a particular format.

Step 4: Export Excel Template and Complete with Data

Next, we want to export the Excel template so we can add our data to it. Back in the Configuration Package Card, click ‘Excel’ and then click ‘Export to Excel’. This will download an Excel file that contains all the fields that were checked off in the previous step and it will include all the records that are currently existing in the system in the table.

Open the downloaded Excel file. The next step is to delete the rows of your existing data in the exported file and then save the Excel file as an import template for the records you are going to import. The next time we want to import customers, the same Excel template can be used and then we will use the Customer Configuration Package.

If the dowloaded file does not contain any information and simply the column names, that means you currently do not have any customers created in your Business Central environment.

Once you’ve saved the Excel template, fill in your customer data that you want imported into Business Central. Once all the data is filled out, save the Excel file, and navigate back to Business Central.

Note: Make sure the information is entered correctly as per Business Central requirements.

Step 5: Import Excel File and Apply the Data

When you’re ready to import the data, go into the Configuration Package Card you created in the earlier steps. Navigate back to the Customers Configuration Package, click ‘Excel’, and then select ‘Import from Excel’.

Once the file is imported, click on “Apply Package” at the top of the window.

Once the package has been applied, the new customer information will automatically be entered within Business Central. This function will also make sure that all the fields from the Excel template get validated to confirm the information entered is correct.

Pro Tip: “Apply Package” option can also be used from the Table Column option. Click on “Functions” then “Apply Data”.

The setup for General Entries is a bit different. Once the Table ID has been entered and No. of fields has been opened and selected, click on “Excel” and “Export from Excel”.

Once the file opens, make sure to enter the first three columns correctly:

Be sure to use all capital letters in columns A and B. The information entered in these two columns will dictate where your General Entries will go. If you specify the wrong batch, the entries can go to the wrong screen. In column C, each line will Increment by 10000.

Pro Tip: Make sure the date format in the “Posting Date” column is in MM/DD/YYYY format.

Step 6: Review Imported Data

Next, we’ll want to review the data in the Customer record itself. Below is an example of what a final import looks like in the Customer Card.

How to Deal with Errors in the Configuration Package

When entering data in your excel template it may show up as an error once imported through the Configuration Package.

To review the errors, click on the red text.

The error text indicates that, in this case, the errors are related to the No. Series.

To rectify this, search “No Series” and navigate to Customer No. Series. Then select the manual No. Series option so that you can specify your own number series for the new customers.

Once this has been completed, you can go back and import the same file again.

Note that to remove the errors once the changes have been made, you can click on each error individually and then click “Apply Data” to update the settings for that error.

Additional Information

In the Configuration Package Card, there are different columns you can select that will alter the data available in the table.

Checking off the “Dimensions as Column” box signals that the dimensions will be added in to the related table ID. For example, if this box is selected, it will add additional fields for dimensions. Scroll to the bottom to view all dimension rows that have been added:

“Skip Table Triggers” indicates that any code unit triggers related to the Configuration Package will be skipped. Code unit triggers such as: Imported by User ID & Created by User ID etc.

Checking off “Delete Table Record” will allow you to delete any information currently added in BC for that particular table ID. For example, if we select this option and then import the customers, any previous information related to the customer will automatically be deleted and the new information gets entered. Note that this is a very risky function and should only be used under guidance or after testing this function in a test environment.

Video Instructions of How to Import Data into Dynamics 365 Business Central

The video shows how to import data into Dynamics 365 Business Central and shows some examples of other ways to use Configuration Packages.

In the video, we go over the following:

• How to use the ‘Inspect pages and data’ function to find which table stores the data you are trying to import – 1:10
• How to create a Configuration Package that will be used to import data from Excel into Business Central – 2:40
• Demo of using the Configuration Package to import customer master records – 5:20
• Examples of how to use Configuration Packages further – 7:25

Please reach out to us if you have any questions about importing data into Dynamics 365 Business Central.