Skip to main content

Excel Mapping Template

The Excel Mapping Template specifies the column layout of the Excel files you will import and the settings that affect how the files get imported. You can create multiple mapping templates for each import type.


If you are new to Excel Importer, we recommend downloading and applying the sample Excel Mapping Templates because they give you examples of templates we frequently use. The easiest way to download these samples is to use the Excel Importer Setup Wizard.

Creating a new Excel Mapping Template

You can create or edit Excel Mapping Templates from the Excel Import Mapping List. The steps for creating the Excel Mapping Template are:

  1. Open the Excel Import Mapping List
  2. Create a new record
  3. Specify the Field Mapping to the Excel columns in the file
  4. Add additional fields to the field mapping that need to get values but are not available in the file
  5. Specify values for the settings

We will create a new Excel Mapping Template to import the following Excel file:

Excel file containing customer opening entries

Open the Excel Import Mapping List

You can open the Excel Import Mapping List from any of the following places:

  • The Import from Excel dialogue, which gets opened when you select Import from Excel.
  • The Excel Import Mapping List - use Tell Me to search for this page.
  • The Assisted Setup also gives you access to the Excel Mapping Templates.

In this example, we will open the Excel Import Mapping List from the Import from Excel dialogue. We use this approach because it allows us to configure the template in the same place where we want to use it.

  1. Open the sales journal and then select the Import from Excel action to open the Import from Excel dialogue.

Sales Journal Select Template 2. Use the lookup of the Template Code to open the Excel Import Mapping List. Alternatively, click Select a template to use for the import to open the Excel Importer Mapping List.

Create a new record

  1. In the Excel Import Mapping List, click New to create a new Excel Mapping Template.

Excel Import Mapping List

  1. The Excel Mapping Template Card is opened with a new record.
    • Select the Template Type Import General Journals. Excel Importer will fill in this value if you have come from the Import from Excel dialogue, as we do in this example.
    • Enter CUSTOMER-OPENING for the Code.
    • Enter Customer opening entries for the Description.
    • For First Data Row, we use the default value (2) because that is the first row in the Excel file we would like to import. You only change this value if your data starts in a different place.

Specify the Field Mapping for the Excel columns

  1. Use the Select Fields action to open the fields list.

Excel Import Mapping List


The fields list shows all available fields to select for the import type. Some fields are not available because they get updated by the system.

For some of the import types, it includes fields from more than one table. For example, the sales document import type contains fields from the Sales Header, Sales Line and Tracking Specification.

  1. Select the fields that you want to map.
    • Select the Edit List action to make the page editable.
    • Select the fields by placing a checkmark in the Select field.
    • Change the Excel Column ID if required.
    • Use Search to find fields quickly.
    • Click OK when you have selected the fields you want. Excel Importer will take you back to the Excel Mapping Template Card, and the selected fields get added to the template.

Select fields

  • You can select the same Excel Column ID for more than one field if you need to. For example, you might want to update the Document No. and the External Document No. with the same value from the file.
  • You can use the Select Fields action as often as you like, even if your template already has field mapping.

Add additional fields

Your Excel file does not always contain all the fields that need to be specified. We also want to set values for Account Type, Description, Bal. Account Type and Bal. Account No. in this example.

  1. For each field you want to add:
    • Add a new line at the bottom of the Field Mapping section.
    • Enter the Processing Order.
    • Enter Field for the Type field.
    • Enter the field value in the Constant Value field.

The Processing Order specifies the sequence in which Excel Importer should update the fields.

Select Additional Fields


The Processing Order is important because Business Central applies business logic when you enter field values that may replace values that get imported. The general rule for the Processing Order is that it should be in the same sequence as you enter the values on the page.

Notice that we entered 5 for the Account Type. We want to specify this field before the customer number from the file gets copied to the Account No. field.

Specify values for settings

In this template, we do not want to import rows that have a zero amount. We clicked on the setting value and selected Yes. Customer Opening Transactions Settings


You can close the Excel Mapping Template Card and return to where you started. In our example, we will return to the Import from Excel dialogue. We can now select the template and import the file. You can see how we import the file here.


Need to create a new template similar to another template? Use the copy template feature to assist you.

Excel Mapping Template Reference

This section details all the fields and features connected to the Excel Mapping Template. The Excel Mapping Template consists of the following components:

  • Header,
  • Field Mapping
  • The Settings, where you specify rules to apply.

Header Fields

Here you specify a name and other details about the template. Header Fields

