Customers, Vendors, Items, Resources, and Employees
Whether you're migrating to Business Central from another system or regularly updating or creating master data such as Customers, Vendors, Items, Resources, and Employees, it's crucial to have a fast and reliable method for your users. Excellent Importer offers significant advantages over configuration packages.
While configuration packages can also be used to import master data, Excellent Importer provides several distinct benefits:
- Versatile File Support: It allows the importation of both Excel and text files.
- Enhanced Adaptability: It supports various file formats and provides advanced mapping capabilities. This feature is particularly advantageous during system migrations, enabling you to import data in its original exported format without requiring additional reformatting.
- User Accessibility: Seamlessly integrated into lists, it enhances ease of access and usability, allowing users to work with data efficiently.
- Robust Built-in Validations: It prevents unauthorized alterations to critical fields that the application typically restricts, such as Unit Costs on items. Making changes to these fields when transactions are ongoing can cause significant issues. Excellent Importer incorporates robust validations like those experienced when updating fields directly on a page, with additional safeguards to prevent inappropriate updates.
- Better Performance - Excellent Importer is more than 10 times faster than configuration packages. We have examples where our customers have had to import files having more than 120K lines in less than 20 minutes compared to more than 8 hours using configuration packages.
Configuration Packages should not be accessible to end-users once your Business Central company is live and processing transactions, as untrained use can unintentionally cause data issues.
Default Dimensions for Master Data
When importing master data (Customers, Vendors, Items, Resources, Employees, Projects, and Fixed Assets), you can create and configure default dimensions using the Dimension type in your Excel Mapping Template. This works slightly differently from standard field mapping:
How Dimension Mapping Works
- Select Type = Dimension: Instead of selecting "Field" or "Related Field", select Dimension as the type
- Specify the Dimension: On that same mapping line, there is a field where you specify which dimension you're importing
- Specify the Value: Specify a constant value for that dimension or link it to a column in the file, just as you would with any other field
- Anchor Field Concept: The Dimension mapping acts as an anchor field - similar to how anchor fields work for item related tables. Any fields from the Default Dimension table that follow this anchor relate to that specific dimension.
Dimension fields cannot be included in concatenation, just like other anchor fields.
Available Default Dimension Fields
After specifying the Dimension anchor, you can map the following additional fields to configure how that dimension behaves:
- Value Posting: Controls whether the dimension is mandatory or same code
- Allowed Values Filter: Restricts which dimension values can be used (only applies when Value Posting is set to Code Mandatory)
Mapping Multiple Dimensions
You can import multiple dimensions for the same master data record by repeating the pattern:
- Dimension (anchor) - maps to both Dimension Code and Dimension Value Code
- Value Posting (optional)
- Allowed Values Filter (optional)
- Next Dimension (new anchor)
- Value Posting for next dimension (optional)
- Allowed Values Filter for next dimension (optional)
- And so on...
Each new Dimension column acts as an anchor, starting a new group of dimension-related fields.
Customers
You can import customers from the Customer List. The customer import also allows you to import bank accounts and ship-to addresses related to the customers you are importing. Since a customer can have more than one bank account and more than one ship-to address, the import will allow you to have multiple rows for the same customer.
Using Customer Templates
You can create customer templates in Business Central to specify default values for customers. Excellent Importer supports customer templates. You select the customer template as one of the fields in the field mapping. You can specify a constant value or include the customer template code in the import file.
You can import default dimensions for customers. See Default Dimensions for Master Data for details on how to configure dimension mapping using the anchor field concept.
Settings
Update Method
This is a common setting available on templates that support updating existing records. The supported options for this template are:
| Setting Value | Description |
|---|---|
| Add | Only add new records. |
| Update/Add | Update existing records and add new records. |
| Update/Skip | Only update records that already exist; skip records that don't exist without causing an error. |
Vendors
You can import vendors from the Vendor List. The vendor import also allows you to import bank accounts and order addresses related to the vendors you are importing. Since a vendor can have more than one bank account and more than one order address, the import will allow you to have multiple rows for the same vendor.
Using Vendor Templates
You can create vendor templates in Business Central to specify vendor default values. Excellent Importer supports vendor templates. You select the vendor template as one of the fields in the field mapping. You can specify a constant value or include the vendor template code in the import file.
You can import default dimensions for vendors. See Default Dimensions for Master Data for details on how to configure dimension mapping using the anchor field concept.
Settings
Update Method
This is a common setting available on templates that support updating existing records. The supported options for this template are:
| Setting Value | Description |
|---|---|
| Add | Only add new records. |
| Update/Add | Update existing records and add new records. |
| Update/Skip | Only update records that already exist; skip records that don't exist without causing an error. |
Items
You can access the import functionality from the Item List and the Catalogue Item List.
Settings
Update Method
This is a common setting available on templates that support updating existing records. The supported options for this template are:
| Setting Value | Description |
|---|---|
| Add | Only add new records. |
| Update/Add | Update existing records and add new records. |
| Update/Skip | Only update records that already exist; skip records that don't exist without causing an error. |
Item Variants
The app allows you to import and create item variants while importing the item information. To use this feature:
- When importing items, include the variant codes as columns in your data
- In the Mapping Template, the Type will be Custom Value and No. will be ITEMVARIANT.
- Each unique variant code creates a separate variant for the item
- Empty variant columns are skipped (no variant created)
- Existing variants with the same code are updated rather than duplicated
For more information, refer to the example below.
Item Attributes
The app allows you to assign item attributes while importing the item information. All attribute types (Text, Integer, Decimal, Date) are supported, and you can assign or unassign attribute values during the import.
-
Prerequisites: - Attributes must already be defined in Business Central - Custom Value setup needs to be completed for each attribute you want to import.
-
Template Configuration: - Add columns in your file for each attribute you want to import - Map these columns to Custom Values of type "ItemAttribute"
- Set the "Action Sub-type" to the specific Attribute ID
Example: Item Import with Variants and Attributes
This example shows you how to configure an Item import to handle variants and attributes. This example is also included in the sample templates and data, which you can access using the Excellent Importer Setup Wizard.
File
Looking at the file below, you can see:
- The colour and material columns represent two different item attributes.
- Sizes 1 to 6 represent item variants.
Mapping Template
Custom Values
- You only need one custom value setup for Item Variant, even if many variant columns exist. Therefore, the app creates this automatically.
- Specify the Processing Action and Action Sub-type for Item Attributes.
Import
In the animation below, we:
- Import the file using the item template that we set up.
- We use the Business Central feature Filter by Attributes to filter on items that have a Red or Black colour attribute.
- We then clear the attributes filter and inspect the variants created against two items
Item Related Tables
The app supports importing multiple related records for each item in a single import operation. This includes:
- Item References (GTINs, barcodes, vendor item numbers)
- Item Units of Measure (different packaging sizes, sales/purchase units)
- Item Translations (multi-language descriptions)
- Item Substitutions (alternative items)
How Anchor Fields Work
When importing multiple related records, the app uses anchor fields to group related data together. An anchor field identifies the start of each group of related information.
Key Concepts:
- Anchor Field: A field that marks the beginning of a new record group (e.g., Reference No., UOM Code, Language Code)
- Input Sequence: The order in which fields appear in your Excel template determines grouping
- Grouping Logic: All fields between anchor fields belong to the same record
Example: Multiple Item References
If your Excel file has columns for:
- Item No. (A1001)
- Reference No. 1 (GTIN1234567890123)
- Reference Type 1 (GTIN)
- Reference No. 2 (VENDOR123)
- Reference Type 2 (Vendor)
- Reference Type No. 2 (10000)
The app will create:
- Group 1: Reference No. = "GTIN1234567890123", Type = "GTIN"
- Group 2: Reference No. = "VENDOR123", Type = "Vendor", Type No. = "10000"
Supported Related Tables:
| Table | Purpose | Anchor Field |
|---|---|---|
| Item Reference | GTINs, barcodes, vendor item numbers, customer item numbers | Reference No. |
| Item Unit of Measure | Different packaging/selling units | Code |
| Item Translation | Multi-language descriptions | Language Code |
| Item Substitution | Alternative/replacement items | Substitute No. |
- Empty anchor fields are skipped (no record created)
- Each anchor field with a value creates a separate record
- Fields are processed in Input Sequence order from your template
- Field concatenation is NOT supported for related table fields - each field value remains separate and distinct within its group
Using Item Templates
You can create item templates in Business Central to specify item default values. The app supports item templates. You select the item template as one of the fields in the item mapping. You can specify a constant value or include the item template code in the import file.
You can import default dimensions for items. See Default Dimensions for Master Data for details on how to configure dimension mapping using the anchor field concept.
Example: Item Import with Multiple References and Units of Measure
This example demonstrates how to import items with multiple references (GTINs) and units of measure in a single operation.
File Structure
Your Excel file might look like this:
| Item No. | Description | Reference No. 1 | Reference Type 1 | Reference No. 2 | Reference Type 2 | UOM Code 1 | UOM Qty 1 | UOM Code 2 | UOM Qty 2 |
|---|---|---|---|---|---|---|---|---|---|
| ITEM001 | Sample Item | 1234567890123 | GTIN | VENDOR123 | Vendor | PCS | 1 | BOX | 12 |
| ITEM002 | Another Item | 9876543210987 | GTIN | PCS | 1 | CASE | 24 |
Mapping Template Configuration
In your Excel Mapping Template:
- Item Fields: Map normally (Item No., Description, etc.)
- Item Reference Fields:
- Reference No. 1 → Item Reference."Reference No."
- Reference Type 1 → Item Reference."Reference Type"
- Reference No. 2 → Item Reference."Reference No."
- Reference Type 2 → Item Reference."Reference Type"
- Unit of Measure Fields:
- UOM Code 1 → Item Unit of Measure.Code
- UOM Qty 1 → Item Unit of Measure."Qty. per Unit of Measure"
- UOM Code 2 → Item Unit of Measure.Code
- UOM Qty 2 → Item Unit of Measure."Qty. per Unit of Measure"
Result
For ITEM001, the import will create:
- Item References:
- Reference No. "1234567890123" with Type "GTIN"
- Reference No. "VENDOR123" with Type "Vendor"
- Units of Measure:
- Code "PCS" with Qty. per Unit = 1
- Code "BOX" with Qty. per Unit = 12
For ITEM002, the import will create:
- Item References:
- Reference No. "9876543210987" with Type "GTIN" (second reference skipped due to empty value)
- Units of Measure:
- Code "PCS" with Qty. per Unit = 1
- Code "CASE" with Qty. per Unit = 24
Example: Item Import with Translations and Substitutions
You can also import item translations for multi-language support and item substitutions for alternative products.
File Structure for Translations
| Item No. | Description | Lang Code 1 | Translation 1 | Lang Code 2 | Translation 2 |
|---|---|---|---|---|---|
| ITEM001 | Sample Item | da-DK | Eksempel Vare | de-DE | Beispiel Artikel |
File Structure for Substitutions
| Item No. | Description | Substitute 1 | Sub Type 1 | Substitute 2 | Sub Type 2 |
|---|---|---|---|---|---|
| ITEM001 | Sample Item | ITEM002 | Item | ITEM003 | Item |
Mapping Configuration
For Translations:
- Lang Code 1 → Item Translation."Language Code"
- Translation 1 → Item Translation.Description
- Lang Code 2 → Item Translation."Language Code"
- Translation 2 → Item Translation.Description
For Substitutions:
- Substitute 1 → Item Substitution."Substitute No."
- Sub Type 1 → Item Substitution."Substitute Type"
- Substitute 2 → Item Substitution."Substitute No."
- Sub Type 2 → Item Substitution."Substitute Type"
Catalog Items
Catalogue items are grouped by Entry Number (if supplied), along with Vendor No. and Vendor Item No. to properly identify unique records during import.
Important considerations:
- Updating existing catalogue items: You must supply the Entry Number stored in Business Central, as this is the primary key for the Catalogue Item table
- Multi-line imports: When importing multiple rows for the same catalogue item (e.g., with different vendors), the grouping logic uses Entry Number, Vendor No., and Vendor Item No. together to determine if rows belong to the same catalogue item
- Business Central validation: Per standard Business Central functionality, you cannot create more than one catalogue item for the same Vendor No. and Vendor Item No. combination
This grouping behaviour ensures that catalogue items with the same Entry Number but different vendor information are processed correctly during import operations.
Resources
You can import resources from the Resource List. The app supports using a configuration template to default fields that do not exist in a file.
You can import default dimensions for resources. See Default Dimensions for Master Data for details on how to configure dimension mapping using the anchor field concept.
Settings
Update Method
This is a common setting available on templates that support updating existing records. The supported options for this template are:
| Setting Value | Description |
|---|---|
| Add | Only add new records. |
| Update/Add | Update existing records and add new records. |
| Update/Skip | Only update records that already exist; skip records that don't exist without causing an error. |
Employees
You can import employees from the Employee List. The app supports using an Employee Template to default fields that do not exist in a file.
Using Employee Templates
You can create employee templates in Business Central to specify default values for employees. Excellent Importer supports employees templates. You select the employee template as one of the fields in the field mapping. You can specify a constant value or include the employee template code in the import file.
You can import default dimensions for employees. See Default Dimensions for Master Data for details on how to configure dimension mapping using the anchor field concept.
Settings
Update Method
This is a common setting available on templates that support updating existing records. The supported options for this template are:
| Setting Value | Description |
|---|---|
| Add | Only add new records. |
| Update/Add | Update existing records and add new records. |
| Update/Skip | Only update records that already exist; skip records that don't exist without causing an error. |
Examples
Example 1: Using an Employee Template
Here is an example of an Employee Template created for IT:
On the Excel Mapping Template, you select the Code field from the Employee Template table. In this example, we specified IT as a constant, but you can also include this value in your file.
