How to Create an Excel Import Integration

This guide walks you through creating and configuring an integration to import Excel files into Business Central.

Prerequisites

Permission - You have NAVX IF STANDARD or NAVX IF ALL permission set
Excel File - You have an Excel file (.xlsx) with data to import
Target Table - You know which Business Central table will receive the data

Step-by-Step Process

Step 1: Create a New Integration

Navigate to Integration Framework → Integrations
Click New
Complete the following fields:
- Code - Unique identifier (e.g., "CUSTOMER_IMPORT")
- Description - Clear name (e.g., "Customer Master from Excel")
- Integration Type - Select Excel
- Direction - Select Import

Step 2: Configure Column Mapping

From the Integration, click Integration Fields
For each column in your Excel file:
- Click New
- Source Column - Enter column letter (e.g., "A", "B", "C") or number (1, 2, 3)
- Column Name - Enter the field name (e.g., "Customer Code", "Customer Name")
- Column Type - Select Standard for regular data columns
Optional: For fixed values that aren't in the Excel file:
- Column Type - Select Constant
- Constant Value - Enter the fixed value (e.g., "USD" for currency code)

Step 3: Map Fields to Business Central

From the Integration, click Integration Mappings
For each field, create a mapping:
- Source Field - Select the source field from Step 2
- Destination Table - Select target table (e.g., "Customer")
- Destination Field - Select BC field that receives the data
- Validate - Enable to validate field values during import
Optional - For lookups (e.g., matching Customer No. codes):
- Get Value From Lookup - Enable
- Lookup Table - Select lookup table
- Lookup Field - Select field to match against
- Return Field - Select field value to use

Step 4: Configure Excel Import Settings

Back on the Integration header, set these options:
- Skip Header Lines - Set to 1 (for typical Excel with header row)
- Skip Footer Lines - Set as needed
- Auto Process - Enable to automatically process after import

Step 5: Import and Review Data

Click Import on the Integrations list
Select your Excel file (.xlsx)
Review the imported records on the Integration Records page
- Status shows New or Ready to Process
- Verify data accuracy

Step 6: Process Data into Business Central

Select records to process (or all records)
Click Process or Process All
Records move to Completed or Error status
If errors exist, click Show Errors to view details

Key Features for Excel Import

Header Row Handling

If your Excel file has column headers:

Set Skip Header Lines to 1
The headers are skipped during data processing
Your source column references (A, B, C) map to the actual data rows

Data Type Handling

Excel import automatically handles:

Text - Imported as-is
Numbers - Converted to appropriate format (integer, decimal)
Dates - Must be in BC recognizable format
Empty cells - Treated as empty values
Mixed types - Column treated as text

Working with Multiple Sheets

To import from a specific sheet:

Create separate integrations for each sheet
Reference the sheet contents in distinct field mappings
Process sheets independently or in sequence

Common Scenarios

Scenario 1: Customer Master Import

Excel structure:

Column A: Customer Code
Column B: Customer Name
Column C: Phone Number
Column D: Fixed value: "USD" (currency)

Configuration:

Integration Type: Excel
Source Fields:
  - Field 1: "Customer Code" (Column A, Standard)
  - Field 2: "Customer Name" (Column B, Standard)
  - Field 3: "Phone" (Column C, Standard)
  - Field 4: "Currency Code" (Constant = USD)

Mappings:
  - "Customer Code" → Customer.No.
  - "Customer Name" → Customer.Name
  - "Phone" → Customer."Phone No."
  - "Currency Code" → Customer."Currency Code"

Scenario 2: Sales Order with Lookups