Excel Import Mapping

The Excel Import Mapping allows you to import product dimensions, properties, and additional materials from Excel files directly into the product configurator. By defining mapping rules, you can automate the transfer of data from structured Excel worksheets to BOM calculation templates.

Overview

With Excel Import Mappings, you create reusable mapping configurations that define how Excel cell values correspond to configurator fields. Each mapping consists of a header with general information and mapping lines that specify which Excel cells map to which configurator dimensions, properties, or additional material fields.

The mapping supports:

Dimensions: Map Excel values to product dimensions such as Length, Width, Height, Weight, or Unit.
Properties: Map Excel values to configurator properties defined in BOM calculation templates.
Additional Materials: Import lists of additional materials using loop zones that iterate over Excel rows.
Value Translation: Translate Excel values to Business Central system values automatically.
Unit Conversion: Apply conversion factors to convert between different units of measure.

How to Set Up an Excel Import Mapping

Choose the icon, enter Excel Import Mappings, and then choose the related link.
Choose the icon to create a new mapping.
Fill in the Code and Description fields.
Optionally, upload an example Excel file using the Upload Example File action. The example file appears in the Excel Preview pane on the right side of the page, making it easier to identify cell addresses.
Add mapping lines in the Lines section to define the data mapping.
When the mapping is complete, choose the Validate action to check for errors.
Choose the Release action to lock the mapping for use.

Tip

You can click cells in the Excel Preview pane to assign cell addresses to mapping lines. Use the assist-edit on the Source field of a line, choose Assign Source, then click the desired cell in the preview.

Fields and Options

Mapping Header

Field	Description
Code	The unique identifier for the mapping.
Description	A description of the mapping.
Description 2	An additional description for the mapping.
Status	Shows whether the mapping is Open (editable) or Released (locked for use).
Example File Name	Shows the name of the uploaded example Excel file. Click the field to upload a new file.

Mapping Lines

Field	Description
Type	Specifies the mapping type: Dimension, Property, Loop (Start), Loop (End), or Additional Material.
Value	Shows the mapped value depending on the type: dimension name, property code, search text, or material field.
Sheet Name	Specifies the Excel sheet name this mapping line reads from. If empty, the first sheet is used.
Source	Specifies the cell address (e.g. B2) or cell range (e.g. A7:A50). For loops, use the format Range;Position (e.g. A10:A20;A12).
Color Indicator	A color swatch that visually groups related mapping lines and highlights them in the Excel Preview.

Line Details

Open the detail card for a mapping line by choosing the Details action.

Field	Description
Dimension Value	Specifies the system dimension to map: Length, Width, Height, Weight, or Unit. Visible when Type is Dimension.
Property Code	Specifies the configurator property to map the Excel value to. Visible when Type is Property.
Add. Material Field	Specifies the additional material field to map: Description, Group, Item No., Width, Length, Thickness, or Unit. Visible when Type is Additional Material.
Search Text	Specifies the text to search for in the loop range. Visible when Type is Loop (Start) or Loop (End).
Source	Specifies the cell address or range in the Excel file.
Sheet Name	Specifies the Excel sheet name.
Conversion Factor	Specifies a multiplication factor for numeric values (e.g. 1000 to convert meters to millimeters).
Unit (Excel)	Specifies the unit of measure used in the Excel file. The system converts values to the BOM template unit automatically.
Required	Specifies whether the field is mandatory during import.
Default Value	Specifies the fallback value if the Excel cell is empty.
Missing Value Behavior	Specifies how to handle empty values: Skip Line, Error, or Use Default.

Value Translations

Value translations allow you to automatically convert Excel values to Business Central system values during import. This is useful when the Excel file uses different terminology than Business Central.

For each mapping line, you can define translation pairs on the Line Details card:

Field	Description
Excel Value	The value as it appears in the Excel file.
System Value	The corresponding value in Business Central.

Loop Zones for Additional Materials

Loop zones allow you to import repeating rows of additional material data from the Excel file. A loop zone is defined by a Loop (Start) and Loop (End) marker that identify the boundaries of the data range.

How to Set Up a Loop Zone

