Importing Excel Files

BCCL supports importing data directly from Excel spreadsheets. This is convenient when your data originates from business users who work in Excel, or when you need to import data that includes formatted dates, numbers, and mixed data types. This page covers the Excel-specific parameters, mapping by column letter, data type handling, and common pitfalls.

Supported Excel Format

BCCL only supports the .xlsx format (Excel 2007 and later, also known as Open XML). The legacy .xls format (Excel 97-2003) is not supported. If you have an .xls file, open it in Excel and save it as .xlsx before importing.

Required Parameters

Excel imports require two additional parameters beyond the standard PUTDATA parameters:

Parameter	Flag	Description
Sheet name	-x	The name of the worksheet containing your data
Data range	-y	The cell range where your data begins and ends

Both parameters are mandatory for Excel imports. BCCL will report an error if either is missing.

Basic Excel Import Command

bccl -t putdata -s table=18 -i customers.xlsx -x Customers -y A2..C50

This reads data from the sheet named "Customers", starting at cell A2 and ending at cell C50.

Range Syntax

BCCL uses two dots (..) to separate the start and end cells in a range. This differs from Excel's colon notation:

Notation	System	Example
Two dots	BCCL	A2..C50
Colon	Excel	A2:C50

Important: Using Excel's colon notation (like A2:C50) will cause an error. Always use two dots: A2..C50.

Range Examples

Range	Meaning
A2..C50	Columns A through C, rows 2 through 50
A1..H1000	Columns A through H, rows 1 through 1000
B5..F200	Columns B through F, rows 5 through 200
A2..Z9999	All 26 columns, rows 2 through 9999

The start cell defines where BCCL begins reading data. Typically you set the start row to 2 (or wherever your data begins) to skip a header row. The end cell defines the last possible data cell -- BCCL stops reading when it reaches the end row, even if some rows are empty.

Importing Without a Mapping File

When you import Excel data without a mapping file, BCCL matches columns to table fields by position. The first column in the range maps to the first field in the table, the second column to the second field, and so on.

bccl -t putdata -s table=18 -i customers.xlsx -x Customers -y A2..C50

For this to work correctly, the number of columns in your range must match the number of fields in the target table (excluding blob, media, and mediaset fields unless -b true is specified). If the counts do not match, BCCL reports an error:

There's 3 columns in the uploaded file, but 68 fields in the Customer table, consider using a mapping file?

In practice, you almost always want to use a mapping file with Excel imports.

Importing With a Mapping File

When using a mapping file with Excel data, the MappedName property uses Excel column letters (A, B, C, etc.) instead of column header names. This is because BCCL reads Excel cell values by position, not by header names.

Example Mapping File for Excel

Suppose your Excel sheet has this layout:

	A	B	C
1	Customer Name	City	Post Code
2	Adatum Corporation	Redmond	98052
3	Trey Research	Atlanta	30329

Your mapping file would use column letters as MappedName:

{
  "Customer": [
    {
      "FieldNo": 2,
      "FieldName": "Name",
      "MappedName": "A",
      "Validate": true
    },
    {
      "FieldNo": 7,
      "FieldName": "City",
      "MappedName": "B",
      "Validate": true
    },
    {
      "FieldNo": 91,
      "FieldName": "Post Code",
      "MappedName": "C",
      "Validate": true
    }
  ],
  "ValidateOnInsert": true,
  "ValidateOnModify": true
}

And the import command:

bccl -t putdata -s table=18 -i customers.xlsx -x Customers -y A2..C50 -m mapping.json

Key Differences from CSV Mapping

Aspect	CSV Mapping	Excel Mapping
MappedName	CSV column header text	Excel column letter (A, B, C...)
Header row	Used for matching	Not used for matching
Column identification	By name	By position

Important: The header row in your Excel sheet is not used for matching. Even if row 1 contains column names, BCCL identifies columns purely by their letter position. The header row exists only for human readability. Set your range to start at the first data row (typically row 2) to skip it.

Data Type Handling

One advantage of Excel imports is that BCCL can read native Excel data types, preserving more precision than text-based formats like CSV. BCCL handles the following Excel cell types:

Numbers

Numeric cells are read as decimal values. BCCL uses the invariant culture when parsing numbers from Excel, so there are no locale-related issues with decimal separators.

Cell value: 1234.56
BCCL reads: 1234.56

Dates

