Back

How to understand the Standard Excel Import Template Sheets and the order of Columns

The Master Data Import Worksheet
Video 7/10
Play
Close
  • Helpful
  • Not helpful
  • Needs update
  • Technical error
An intermediate video requires some previous experience with Business Central, but it is still easily accessible to most people. Intermediate Watch the "basic" videos to take the tour of the main processes of Business Central. This is the basic, need-to-use functionality. The Basics This video includes functionality from the app "Master Data Information" which is available at Microsoft AppSource. Click to visit AppSource. Master Data Information

Playlists  Manage

Log in to create a playlist or see your existing playlists.

Open Playlist
Presenter: Sune Lohse, Chief Strategy Officer

When you use the standard template that we provide to import data in master data import worksheet, we have some Excel sheets and they are coded with some different colors and different formatting that you can use to understand how to set up data and how to work with the import.

Let’s take a look at those so you can understand how it works.

For instance, this is my item import template, and with this we can see some colors in the header.

We can see some field being gray, there’s a field being bolded.

We also have field that are italic somewhere on the lead time calculation field.

And this also have a different meaning so you can understand how to enter the field.

So for each Excel template, we have some of those coding.

If you look at the sheet, how to use this template, you can see what it means.

So the red text field that you should fill in for the template.

So at least for this table, the item table you should fill in the red columns.

The Baltics is an option string, which is an English where you should use the English caption.

So an option string is like a predefined dropdown list that you cannot change.

This means as an example, if you look at a bold thing in here, the costing method could be standard fee for average
and so on, but you cannot change it.

So bold meaning you have to enter one of the standard things.

And as you can see in the example, the red ones when you should fill in the great background means that data can be found in an underlying table before importing.

So if we have inter data in the column, it is those that are found in the supply chain box default setup.

So if you get the default setup from a back, we predefined information here that you should use.

Otherwise you should build those for yourself.

For example, if you take the base unit of measure, which is gray, the list of base units that we use should be the ones that you’d use in this sheet.

So if you call it each or something else in here, it wouldn’t apply when you import.

So this should apply to the underlying table.

Fields that are italic means that it’s a a date formula fields, so you have to write it correctly.

And in English, uh, with dates, week, months, or year, like six weeks for for six weeks.

That’s an example.

Again, if we look right here, the lead time calculation is an italic field, meaning you’ll have to enter the field here as a correct date formula.

And then we also thought about the order of the column.

So you shouldn’t just create templates without creating orders of the columns.

As an example, the vendor number here will change the lead time calculation on the item card.

So the column will be, or the data will be applied in The column order.

So when you use the import worksheet it in this example, it’ll first enter the vendor number 30,000, and then afterwards the lead time calculation, if you change that around and add the lead time calculation first, then when you add the vendor number, it will take the default lead time calculation from the vendor card and override on the item card because it validates data.

So we also did a lot of thinking about the order of the column.

So be aware if you change this on the tab called explanation of values.

You can see for the item table, which is the sheet we are looking in now, some of the standard data we have in the system.

So for instance, the dropdown list that you cannot change, which is defined by Microsoft.

The list of data that we build, this equals the gray ones that we suggest.

For instance here, the unit of measures that we come up with.

So here you can see a list of our standard setup if you use the A backend standard setup.

But of course you can set it up yourself.

And on the lookup table values, you can see which column those apply to.

So the base unit of measure has a dropdown list ing with the dropdown list and so on.

So this is just like a copy of the first sheet, but we added on which column you have the standard setup.

So the simple thing is just to fill in the import template in the order that we predefined and fill in the written columns, make sure that the great column has an underlying value, and make sure that the bolted column is written in the correct English caption.

939109627-B0j0yN7snU8-ENG24032819