If you work with Business Central and need to update master data across many records at once, the master data import worksheet lets you do it in a single import. You can mix standard table fields with master data information in the same Excel sheet, so you don’t have to run separate processes for each.
You define master data information codes, such as an ABC code, with their own value lists. An information code can apply across different tables, for example both customers and items, if you don’t tie it to a specific table.
To import master data information, you map the Excel column to the information value in your import template, select the template, and carry out the import. The values then appear in the master data specification on each record.
Mixing standard fields and master data in one import
The master data import worksheet lets you import standard master data for the standard table together with master data information in the same import. In practice this means you can mix master data with standard field information in one Excel sheet.
Take a customer list as an example. The customers already hold some master data information. If you open the master data specification and drill down on the information code, you can see which information codes are already in place. From here you can add a new value to every customer in one go.
Preparing the Excel sheet for import
Say you want to add an ABC code to all your customers. In the prepared Excel sheet, you define the ABC codes for the customers you want to update.
You can either use an existing template or build a new one. In this example, the second column in the sheet is reused. It’s labelled “Name 2”, but since that field isn’t needed for anything else, you can repurpose it for the ABC code. You don’t even have to rename the heading in Excel, because the mapping is handled by column position in the template. The ABC code values go straight into that column.
Creating the information code and value list
Before importing, set up the master data information itself. In Business Central you create a new information code for your customers, for example one called ABC code. This is part of master data information creation. If the same code could also apply to items, you simply don’t attach it to a specific table.
Next, you need a dropdown list of information values, unless you plan to import the values directly. In this example only the value “ABC” is used, but you could add more letters if needed. Once the information code and the information value list are in place, the setup is ready.
Mapping the column in the import template
With the setup done, the only remaining task is to apply the ABC code to all your customers through the master data import worksheet.
Adjust the template so the import knows what to do with the column. You can change the template before carry-out, since the template only takes effect during carry-out. Open the customer template, find the master data import template card, and change the mapping for the column you reused (the one that was “Name 2”). Instead of a standard field, set it to be master data information, pick the information value, and select the new ABC code you created.
It’s worth checking the template before running. In this example the ABC code lands in column three of the import, even though the heading in the Excel sheet was “Name 2”. Open the import template card to confirm that column three maps to the master data information with information value ABC.
Running the import and verifying the result
In a real scenario you would normally delete all the other columns from the worksheet so you’re not re-entering all your existing data into Business Central. In this small example, all the existing customer data is carried out along with the new code, which isn’t the tidiest approach, but it works for the demonstration.
After carry-out, go back to the customer list. Open one of the customers, look at the master data specification, and you’ll find the ABC code matching the value from the Excel sheet.
It’s possible to add master data information directly during import by using columns in an Excel sheet when you import.
Q&A
Can I import standard fields and master data information in the same import in Business Central?
Yes. The master data import worksheet lets you import standard master data for the standard table together with master data information in the same import, mixing both types of data in one Excel sheet.
How do I add a new master data code, like an ABC code, to all my customers?
Create the information code (for example ABC code) and its information value list in Business Central, prepare an Excel sheet with the values, map the relevant column in the import template to the master data information value, select the template, and carry out the import.
Can the same information code apply to more than one table?
Yes. If you don’t attach the information code to a specific table when you create it, the code can apply across tables, for example both customers and items.
How does the import know which Excel column holds the master data value?
The mapping is handled in the import template by column position, not by the heading in Excel. You set the relevant column to be master data information and select the information value and code. You can open the import template card to confirm which column maps to the master data information.
Do I need to include all my existing data when importing master data information?
No. Normally you delete the other columns from the worksheet so you’re not re-entering all your existing data into Business Central. You only need the records and the column with the master data value you want to apply.