How to understand and create a Master Data Import Template Card

The master data import template card defines how data flows from an Excel sheet into Business Central when you use the master data import worksheet. Each template points to a specific table, for example the item table or the customer table, and the table number is what determines where the data lands.

You set up the import column by column, with support for up to 50 columns per template. For each column you choose either a standard table field or a master data information field. Master data information fields let you import values, descriptions, and drill-down list entries tied to a specific information code.

You create new templates with the Copy Current Template action on the master data import list. This lets you make an exact copy of an existing template, such as the item template, and adjust the column mapping for a specific vendor without redoing the mapping every time.

What the master data import template card does

When you import data, you select a data template that tells Business Central what you are importing, for instance customers, items, or vendors. The system comes with a predefined list of templates, and you can open any of them through the import template card to see exactly how they are built.

On the right hand side of the card you can see which table the template applies to. The name you give the template does not matter for the import itself. What matters is the table number. If the table number points to the item table, the template imports items. If it points to the customer table, it imports customers.

Setting up columns in the import template

When you open the import template card, you see the full column setup. You can define up to 50 columns. Scroll down and you will see the columns run all the way to 50.

For each column you set a data type. If you choose the data type Table, the column maps directly to a field in the underlying table, for example the item table. When you then select the table field, you get a lookup into the fields of that table and pick the field you want to import into.

You can also assign a specific column in the Excel sheet to each line. If you don’t, the import assumes the Excel sheet is ordered the same way as the columns in the template. In practice, the easiest approach is to set up the Excel sheet in the same order as the template, so you don’t have to specify the Excel column at all.

Importing master data information fields

Instead of Table, you can set a column to the data type Master Data Information. When you do this, the field selection expands so you can pick an underlying master data information field.

You then choose what you want to import for that field:

• Information value for a drill-down list, for example an A, B, C code where the import picks up A, B, or C.
• Value or description for a free value, such as a date like a last date tested.

For each of these you also specify which information code the column applies to. For example, if column six is set up for an A, B, C information code, the import reads the dropdown value for that code on the item.

You can also use an information code filter when importing a value or description. A practical example is a wheel diameter. The wheel diameter is a value, but you want the information code to record whether it is in inches or millimeters. If you import in inches, you set up column six for inches, and you can add column seven as a master data information value for wheel diameter in millimeters. Columns six and seven then both apply in the same import.

Creating and modifying your own templates

You modify an existing template directly on the import template card by changing the column setup, for example resetting a column back to the item category code.

To create a new template, use the Copy Current Template action on the master data import list. Select a line and copy the template. The new template starts as an exact copy of the original, and you adjust the mapping from there.

A common scenario is a vendor that regularly supplies you with new items. You might get clothes sewn somewhere in China, and the vendor sends a list of item numbers to import. You want to reuse the item template but adjust it per vendor, because each vendor delivers data in a different layout. Instead of redoing the mapping for every import, you create a dedicated template per vendor.

To do this, select the item template, run Copy Current Template, and name the new template, for example item vendor with the vendor number. You can keep as many import templates for items as you need, and you can delete templates again when you no longer use them.

Q&A