BCCL
Business Central Command Line
Import Excel Spreadsheet
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).
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
}