Transcription of video
The Master Data Import Worksheet in Microsoft Dynamics 365 Business Central lets you create your own templates for importing data such as items. You can build a separate template for each vendor and define exactly which columns map to which fields.
Each template can hold up to 30 columns. If you need more than 30 columns for a vendor, you split the import across two or more templates and import the columns in sequence.
When you set up an item template, the first fields are locked to the item itself. You then map the remaining columns to either item fields or Master Data Information values. If you map a column to the wrong table, for example a customer field in an item template, you get an error.
Creating a template for a specific vendor
You can create as many templates as you like. A common use is to make a dedicated template for items from each vendor, since different vendors send their data in different formats.
To create one, go to the list of templates and add a new template. For example, you could call it “Item from Tires & CO”. You set the type to Item because you want to import items into the worksheet with this template. You can create templates for the other data types in the same way.
Defining the column setup
After creating the template, open the template card to define the column setup. The first fields are greyed out because they are reserved for the item itself. From the second column onwards, you decide what each column should contain.
You have to select either Item or one of the Master Data Information Values for each column. If you select something invalid, such as Customer, you get an error, because you cannot apply data to the customer table from an item template.
When you select Item, you can look up the field you want. For example, you can set Description as the second column. For the third column, you can enter the field name directly, such as the vendor item number, and the system finds the first field with that name. This makes the setup quick.
Mapping columns to Master Data Information values
For attributes that are stored as Master Data Information, you select Value instead of a standard item field. A typical example is the wheel diameter for tires.
When you choose Value, you also specify which Master Data Information the value applies to. For a wheel diameter, you point the column to the information code for inches. This way you can map every column the vendor sends, including custom attribute values.
Handling more than 30 columns
A single template supports up to 30 columns. If a vendor sends more than 30 columns, create more than one template for that vendor, for example “Tires & CO import 1” and “Tires & CO import 2”. You then import the first set of columns and import the second set afterwards.
With this approach you can define as many templates as you need across all the different data types.
Q&A
How many columns can a single Master Data Import Worksheet template contain?
A single template supports up to 30 columns. If you need more, you create additional templates for the same vendor and import the columns in sequence.
Can I create separate import templates for different vendors?
Yes. You can create as many templates as you like and define each one to match how a specific vendor delivers its data.
Why do I get an error when mapping a column in an item template?
You get an error if you map a column to a field from another table, such as a customer field, because you cannot apply data to the customer table from an item template. You must select either Item or one of the Master Data Information Values.
How do I import an attribute such as wheel diameter for tires?
Map the column to Value instead of a standard item field, then specify which Master Data Information it applies to, for example the information code for inches.