How to import master data easily into Business Central
In this video, I will show you how to read and understand the Excel import template that we provide when creating new companies and you want to import Master data.
It could be a template like customers, items, vendors, Etc. We have quite a few templates that you can use.
This is what happens in the video
To understand how to fill them in correctly so the error handling will be minimal, I will show you how the template could be understood and I’ll show that from the customer template and the item template.
So first of all, I will go through the worksheets in this template.
So we have the columns that equals the fields that we want to import: number, name, name 2, search name, Etc. So these are all the fields that the templates are default setup with.
Don’t change these columns because they are matched into the master data import worksheet with the default templates that we set up. So if you leave the columns like this, they will fit to the import directly.
Those columns has some codings that I will get back to to understand what the colors and the gray and the bolds are doing here.
And on the first worksheet, we also have some example and you will just delete those lines with all the examples when starting to import data here.
The next sheet is an explanation on how to read this template and I’ll get back to that because that’s the big key for understanding all of this.
In the next column, we have lookup values and I’ll get back to those as well to explain how those are working and the last worksheet we have the explanation of those lookup values that I will also get back to.
Now, let’s look at the item template because we have more different fields there to explain with.
Here is my item template and I will start by explaining all the columns and all the logic that we have embedded in this formatting and here’s how it works.
So on my how to use this template tab, I can see how to do it. So, first of all, make a copy of this Excel sheet so you will always have this Excel sheet as your base to see what you’re doing.
After making a copy, you can start working with data. This is how the columns are formatted.
So the red text implies that this is a field that you should fill in as a minimum. So if we look back on the import template for items, you should fill in as a minimum number, description, general product posting group out here, inventory product posting group, replenishment system, Etc.
All those red columns or red fields, VAT posting group, implies that those should be filled in. So this is the coding for red.
Next thing is the bold columns or the bold headers.
So this display, it’s an option string and you have to enter the English caption according to the option that we have specified.
Those options are coming from standard Business Central. So, for instance, the costing method, which is a bold field should be entered here as one of the options, standard, FIFO, average, Etc.
And those options can be seen in the lookup table sheet. So, for instance, here if I’m looking at my costing methods, these five options written here is the actual options that you have and you have to enter one of those of course.
Next is the field with the grey backgrounds. This is data to be found in an underlying table before importing, and if we have entered data in the columns in the last two sheets in the value sheet and explanation sheet, it is those who are found in our supply chain box default setup. So the standard company setup that you get from us will be with those lookup values.
If there’s no data entered in the field, it’s because the table is empty and you must set it up yourself.
So you must either correct your own data to match our default setup or even add more data to the underlying table before you import.
Let’s look at some examples here. So if we look at the base unit of measure, it’s grey meaning it’s applying or related to an underlying table.
And if I look at my lookup table values, I can see all the base unit of measure here that comes with the standard setup.
I can see here also that the item category code and the product group code has no value in the standard setup, meaning on my first sheet I can enter whatever here but I have to put it into the underlying table before I can actually import data.
Likewise, on my general product posting group. I have some information here and it’s grey and it’s red meaning red that it should be filled and grey meaning that it’s somewhere here in my general product posting group and I can see that it comes with some default data that I can use or I will have to add more data in the underlying table.
The last option here is if it’s italic, italic columns implies that it’s a date formula column and you have to enter a date formula correctly in the English expression using D, W, M and Y for day, week, month, or year and we have an example here in one of our items. Let’s scroll a little right.
We can see here our lead time calculation field is italic and the information here is 3W meaning three weeks. So if I have entered here just four like this, it will provide an error when I try to import data. So it has to be entered in the correct way.
The last thing we have here is if we have special information for this table that you could take into account and on the item, we don’t but if I go back to my customer like here and look at the same explanation here, of course, for the customer, you can note that in the customer table you can enter postcode, city, and contact although data is not found in the underlying table.
This means that normally postcode and country/region and contract relates to an underlying table, but it’s not validated into Business Central. So you can enter those fields anyway without filling in the underlying table.
On the header, you also have a number after each column, each field and it explains how long the field is in Business Central meaning if you enter a name in more than 50 characters, you will get an error while importing data because there’s only 50 characters likewise on all the other fields.
You also have descriptions on some of the fields with a little red mark on it. Meaning that there is special descriptions for these specific fields explaining what these fields actually do.
Worksheet lookup values is displaying all the lookup values that you can enter. So if you’re entering the column, cash flow, payment method code, this all the values that we provide out of the box and the standard setup, and on the explanation of value, you can see what they refer to so it could be difficult to understand what do we mean about BG as a country region or 10 days in the cash flow payment method code.
So on the explanation tab, we have explained that even more. So in the example of the country/region code that was BG or whatever it was, I can see here which name it actually has.
So this is data from the underlying table. So in this way, I can see my payment terms CM+8D meaning current month plus eight days, etcetera. So this is just explanation on the lookup values.
This is how the template works, so you will have to delete the lines in here and then enter all the information on the import template worksheet, the first worksheet, and from there you can import directly into the master data import worksheet.