Business Central Command Line

Import Excel Spreadsheet

BCCL can import data from an Excel spreadsheet (.xlsx files, not .xls) using the PUTDATA task.

You can specify the specific range of data you want to import. First, you must specify the sheet with the -x parameter to select the right sheet within the spreadsheet file

BCCL -t putdata -s table=18 -i customers.xlsx -x Customers

After specifying the sheet, you must specify the range of cells. With BCCL you don’t need to prepare the Excel sheet or do anything special, any range within a sheet can be used. Use the -y cellrange to specify the range. A range is separated with two dots, like A2..C50 (The Excel colon is sometimes problematic in command-lines so we’re sticking with Business Central syntax).

Data range is A2 .. C50 – The header line is not used.
BCCL -t putdata -s table=18 -i customers.xlsx -x Customers -y A2..C50

You don’t need a mapping file if you have the same number of columns as there are fields in the target table. Otherwise you need a mapping file:

{
  "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,
  "ValidateOnDelete": true
}

Notice, that the MappedName is the Excel column name.

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

Result:

{
  "Table": 18,
  "Inserted": 49,
  "Modified": 0
}
And the customers are created in Business Central