Excel stores dates internally as OLE Automation dates (a floating-point number representing days since December 30, 1899). BCCL detects date-formatted cells and converts them to the standard yyyy-MM-dd format for Business Central:

Cell value: March 15, 2026 (displayed)
Excel internal: 46097 (OLE date)
BCCL reads: "2026-03-15"

If a date cell also contains a time component, BCCL preserves the full datetime value.

Times

Time-formatted cells are converted to HH:mm:ss.fff format:

Cell value: 2:30 PM (displayed)
BCCL reads: "14:30:00.000"

Strings

Text cells are read as-is. Shared strings (Excel's internal string deduplication) are resolved automatically.

Booleans

Boolean cells are converted to their logical values (true or false).

Empty Cells

Empty cells within the data range are skipped. BCCL tracks column positions and handles gaps in data gracefully.

Complete Excel Import Example

Here is a full end-to-end example of importing item data from Excel.

The Excel File (items.xlsx)

Sheet name: ItemData

	A	B	C	D
1	Item No.	Description	Unit Price	Base Unit
2	ITEM-001	Widget	25.50	PCS
3	ITEM-002	Gadget	42.00	PCS
4	ITEM-003	Component	8.75	BOX

The Mapping File (item_mapping.json)

{
  "Item": [
    {
      "FieldNo": 1,
      "FieldName": "No.",
      "MappedName": "A",
      "Validate": true
    },
    {
      "FieldNo": 3,
      "FieldName": "Description",
      "MappedName": "B",
      "Validate": false
    },
    {
      "FieldNo": 18,
      "FieldName": "Unit Price",
      "MappedName": "C",
      "Validate": false
    },
    {
      "FieldNo": 8,
      "FieldName": "Base Unit of Measure",
      "MappedName": "D",
      "Validate": true
    }
  ],
  "ValidateOnInsert": true,
  "ValidateOnModify": true
}

The Import Command

bccl -t putdata -s table=27 -i items.xlsx -x ItemData -y A2..D4 -m item_mapping.json

The Result

{"Table":27,"Inserted":3,"Modified":0}

Working With Multiple Columns

For sheets with many columns, column letters extend beyond Z into two-letter combinations (AA, AB, AC, etc.), following standard Excel conventions. Use the same letters in your mapping file:

{
  "FieldNo": 50,
  "FieldName": "Some Field",
  "MappedName": "AA",
  "Validate": false
}

Paging With Excel Files

For large Excel files, BCCL sends data in batches. The default page size is 1000 records. Each batch is committed as a separate transaction.

You can adjust the page size:

bccl -t putdata -s table=18 -i big_data.xlsx -x Sheet1 -y A2..Z50000 -m mapping.json -z 500

When a page boundary is reached during Excel reading, BCCL sends the current batch to Business Central, receives the response, and then continues reading the next batch from the Excel file.

File Locking

Excel files are locked while open in Microsoft Excel. If you attempt to import a file that is currently open, BCCL reports an error:

Cannot open Excel file, is the file already opened in Excel?

Close the file in Excel before running the import, or save a copy to a different location.

Automatic Number Series With Excel

Just as with CSV imports, you can let Business Central generate primary key values by omitting the key field from the mapping. Your Excel data would contain only the non-key fields, and the range should cover just those columns:

{
  "Customer": [
    {"FieldNo":2,"FieldName":"Name","MappedName":"A","Validate":true},
    {"FieldNo":7,"FieldName":"City","MappedName":"B","Validate":true}
  ],
  "ValidateOnInsert": true,
  "ValidateOnModify": true
}

bccl -t putdata -s table=18 -i new_customers.xlsx -x Sheet1 -y A2..B100 -m mapping.json

Tips and Best Practices

• Always close the Excel file before running an import to avoid file lock errors.
• Set the range end row generously. BCCL handles empty rows at the end gracefully, and it is better to set the range too large than too small.
• Remember that MappedName in Excel mapping uses column letters, not header text. Double-check your column letters.
• Use the two-dot range syntax (A2..C50), not Excel's colon syntax.
• For date fields, ensure cells are formatted as dates in Excel. If dates are stored as text, BCCL may not parse them correctly.
• Use verbose mode (-v) to see exactly what data BCCL reads from each cell when troubleshooting.
• If your sheet name contains spaces, wrap it in quotes: -x "My Sheet Name".

Related Topics

• Importing Data Overview
• Importing CSV Files
• Importing XML and JSON Files
• Advanced Import Features
• Command Reference