Template TypeThis compulsory field specifies the import type. This field must contain a value before you can specify any other fields.
CodeThis compulsory field specifies a unique id for the Excel Mapping Template. You can specify alphanumeric characters in this field to make the id meaningful.
DescriptionThis is a description of the template. This field will appear in the Excel Import Mapping List and should be meaningful so users know when to select this template.
First Data RowHere, you can specify the first Excel row from which to import data. The value defaults to 2 because most Excel files will have headings on the first line and data starting on the second line. You can change this value if your data begins on a different row.
Row FilterAllows you to specify when the app should import a row or when the app should skip a row. You can read more about this feature here.

Field Mapping

This is where you specify the columns from the Excel file and map them to fields on the table in Business Central. The Import Type determines which fields are available for selection. Header Fields

Common Fields

These are the fields that are used most frequently.


When the text colour of the mapping lines is red, it indicates that you have not completed the setup correctly.

Processing OrderThe Processing Order specifies the sequence in which Excel Importer should update the fields. The general rule is the Processing Order should be n the same sequence you specify the fields when you capture the data directly in Business Central. Excel Importer increments the Processing Order by 10 to allow you to add new fields between existing fields easily.
Excel Column IDYou specify the column from the file here. If the field is assigned a constant value, then you leave this field empty.
TypeChoose either of the following: Field, Related Field, Dimension or Custom Value. Field or Related Field is used to map the Excel column to a field in a table. Dimension maps the Excel column to a dimension in Business Central. Custom Value is used when you cannot map the Excel column to a Business Central field but need the value for some special processing.
No.Depending on the Type, this contains either a reference to the field, dimension or custom value.
DescriptionProvides a description which is based on the Type and No.
Constant ValueThis field specifies values that you want to assign to fields that you cannot map to an Excel Column but need to have a value. This field sometimes gets used with other fields on the mapping line.
Default ValuesSpecifies how to default values in this field. Choose None (default) when you want to use the value in the Excel cell, Use BC Default if Empty if you're going to use the value Business Central defaults when there is no value in the Excel cell and Use Constant if Empty to use a constant value when there is no value in the file. If you specify Use BC Default if Empty, you must also enter a value for Constant Value.
Validate FieldUse this field to control how Business Central updates the field value. Select Always to execute the business logic that Business Central would typically run. Select Only if Values when you want to run the business logic when a value is in the imported file. Select Only if not default value when you would only like to run the business logic when the value imported differs from the field's default value (e.g. the default value for decimal is zero). Select Never if you only want to fill a specific value without executing the business logic. Note: Dimension Values are always validated irrespective of the specified value.

Selecting Never for Validate Field does not execute the underlying business Logic associated with the field. It would be best to choose this option as a last resort. You usually only specify this value for fields such as document numbers which would otherwise cause an error when importing. We have found that the default value of Always works in most scenarios provided that you specify the processing order correctly.


These fields get used when you need to change the value in the Excel cell before it is applied to the field in Business Central.

Starting PositionThis field defaults to 1. Change this value if you do not want to copy the first part of the text in the Excel cell.
LengthSpecify a value here if you do not want to copy the full value of the Excel cell. For example, if the Excel cell contains more than 100 characters and you want to use the value for a description, you would specify 100. This field works in conjunction with the Starting Position.
Decimal Rounding PrecisionThis field only applies to Decimal values and defaults to 0.01. You can change the rounding that Excel Importer should use on the value in the Excel cell. You might want to specify 0.00001 for quantities or unit amounts.
MultiplierAllows you to multiply a decimal or integer by a specific value. You can use this to change the sign of an amount (-1) or to convert cents to currency (e.g. 0.01). If this value is not specified (zero), the amount will not be multiplied.
Remove Special CharactersThis field removes special characters from the Excel cell. You only need to do this if the formatting in Excel will cause an issue when you import the file. In most cases, you do not need to change this.
Transformation RuleApply a rule to convert the text value. This uses the standard Business Central Transformation Rules, which you can read about here. We have also added a practical example here.

Account/Dimension Mapping

The Account/Dimension Mapping allows you to handle scenarios where the data in Excel has a different coding structure to Business Central. You can read more about the setup here. You only specify values in the fields below if you need to handle mapping.

Mapping Template CodeThe Account/Dimension Mapping Template that contains the mapped text.
Mapping TypeThis field gets used to specify if the mapping line is coming from the Excel file (Parent) or if the mapping line is an additional field that you want to include from the Account/Dimension Mapping (Child).
Mapping FieldThis is the field you want to retrieve from the Account/Dimension Mapping. For example, you might want to retrieve the mapped account number or one of the dimensions from the Account/Dimension Mapping.
Mapping Parent Column IDYou specify a value here when the Mapping Type is child. This creates a link between the Parent line and the current line.

Mapping Excel Columns to Blob Fields

