How to read the Excel Import Template

When you set up a new company in Business Central, you can import master data such as customers, items and vendors using the Excel import templates from Abakion. The templates use color and text formatting to tell you exactly how to fill in each field, so the error handling during import becomes minimal.

Red text means the field is mandatory and must be filled in. Bold headers mean the field is an option string where you must enter the English caption exactly as specified. A grey background means the value must exist in an underlying table in Business Central before you import. Italic columns mean the field expects a date formula written in English using D, W, M and Y.

Each column header also shows a number that tells you the maximum field length in Business Central. If you exceed it, for example more than 50 characters in a name, the import fails.

The templates contain several worksheets: the import worksheet itself, a “how to use this template” explanation, a lookup values sheet and an explanation sheet for those lookup values.

What the Excel import templates are for

When you create a new company and want to import master data, Abakion provides a set of Excel import templates. There are templates for customers, items, vendors and more. The purpose is to let you fill in your data in a structured way so that it imports cleanly into Business Central with as little error handling as possible.

The templates come preconfigured. The columns match the fields you want to import: number, name, name 2, search name and so on. Don’t change these columns. They are matched directly into the master data import worksheet through the default templates we set up. If you leave the columns as they are, they fit the import directly.

The worksheets in the template

Each template contains several worksheets:

• The import worksheet is the first sheet, where the columns equal the fields you want to import. It also includes some example lines. Delete those example lines before you start importing your own data.
• The “how to use this template” sheet explains how to read the formatting. This is the key to understanding everything else.
• The lookup values sheet lists all the values you can enter for fields that depend on an underlying table.
• The explanation of value sheet describes what each lookup value refers to.

Before you do anything else, make a copy of the Excel sheet. That way you always keep the original as a reference for what you are doing.

How the column formatting works

The formatting tells you how to treat each field. Here is what each style means.

Red text means the field must be filled in as a minimum. On the item template, for example, the red fields include number, description, general product posting group, inventory posting group, replenishment system and VAT posting group. All of these must contain data.

Bold headers mean the field is an option string. You have to enter the English caption that matches one of the specified options. These options come from standard Business Central. The costing method, for instance, is a bold field, and you must enter one of the options: standard, FIFO, average and so on. You can see the available options in the lookup table sheet. For the costing method, the five options listed there are the only valid values.

A grey background means the value must exist in an underlying table before you import. If the lookup value sheets already contain data for that field, it means the value comes with the default setup of the supply chain box. The standard company setup you get from us already has those lookup values. If the field has no data, the underlying table is empty and you have to set it up yourself. So you either correct your own data to match our default setup, or you add more data to the underlying table before importing.

For example, the base unit of measure is grey, which means it relates to an underlying table. In the lookup table values you can see all the base units of measure that come with the standard setup. The item category code and the product group code, on the other hand, have no value in the standard setup. You can enter whatever you want on the first sheet, but you must add it to the underlying table before the import will work.

A field can be both grey and red at the same time. The general product posting group is one example. Red means it must be filled in, and grey means the value must exist in the underlying table. It comes with some default data you can use, or you can add more data to the underlying table.

Italic columns mean the field expects a date formula. You must enter the formula in the correct English expression using D, W, M and Y for day, week, month or year. On the item template, the lead time calculation field is italic. A value of 3W means three weeks. If you just enter “four” in that field, you will get an error when you try to import. The formula has to be written in the correct way.

Field length limits

Each column header includes a number after the field name that tells you how long the field can be in Business Central. If you enter a name longer than 50 characters, you will get an error during import because the field only allows 50 characters. The same applies to all the other fields, each with its own limit.

Some fields also have a description marked with a small red mark. That means there is a special description for that specific field explaining what it actually does.

Fields that relate to a table but are not validated

Some fields normally relate to an underlying table but are not validated into Business Central during import. On the customer template, you can enter postcode, city and contact even though no data is found in the underlying table. Postcode, country/region and contact normally relate to an underlying table, but because they are not validated, you can fill in these fields anyway without filling in the underlying table first.

Reading the lookup values and their explanations

The lookup values worksheet shows all the values you can enter for a given column. If you are filling in the cash flow payment method code, the sheet lists all the values we provide out of the box in the standard setup.

The explanation of value sheet helps when a value is hard to interpret on its own. It can be difficult to know what a country/region code like BG means, or what “10 days” refers to in the cash flow payment method code. On the explanation tab, the data comes from the underlying table, so you can see the actual name behind each code. For a country/region code you can see which country name it represents. For payment terms, CM+8D means current month plus eight days.

The import process

Once you understand the formatting, the process is straightforward. Make a copy of the template. Delete the example lines on the import worksheet. Fill in all your information on the import worksheet, following the formatting rules for red, bold, grey and italic fields, and respecting the field length limits. From there you import directly into the master data import worksheet in Business Central.

Q&A