Importing customers, items, or other master data into Business Central often turns into a manual chore. The Master Data Import Worksheet gives you a structured way to bring in records from an Excel sheet, validate them against your data model, and fix any errors before the data lands in your system.
You work from a predefined import template that maps each Excel column to the correct field in Business Central. This means you only have to paste your values into the right columns and run the import.
When the import runs, each line is marked as either a success or an error. You can filter out the successful lines, focus on the errors, correct them directly in the worksheet, and run the import again until everything succeeds.
Common errors include invalid location codes and incorrect country/region codes. For example, Spain uses the code ES, not SP, and a location code must match an existing code such as “basic” rather than a typo like “simple”.
What the Master Data Import Worksheet does in Business Central
The Master Data Import Worksheet lets you import new master data records into Business Central from an Excel sheet. A simple example is importing new customers, but the same approach works for other master data types.
You open the worksheet, choose an import template, and select the Excel file containing your data. The worksheet reads the file and lines up the data for import.
Using import templates to map Excel columns to fields
The worksheet relies on a predefined import template. In this example, the template is called “customer” and already has a set of fields defined. The template also supports item templates for those predefined fields.
The big advantage is that the columns in your Excel sheet follow the template. The customer numbers, names, and all the other columns map directly to the fields defined in the template. You don’t have to do any manual mapping. You just paste the right values into the correct columns and import.
Running the import and handling multiple worksheets
When you select your Excel file, Business Central shows you the worksheets inside it. If the file contains more than one worksheet, you pick the one you want. In this example, the file had two worksheets, and the relevant one was called “customer”.
Once the data is loaded into the worksheet, the action is set to “create”. You then carry out the actions to run the import. If some of the customers already exist, the import updates the existing data instead of creating duplicates.
Reading success and error lines after import
After you run the import, each line shows its status. Lines marked as success have been imported correctly. Lines with errors need attention before they can go through.
To investigate an error, scroll right on the line until you find the field causing the problem. The worksheet shows you which value failed. In this example, a line had a location code called “simple”, which doesn’t exist in the system.
Some fields support a lookup so you can pick a valid value directly. Whether a lookup is available depends on the data model behind the field. To find the correct location code, you look it up in Business Central, see that it is actually called “basic”, and change the value in the worksheet.
Filtering out successful lines to focus on errors
A practical way to work through a large import is to remove the lines that already succeeded. Filter on the success status and delete those lines, since they are already in the system and don’t need any more work.
When you remove the filter, you are left only with the lines that still have errors. This gives you a clear overview and makes it easy to find every column that needs a fix.
Typical fixes include correcting a country/region code (Spain is ES, not SP) and correcting a location code (basic instead of simple). When a line has errors in several columns, the worksheet keeps showing you the columns that still need correcting.
Re-running the import until everything succeeds
After correcting the errors, you carry out the actions again. The worksheet re-validates the lines and reports which ones now succeed. You repeat this cycle until all lines are marked as success.
This workflow makes it straightforward to import master data and manage the errors along the way, without having to clean everything up before you start.
Q&A
What is the Master Data Import Worksheet in Business Central used for?
It lets you import new master data records, such as customers or items, into Business Central from an Excel sheet. You use a predefined import template that maps the Excel columns to the correct fields, then run the import and handle any errors directly in the worksheet.
How does the import template make importing easier?
The template defines which fields you want to import and what they map to. Because your Excel columns follow the template, you only need to paste your values into the correct columns. There is no manual field mapping during the import.
What happens if some of the records I import already exist?
When you carry out the import, you can choose to update the existing data instead of creating duplicates. Existing customers are updated, and new ones are created.
How do I find and fix errors after running an import?
Each line is marked as success or error. Scroll right on an error line to see which field failed, then correct the value. You can use field lookups where available to pick a valid value. After correcting, carry out the actions again to re-validate.
How can I get a clear overview of only the lines with errors?
Filter on the success status and delete the successful lines, since they are already imported. When you remove the filter, only the error lines remain, making it easy to see every column that still needs correcting.
What are common errors when importing master data?
Common errors include invalid location codes and incorrect country/region codes. For example, a location code must match an existing value like “basic” rather than a typo, and Spain uses the country/region code ES, not SP.