Some fields in Business Central hold text values but are defined as a Blob. An example is the Work Description on sales documents. Excel Importer allows mapping one or more columns to the same Blob field. The example below shows how to map multiple columns from an Excel file to the same blob field.

Blob Field Mapping


There are common settings and settings specific to each import type. The settings fact box is on the Excel Mapping Template Card. In the settings fact box, you can see:

  • The setting name (red box)
  • A detailed explanation of the setting (yellow box).
  • The setting value (blue box)

Settings Fact box

To change a setting, you click on the setting value: Settings Fact box


You will find help for settings specific to each import type in the import type's help article.

Common Settings

Common settings get added to all Excel Mapping Templates.

Validate data before ImportSpecifies that you want to perform validation before importing data to receive a list of the most common issues. The default value is Yes. Note that if you choose No, the data will get validated while importing, and the import will stop on the first error. In other words, this setting cannot disable the validation - it only allows you to enable error collection.
Create missing dimension on ImportSpecifies that you want to create new dimension values if your file contains values that do not exist in Business Central.

Create missing dimension on Import should only be set to Yes on templates where you trust the data quality of the source file to prevent invalid dimensions from getting created.

Adding rules to include or exclude Excel Rows

Business Value

Some of the Excel files you import may originate from reports produced by external systems. Sometimes, these files will include unwanted rows, such as sub-totals. Alternatively, you may receive Excel files containing rows with invalid data you do not want to import. Removing these lines takes time and can lead to errors.

Feature Details

Excel Importer allows you to specify a Row Filter. When you specify a Row Filter, you also specify whether the app will use this filter to include or skip lines. The Row Filter is available on all Import Types.


The app evaluates the row filter before applying mapping and transformation and after mapping and transformation.

  • If, before processing the mapping/transformation, the row does not meet the criteria required to include the row in the import, then the app will skip the row.
  • Else, the app will apply the mapping/transformation and then re-evaluate the conditions using the values after mapping to determine whether to include or exclude the line.

Example: Skip Specific Rows from a file

In this example, we will import employee expenses from a file that contains a total per department. We will use the Row Filter to skip these lines.

Excel File

Skip Rows - Excel File

As you can see above, the lines with the total must not get imported because they do not contain any valid transactional data. If we import the file without applying the Row Filter, we get the following error:

Skip Rows - Error with no filter

Specify the Row Filter

Open the Excel Mapping Template Card and select the assist edit action next to the Row Filter.

Skip Rows - Error with no filter

The following page gets opened:

Edit Row Filter

  1. Select Skip for the Row Filter Type.
  2. Specify the conditions. For our example, we only need to specify one condition because every line we want to import has an Employee Name.
  • You can specify any column from the Excel File in your conditions - even if you have not mapped the column in your template.
  • The filter is for the text value imported from the file before it gets copied to the field.

After you close the page, the Row Filter is updated as below:

Skip Row - Final Template

Import the file

When you import the file, you will notice that all rows get imported except those that did not have a value in column A.

Example: Include Specific Rows from a file

We will use the template from the previous example to show you how to achieve the same outcome by specifying which lines to include rather than which to skip.

Update the Row Filter

Open the Excel Mapping Template and select the assist edit next to the Row Filter.

Edit Row Filter - Include

  1. Change the Row Filter Type to Include.
  2. Change the filter on column A's value to <>''.

Close the page. Notice that the Row Filter is updated.

Import the file

The file will import the same lines even though we specified the rule differently.

Copy Template

The Copy Template aims to copy existing templates as a starting point for a new template and copy templates between companies. The feature supports copying to a new template and copying from an existing template. If you change a template in one company, you can use the copy template feature to push these changes to other companies.

This Step-by-Step guide will show you how to copy a template to a different company.

Custom Values

The custom values feature allows you to map data columns from files that cannot map to a field in Business Central, and then you can do processing with this data.

Step-by-Step Guides

Create an Excel Mapping Template for Purchase Documents

This guide will take you through the steps to create an Excel Mapping template for importing purchase documents from an Excel spreadsheet.

Copy Excel Mapping Template

This guide will take you through copying an Excel Importer mapping from one company to one or more companies in Business Central.


Transformation Rule Example

On sales and purchase documents, you can add comment lines. While the Type gets formatted as the text Comment on the pages, this value cannot be entered into the field directly because the actual value is blank. This transformation rule converts the text Comment to blank.

Transformation Rule

Here is an example mapping template where we use the transformation rule:

Transformation Rule in Excel Mapping Template

Here is an example file that you can import with this configuration:

Transformation Rule Excel File Example

Here is the imported document:

Transformation Rule Imported Document