Skip to main content

Create or Edit Excel Mapping Template From File

Business Value​

The Create Mapping From File feature streamlines the process of creating or editing Excel mapping templates by allowing you to import your actual Excel file and configure field mappings whilst viewing the real file content. This eliminates guesswork and ensures accurate mappings by providing immediate visual feedback of your data structure.

Feature Details​

This feature is available from the Excel Mapping Template Card by selecting the Update Template From File action. It allows you to:

  • Import and preview your actual Excel file content
  • Create new mappings or edit existing ones whilst viewing the data
  • Visualise field mappings in real-time with your actual data
  • Test and validate mappings before applying them

Create Mapping From File Action

How It Works​

1. Upload Your Excel File​

When you click the Update Template From File action, you'll be prompted to upload an Excel file. This file will be used to preview data and create accurate field mappings.

Create Mapping From File Action

2. Interactive Mapping Interface​

Once uploaded, the setup page opens with three main sections that work together to provide a comprehensive mapping experience:

Create Mapping From File Setup

Section 1: File Structure Configuration​

Configure how the system interprets your Excel file structure:

  • Heading Row: Specifies which row contains column headers (defaults to row 1). This row appears in bold in the preview.
  • First Data Row: Identifies the first row containing actual data (defaults to row 2, assuming row 1 has headers).
  • Row Filter: Define filters to include or skip specific rows during import.
  • Sample Lines: Controls how many rows are displayed in the preview (for performance with large files).
  • Preview Data With Selected Mapping: Toggle to see how your data will look after applying transformation rules and mappings.

Section 2: Field Mapping Configuration​

Map Excel columns to Business Central fields whilst viewing the actual data:

  • Field Mapping: Select the target Business Central field for each Excel column. Use the lookup to browse available fields or type the field name directly. Create Mapping From File - Field

  • Field Type: Filter available fields by type. The system suggests field types based on your Excel data, but you can modify as needed. Create Mapping From File - Field Type

  • Data Transformation: Configure transformation rules for data conversion, formatting, or manipulation. Create Mapping From File - Transformation

  • Account/Dimension Mapping: Set up mappings for account codes and dimension values. Create Mapping From File - Mapping

Section 3: Live Data Preview​

View your actual Excel file content and see the impact of your mapping/transformation configurations in real-time:

Create Mapping From File - Preview Data

3. Apply Your Mappings​

Once you're satisfied with the field mappings, use the Apply and Close action to save the configuration to your Excel Mapping Template.

Create Mapping From File - Apply Mapping

Key Benefits​

  • Visual Accuracy: See exactly how your Excel data will map to Business Central fields
  • Real-Time Feedback: Immediately view the effects of mapping changes
  • Reduced Errors: Eliminate guesswork by working with actual file content
  • Faster Setup: Streamlined interface for both creating new templates and editing existing ones
  • Data Validation: Test mappings before applying them to ensure correct data transformation

Using Copilot to Suggest Mappings​

For even faster template creation, you can use AI-powered suggestions through the Suggest Mapping with Copilot (Preview) feature. This leverages Azure OpenAI to analyse your Excel data and automatically suggest appropriate field mappings.

How Copilot Suggestions Work​

  1. Activate Copilot: From the Create Mapping From File page, click Suggest Mapping with Copilot (Preview)
  2. Transparent Consent Process: Every time you use Copilot, a consent page appears that:
    • Shows you exactly what data from your file will be sent to the AI service
    • Allows you to edit the data before sending it to remove sensitive information
    • Lets you delete rows from the preview if they contain confidential data
    • Only sends the data that appears in the preview after your modifications
    • Requires your explicit consent before any data is transmitted
    • Provides complete transparency about what information leaves your system
  3. AI Analysis: After consent, Copilot analyses your Excel column headers and data patterns
  4. Smart Suggestions: The system suggests the most appropriate Business Central fields for each Excel column
  5. Review and Apply: Review the suggestions and apply them with the Use it button

Data Privacy and Transparency​

This is the only scenario in Excel Importer where Copilot uses your actual file data. The consent process ensures complete transparency by:

  • Showing Exact Data: You can see precisely what data will be sent to the AI service
  • Data Editing Control: Modify or remove sensitive information directly in the consent dialog
  • Row-Level Privacy: Delete specific rows containing confidential data before transmission
  • Preview-Only Transmission: Only the data visible in the preview after your edits is sent
  • Every Use Consent: Each time you use the feature, you'll see the consent dialog with the current data
  • No Hidden Processing: There are no other use cases where your data is sent to Copilot services
  • User Control: You have full control over when and what data is shared
  • Explicit Agreement: Data is only sent after you explicitly agree to the data usage

What Copilot Considers​

  • Column headers and their semantic meaning
  • Data patterns and types in each column
  • Context of the import type (e.g., General Journal, Sales, Items)
  • Field relationships and Business Central best practices

Benefits of Copilot Suggestions​

  • Instant Setup: Get complete mapping suggestions in seconds
  • Intelligent Matching: AI understands context and relationships between data
  • Learning from Patterns: Recognises common Excel structures and naming conventions
  • Reduced Manual Work: Eliminates the need to manually map each field
  • Smart Defaults: Provides sensible starting points that you can refine as needed
tip

Copilot suggestions work best when your Excel file has clear, descriptive column headers. You can always review and modify the suggestions before applying them to your template.

Administrative Control​

Administrators have the option to disable the Copilot mapping feature entirely if they prefer not to allow users access to AI-powered suggestions. This can be configured in the Copilot & agent capabilities page, providing organisational control over data sharing policies and ensuring compliance with internal security requirements.

When disabled, users will still have access to all manual mapping capabilities but will not see the "Suggest Mapping with Copilot" option, ensuring no data can be sent to external AI services.