Add a mapping line with Type set to Loop (Start).
In the Source field, specify the cell range to search for the start marker (e.g. A1:A50).
In the Value field, enter the search text that marks the beginning of the data. This entry is optional (see Loop Markers Without a Search Text).
Add one or more mapping lines with Type set to Additional Material between the loop markers.
For each additional material line, set the Source to the column letter and a sample row (e.g. B12). The system calculates offsets automatically.
Add a mapping line with Type set to Loop (End). Enter the search text for the end marker, or leave it empty to import every row up to the first empty row.

Note

Only Additional Material lines are allowed inside a loop zone. Dimension and Property lines must be placed outside loop zones.

Tip

Use the anchor position to help the system calculate column offsets when the loop markers are not in the first column. Set the anchor position using the Set Anchor Position option from the assist-edit menu on a loop line.

Loop Markers Without a Search Text

You do not have to enter a search text for a loop marker. This is mainly useful for the Loop (End) marker: if you leave its search text empty, the loop imports every row until it reaches the first empty row and then stops. This way you can import a list that has no fixed end value, such as a material list that simply ends with a blank row.

A Loop (Start) marker can be left empty as well. In that case, the first empty cell in the search range is used as the start of the range.

Excel Preview

The Excel Preview is a built-in viewer that appears as a FactBox on the right side of the Excel Import Mapping card. It renders the uploaded example Excel file directly inside Business Central, so you can visually identify cell addresses and ranges without switching to an external application.

Purpose

Defining cell addresses manually (e.g. typing "C7" or "A10:A22") requires you to know the exact layout of the Excel file. The preview eliminates guesswork by displaying the actual spreadsheet content alongside the mapping lines. This makes the mapping process faster and less error-prone, especially for complex Excel files with multiple sheets.

How the Preview Works

The preview is powered by a JavaScript control add-in that runs inside the Business Central page. When you upload an example Excel file, the system converts it to a base64 string and passes it to the add-in, which renders the spreadsheet as an interactive HTML table.

Key capabilities:

Sheet Tabs: When the Excel file contains multiple sheets, tabs appear at the top of the preview. Click a tab to switch between sheets.
Cell Selection: Click a single cell or drag to select a range. The selected cell address and value are sent back to the mapping line.
Color-Coded Highlights: Each mapping line is assigned a unique color. The corresponding cells in the preview are highlighted with that color, so you can see at a glance which mapping line maps to which cells. The currently selected mapping line is shown with a filled highlight, while other lines use a border highlight.
Automatic Navigation: When you select a mapping line in the Lines section, the preview automatically scrolls to the referenced cell and switches to the correct sheet if necessary.

Assign Source Mode

Instead of typing cell addresses manually, you can assign them interactively:

Select a mapping line in the Lines section.
Use the assist-edit on the Source field and choose Assign Source.
Click the desired cell (or drag a range) in the Excel Preview.
The cell address and sheet name are automatically written to the mapping line.

For loop lines, there is an additional option Set Anchor Position to define the reference point for offset calculations.

Integration with BOM Calculation Templates

To use an Excel Import Mapping with a BOM calculation template, assign the mapping code on the BOM Calculation Template Card:

Open the BOM Calculation Template Card.
Fill in the Excel Import Mapping Code field with the desired mapping.
When a user opens the configurator for this template, the system automatically prompts for an Excel file upload and applies the mapping.

The Excel import works both in the standard configurator and when you create a single configured item directly from a BOM calculation template. When you start a new item from a template that has a mapping assigned, the system prompts for an Excel file and pre-fills the dimensions, properties, and additional materials before the configurator opens. For a single configured item, the Excel import is only offered when you create the item for the first time, not when you edit an existing configuration.

Exporting and Importing Mappings

Mapping configurations can be exported and imported as JSON files for sharing between environments:

Choose the Export action on the mapping card to download the configuration as a JSON file.
Choose the Import action to upload a previously exported mapping configuration.

Actions

Action	Description
Release	Validates and releases the mapping. A released mapping cannot be edited.
Reopen	Reopens a released mapping for editing.
Upload Example File	Uploads an Excel file for the preview pane.
Remove Example File	Removes the uploaded example file.
Export	Exports the mapping to a JSON file.
Import	Imports a mapping from a JSON file.
Validate	Checks the mapping lines for consistency errors.