Excel Template ready to import Customers

The Master Data Import Worksheet
Video 7/24
  • Helpful
  • Not helpful
  • Needs update
  • Technical error
Watch "the details", if you need detailed knowledge about a specific topic. These videos are only relevant for particular users. The Details 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

How do you import customer master data into Business Central?

In this video, I will run through some of the most important fields in the customer import template and explain which ones should be handled or not.

This is what happens in the video

So let’s just start here from the beginning from the column E. Customer number, customer name should be filled in, of course, they’re mandatory.

Name 2 is often not used but you can put it in if you want to. The search name is often filled in automatically so you don’t have to change that if it’s going to be the same as the name, it will just be filled in.

The address, address 2, city, and postcodes gives itself so it’s just to fill in.

The postcode has some related tables that you can use or you can just enter whatever you want in here.

The country/region code is also not validated. So it’s possible just to enter but it’s actually in our lookup values here. So we have somewhere here a country/region default code. So if you can use the terms here, you make it easier for you to import so you don’t have to enter them by yourself.

Email, homepage, phone number, fax number, contact name, they all just to be put in. VAT registration number be aware that there’s some validation of that so it has to be correct, otherwise, you will get some errors here.

EAN number, bill-to-customer is often not used because if it’s the same as the customer, you don’t put in anything. It’s only if the customers choose for shipping to and the bill-to-customer someone else, you enter the customer number here.

Currency code and language code applies to underlying tables that we have pre-filled. So you have to check that those are correct here in the currency code and language code and make sure that you call them the same as we have made in the default setup.

Finance charge code is also put in the default setup so you can see them here in the explanation out here. On the right-hand side, we have the finance charges and you can add more, of course, if those two are not enough for you.

The cash flow payment code is also set up here in the lookup value. It’s not explained because it’s quite explainable by itself. But it’s also here and ready to be set up and if you want to add more than these, you can just put them directly in the underlying table.

Payment method code, payment terms code, and reminder terms code is also set up by default. So go into the lookup value, find them in here. Payment method, payment terms, reminder terms and likewise, the shipping method code is also set up as a default and you can enter that in here.

Whereas the location code is not something that we have set up. So you have to enter your own locations, of course, and make them reflect the data here. So enter the correct locations here as you have set up in the location table.

Shipping agent is not something that we have set up as well. These are just examples. We will have to– Oh sorry– So you’ll have to enter your own here if you have shipping agents and likewise with salespersons code. It’s not something that we have set up.

But of course, the VAT business posting group, general business posting group, and customer posting group is set up by default and you can find them here on the explanation value if you scroll right.

Here, they explained the default setup and of course if you change those and add more of those, you’ll have to make sure to make all the posting setup for those as well. So these are not because it’s complicated but there’s a lot of setup behind this you have to be aware of.

We didn’t set up any price group or discount group. It’s up to you to set up if you want.

And of course, you can block your customer if you prefer to do that, and you can set up shipping time, the shipping time provides a time delay from the shipment date and the plan shipment date on the sales order line if you want some outbound warehouse handling time to maintain here.

And you can set up base calendar code if you want the customer to be able to shop in specific days, and then the application code, application method and the last fields here doesn’t apply to underlying tables so you can just enter those.

Sorry the application method is of course an option field. So we have to add the option fields for that.

And you can see them here on the right side here the application method. You can also see the base calendar codes that we have provided that you could set up if you want to use those as default otherwise enter your own here as well. While entering all this field correctly, it should be very easy for you to import data directly into Business Central.