In this video, I will show you how to use master data input worksheet to import a list of items with a master data not created in the system that we would like to create.
The Master Data Input Worksheet in Business Central lets you import a list of items from an Excel sheet, even when some of the data uses master data fields that don’t exist in the system yet. You import in two steps. First you import the information values, then you import the items themselves and map them to those values.
Colors and sizes are not standard fields in Business Central. You add them as information codes under master data information, and you fill them with information values before you import your items.
You can prepare large data sets quickly by copying a column from your vendor’s Excel sheet, removing duplicates, and pasting the unique values into the import template. This works whether you have 22 lines or 10,000.
When you import, items with errors such as missing posting groups will still be created, but without the fields that failed. You fix those afterwards, often easiest by correcting the Excel sheet and importing again.
The scenario: importing vendor items with colors and sizes
Say you receive an Excel sheet from a vendor with a list of items. The item numbers follow the vendor’s own format, and the sheet includes extra information such as color, product group code, and size. In this example the last item number already in Business Central is 5060, and the vendor’s items use numbers like 66010.
The problem is that color and size are not standard fields in Business Central. You want to import the items and apply color and size at the same time. To do that, you handle them as master data information.
This example uses 22 lines, but the same approach works if the vendor sheet has 10,000 lines with hundreds of colors and many sizes. The technique scales without extra effort.
Preparing the Excel sheet for import
A colleague can prepare the Excel sheet so it matches the standard column setup in the item template. That makes the work easier later. In this example the vendor’s color was placed in the Name 2 column, and the size was placed in the product group code column (column G).
The remaining columns, such as prices and posting setup, are mapped in advance, so when you import you only need to worry about the fields that are specific to this batch.
Adding color and size as information codes
Before you import anything, check your master data information to see whether color and size already exist as information codes. If they don’t, add them.
Add Size as an information code and Color as an information code. At this point they have no information values yet, because you just created them.
If the data set were small, you could simply type in blue, red, yellow, and the different sizes manually. But with thousands of lines that isn’t practical.
Building the list of information values
For large data sets, build the list of values from the vendor sheet itself:
- Copy the color column and paste it into a new Excel sheet.
- Remove all the duplicates so you are left with a clean list of unique colors.
- Do the same for sizes.
- Paste both lists into the import template for information values.
The import sheet for information values has three things per row: the information code (color or size), and the matching information value. So you import under information code “Color” with the color values, and under information code “Size” with the size values. Even with large amounts of data, this makes the preparation quick.
Importing the information values
Open the Master Data Input Worksheet and import the data using the standard information value template, which matches the Excel sheet you just prepared.
Once you carry out the action, you get an import success. The information values now exist as a dropdown list on both the Color and Size information codes. After that you can delete the worksheet lines again, and the values stay in place.
Creating a template for the item import
Next, import the items. You need either a new template or a modified version of the existing one. Creating a new template is cleaner, because it avoids disturbing other people’s setups.
Create a new template, for example “Create item T-shirt”, then open the template card and adjust the columns to match your Excel sheet:
- The Name 2 column in the sheet maps to Description 2 in Business Central. (Note that the field is called Description 2, not Name 2.)
- Map the color column to master data information, information value, using the Color value you created.
- Map the product group code column (column 7) to master data information, information value.
- Map the size column the same way.
The rest of the Excel sheet is already mapped with the correct prices and posting setup, so the template only needs the specific master data fields.
Running the item import and handling errors
With the template ready, import the data using the new “Create item T-shirt” template and select the file. When you carry out the action, you can see the color in the worksheet, and if you scroll right you can see the sizes.
In this run, the posting groups were set up wrong, so the import returned errors. The important point is that the items were still created, just without the general posting group and inventory posting group. You fix that afterwards. Often the easiest path is to correct the Excel sheet and import again.
After refreshing the item list, all the new items are there. Open an item card and you’ll find everything filled in except the posting groups that failed. Open master data information on an item and you’ll see its color, for example yellow, and its size, for example XXXL.
The full workflow in short
This is the pattern for importing items with non-standard fields:
- Take all the data and map it into an Excel sheet.
- Use the Master Data Input Worksheet to import the information values first.
- Then import the items, mapping the relevant columns to master data information fields.
Q&A
How do you import items that use fields not standard in Business Central, like color and size?
Add color and size as information codes under master data information. Import the information values first using the Master Data Input Worksheet. Then import the items and map the relevant Excel columns to the master data information fields.
How do you prepare a large list of color or size values for import?
Copy the column from the vendor’s Excel sheet, paste it into a new sheet, and remove all duplicates. That leaves a clean list of unique values. Paste those into the information value import template under the matching information code.
What happens when an item import has errors, such as wrong posting groups?
The items are still created, but without the fields that failed. For example, an item can be created without its general posting group and inventory posting group. You fix those fields afterwards, often easiest by correcting the Excel sheet and importing again.
Should you modify the existing item template or create a new one for the import?
Creating a new template is cleaner because it avoids disturbing other people’s setups. Adjust the columns on the new template card to match your Excel sheet.
Which Business Central field does the vendor’s Name 2 column map to?
It maps to Description 2. In Business Central the field is called Description 2, not Name 2.