Feature Overview
The Import Worksheet is a bulk import tool for creating or updating master data records — such as items, customers, vendors, and item variants — along with their specifications, in a single operation. Instead of entering records one by one, you prepare data in an Excel file or directly in the worksheet, then carry out all actions in one step.
The feature has two modes. The Import Worksheet is used when you already have records in BC and want to update their specifications, or when you have a prepared data file from an external source. The Item Variant Creation Worksheet (also called the Creation Worksheet) is specifically for creating new item variants from scratch using a combination of specification dimensions. Both worksheets are driven by Import Templates that define the column structure — which fields map to which columns, and how each column is interpreted.
An Import Template defines up to 50 columns. Each column is either a standard BC table field (such as Item No. or Description) or a Master Data Information specification field (an Information Code). For import templates used to bring in data from Excel, you also configure the Excel Column number for each field, telling the system which Excel column maps to which BC field. Once the template is set up, you can use the Generate Import Template Excel Spreadsheet action to produce a ready-to-fill Excel file with the correct column headers.
The import flow is: load data into the worksheet → review the Action Message (New, Modify, etc.) for each line → accept the lines you want to carry out → run Carry Out Actions to execute. Lines that are not accepted are left in the worksheet without changes.
The Batch concept groups related import lines together — multiple templates for different record types (e.g. Items + Item Variants) can be combined into a single batch and run together in the correct sequence.
Key Facts
| Area | Detail |
|---|---|
| Where to open | Master Data Import Worksheet, Item Variant Creation Worksheet |
| Template setup | Master Data Import Template — defines columns and their mapping to BC fields or Information Codes |
| Max columns | 50 per template |
| Import source | Excel file (via Import Data action), or direct data entry in the worksheet |
| Action messages | New, Modify — review and accept before carrying out |
| Batch support | Multiple templates grouped into a batch for sequential execution |
| Copy from record | Templates can be set to copy from an existing item, including specifications and prices |
| Integration points | Information Codes, Information Values, Information Matrix (shared templates), Item Variants |
Getting Started
- Open the Master Data Import Worksheet from the Master Data Information menu.
- Choose Import Templates to open the template list, then create a new template or open an existing one.
- On the Master Data Import Template card, set the Table to the entity type you are importing (e.g. Item).
- Define the columns: for each column, set the Data Type (Table or Master Data Information), the relevant field or Information Code, and the Excel Column number that maps to it in your Excel file.
- Use the Generate Import Template Excel Spreadsheet action to create a blank Excel file with the correct headers.
- Fill in the Excel file with your data.
- Back on the worksheet, use Import Data to load the Excel file into the worksheet lines.
- Review the worksheet lines and check the Action Message for each line (New = create, Modify = update).
- Accept lines you want to process: use Change Action Message → Accept Action Message to mark them.
- Choose Carry Out Actions to execute. The app creates or updates all accepted records and their specifications.
Related Features
The Import Worksheet shares its Import Template structure with the Information Matrix — the same template that drives the matrix columns can also be used as an import template for the worksheet, and vice versa. Item Variants uses the Creation Worksheet with templates that define variant dimensions; variant creation is built on top of the Import Worksheet infrastructure. Information Codes and Information Values define the specification columns available in templates — only codes and values that have been set up can be referenced in a template column. Copy Master Data integrates with the Creation Worksheet: templates can be configured to copy from an existing item record, pulling across its specifications, prices, and unit of measure.
User Stories
US-01: Import a batch of new items with specifications from Excel
As a Purchasing Agent
I want to import 200 new items with their descriptions, categories, and key specification values from a supplier-provided Excel file
So that I do not have to create each item and its specifications manually in Business Central
Setup:
- Create an Import Template for the Item table and map the relevant columns to their Excel column numbers (Item No., Description, Item Category Code, and the specification Information Codes).
- Use Generate Import Template Excel Spreadsheet to produce the blank Excel file and share it with the supplier to fill in.
- When the filled file is returned, open the Master Data Import Worksheet and use Import Data to load it.
- Review the lines — all should show Action Message = New.
- Accept all lines and choose Carry Out Actions.
US-02: Update existing item specifications in bulk from Excel
As a Business Manager
I want to update the Certification and Weight specifications for 500 existing items using an Excel file exported from our data management system
So that our item specifications are kept in sync with the external system without manual entry
Setup:
- Export the current item specifications from the external system to Excel in the column format matching the Import Template.
- Open the Master Data Import Worksheet, load the file using Import Data.
- Lines for existing items will show Action Message = Modify.
- Accept all modify lines and run Carry Out Actions.
US-03: Generate the Excel template file from the Import Template
As a IT Manager
I want to generate a correctly formatted Excel import template so colleagues can fill in data without making column mapping errors
So that imports consistently succeed without manual correction of column mismatches
Setup:
- Open the Master Data Import Template card for the relevant template.
- Choose the Generate Import Template Excel Spreadsheet action.
- An Excel file is created with column headers matching the template configuration.
- Share the file with the relevant team members for data entry.
US-04: Create new item variants using the Creation Worksheet
As a Business Manager
I want to generate all colour and size combinations for a new clothing style using the Creation Worksheet
So that all variants are created in one operation rather than manually
Setup:
- Open the Item Variant Creation Worksheet.
- Select the Template that defines the variant dimensions (e.g. a template with Colour and Size columns).
- Optionally set Copy From Item No. to copy specifications from an existing item.
- Choose Suggest New Records and define the filter values for each dimension column.
- The worksheet generates lines for all combinations.
- Review and accept, then choose Carry Out Actions to create the variants.
US-05: Use a batch to import items and their variants together
As a IT Manager
I want to run a batch import that first creates new items and then creates their variants in the correct sequence
So that the import does not fail due to items not existing when the variant lines are processed
Setup:
- Create a Batch Import Template that references the Item import template first and the Item Variant template second.
- Open the Master Data Import Worksheet and select the batch.
- Load the Excel data file that contains both item and variant data.
- Accept all lines and run Carry Out Actions — the batch processes templates in order.
US-06: Copy from an existing item when creating new variants
As a Purchasing Agent
I want to create new variants of an existing item and automatically copy its base specifications, unit of measure, and prices
So that the new variants start with the correct base configuration
Setup:
- Open the Item Variant Creation Worksheet.
- Set Copy From Item No. to the item you want to copy from.
- On the Import Template, ensure Copy Master Data Information, Copy Item Unit of Measure, and Copy Purchase and Sales Prices are enabled.
- Generate the variant lines and carry out actions.
US-07: Review and selectively accept action messages before carrying out
As a Business Manager
I want to review each import line before committing, accepting most but rejecting a few that have data quality issues
So that only validated lines are carried out
Setup:
- After loading the data into the worksheet, review each line's Action Message and Field Error Hint.
- For lines that look correct, set Accept Action Message to true (either individually or via Change Action Message → Accept Action Message on a selection).
- For lines with issues, leave Accept Action Message as false.
- Run Carry Out Actions — only accepted lines are processed.
US-08: Re-use an Import Template for repeated imports
As a Purchasing Agent
I want to use the same Import Template every month when we receive updated specification data from our supplier
So that I do not have to reconfigure the column mapping each time
Setup:
- Create the Import Template once with the correct column mapping.
- Each month, open the Master Data Import Worksheet, select the template, and load the new Excel file.
- The column mapping is remembered from the template — no reconfiguration needed.
US-09: Import from a CSV file
As a IT Manager
I want to import specification data from a CSV file exported from an external system
So that I can keep BC specifications aligned with the external source without needing Excel
Setup:
- Configure the Import Template with the correct column mapping matching the CSV structure.
- Open the Master Data Import Worksheet and use the Import Master Data Information from csv file action.
- Select the CSV file, review the loaded lines, and carry out actions.
US-10: Copy an existing Import Template to use as a starting point
As a IT Manager
I want to duplicate an existing Import Template and adjust it slightly for a different supplier's file format
So that I do not have to configure the entire column mapping from scratch
Setup:
- Open the Master Data Template List and select the template to copy.
- Choose Copy current Template.
- Enter a new code and description for the copy.
- Open the new template card and adjust the column mapping as needed.
US-11: Reset a template to the app's default column configuration
As a IT Manager
I want to reset a template that has been customised back to the standard configuration provided by the app
So that I can start over with a clean baseline after incorrect modifications
Setup:
- Open the Master Data Template List and select the template to reset.
- Choose Update Template to newest standard.
- The template columns are reset to the app's default configuration for that table type.
Note: To reset all templates at once, choose Update all Templates to newest standard instead. Use with caution — this overwrites any custom column configuration on all templates.
US-12: Convert an Information Matrix template into an Import Template
As a IT Manager
I want to convert an existing Information Matrix template into an Import Worksheet template
So that I can reuse the same column configuration for bulk importing without reconfiguring it manually
Setup:
- Open the Master Data Template List and choose Import from Information Matrix.
- In the lookup, select the Information Matrix template to convert.
- Enter a code and description for the new Import Template.
- Confirm. The app creates a new Import Template with the same column structure as the selected matrix template.
Note: The resulting Import Template can be used immediately in the Import Worksheet. Excel column numbers are not automatically assigned — you will need to map the Excel column numbers on the new template card if you plan to import from Excel.
Item Variant Creation Worksheet
The Item Variant Creation Worksheet is a related but distinct tool used specifically for generating new item and item variant records from specification dimension combinations. Because its primary purpose is variant creation rather than data import, its user stories and field reference are documented in the Item Variants article. The Creation Worksheet shares the same Import Template structure and Carry Out Actions mechanism as the Import Worksheet.
Field Reference
Import Template fields:
| Field | Where to find it | What it does | Default |
|---|---|---|---|
| Code | Master Data Import Template | Unique identifier for this template | — |
| Description | Master Data Import Template | Display name | — |
| Table | Master Data Import Template | The BC table this template imports to (e.g. Item, Customer, Item Variant) | — |
| Copy from Record | Master Data Import Template | When enabled, copies data from an existing record when creating new records | Disabled |
| Copy Masterdata Information | Master Data Import Template | Copies specifications from the source record | Disabled |
| Copy Purchase and Sales Prices | Master Data Import Template | Copies prices from the source record (items only) | Disabled |
| Copy Item Unit of Measure | Master Data Import Template | Copies unit of measure setup from the source record | Disabled |
| Column N Data Type | Master Data Import Template — Column Setup | Whether this column maps to a BC table field or a Master Data Information specification | — |
| Table Field N | Master Data Import Template — Column Setup | The field name (when Data Type = Table) | — |
| Information Code N | Master Data Import Template — Column Setup | The Information Code (when Data Type = Master Data Information) | — |
| Excel Column Field N | Master Data Import Template — Column Setup | The Excel column number this column maps to | — |
Import Worksheet fields:
| Field | Where to find it | What it does | Default |
|---|---|---|---|
| Batch Name | Master Data Import Worksheet header | Groups lines by batch; controls which template sequence is used | — |
| Action Message | Worksheet line | Shows whether the line will create a new record (New) or update an existing one (Modify) | — |
| Accept Action Message | Worksheet line | When enabled, this line is included when Carry Out Actions is run | Disabled |
| Field Error Hint | Worksheet line | Shows validation warnings for this line | — |