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. Excel Importer offers significant advantages over configuration packages.

While configuration packages can also be used to import master data, Excel 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. Excel Importer incorporates robust validations like those experienced when updating fields directly on a page, with additional safeguards to prevent inappropriate updates.
• Better Performance - Excel 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 are Not Intended for End-Users

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.

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. Excel 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.

Settings

Update Existing

Value	Description
Yes	Update existing records and add new records.
No	Only add new records.

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. Excel 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.

Settings

Update Existing

Value	Description
Yes	Update existing records and add new records.
No	Only add new records.

Items

You can access the import functionality from the Item List and the Catalogue Item List.

Settings

Update Method

This setting allows you to specify whether you expect to be adding new records only or adding new ones and updating existing ones.

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.

1. Prerequisites:     - Attributes must already be defined in Business Central     - Custom Value setup needs to be completed for each attribute you want to import.

2. 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

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.

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 Excel Importer Setup Wizard.

File

Looking at the file below, you can see:

1. The colour and material columns represent two different item attributes.
2. Sizes 1 to 6 represent item variants.

Mapping Template

Custom Values

note

• 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

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.

Settings

Update Existing

Value	Description
Yes	Update existing records and add new records.
No	Only add new records.

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. Excel 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.

Settings

Update Existing

Value	Description
Yes	Update existing records and add new records.
No	Only add new records.

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.

See Also

tip

• Excel Mapping Template
• Importing General Journals
• Request a New Feature