Business Central Command Line
GetData
This page as walkthrough of some of the different ways you can use BCCL to get data out of Business Central.
First, we will let BCCL remember our credentials to BC and URL to avoid having to retype these parameters:
BCCL -w "http://bcserver:7047/BC/WS/CRONUS/Codeunit/bccl" -u demo -p demo --remember
The URL for BCCL can be found in Business Central under Web Services. Locate the bccl web service and copy the Soap URL to your command-line:
If you’re using a cloud instance of BC, use the –Auth OAuth (or S2S) to use OAuth authentication.
The next thing to do, is asking BCCL what it can do, called task types:
BCCL -t tasks
Requests like this are returned as json data: (result shortened)
{
"Tasks": [
{
"Task": "GETDATA",
"Description": "Get Data from a table",
"Codeunit": 50105,
"Parameters": [
"table",
"view"
]
},
{
"Task": "PUTDATA",
"Description": "Insert Data into a table",
"Codeunit": 50106,
"Parameters": [
"table"
]
},
{
"Task": "TABLES",
"Description": "Find Tables",
"Codeunit": 50104,
"Parameters": [
"name"
]
}
]
}
We can see that there is a tasktype called tables that takes one parameter name.
Let’s use that to find the customer table, we can use filter notation:
BCCL -t tables -s name=Customer*
This is will give us a list of tables where the name starts with Customer:
{
"Tables": [
{
"No": 6,
"Name": "Customer Price Group"
},
{
"No": 18,
"Name": "Customer"
},
{
"No": 92,
"Name": "Customer Posting Group"
},
{
"No": 266,
"Name": "Customer Amount"
},
{
"No": 287,
"Name": "Customer Bank Account"
},
{
"No": 340,
"Name": "Customer Discount Group"
},
{
"No": 923,
"Name": "Customer Sales Buffer"
},
{
"No": 5105,
"Name": "Customer Template"
}
]
}
With that, we can see that the customer table is number 18. And we can see that there is a getdata tasktype with a table parameter.
Now we can get all customers as CSV, the -t getdata and -s table=18 defines that we want data and the table we need is number 18 (Customers). The -f csv specifies that we want csv as the format and -o output.csv defines that we want the data in the file output.csv .
BCCL -t getdata -s table=18 -f csv -o output.csv
If we want only specific customers we add another setting to -s to specify the view/filters:
BCCL -t getdata -s table=18 view=where(No.=filter(10000..20000)) -f csv -o output.csv
This will gives us all columns, if we only wants certain columns, we can create a mapping file. The mapping file is a json file. It can either be created by hand, or we can ask BCCL to gives us one to edit.
BCCL -t mapping -s table-18 -o table18.mapping.json
This will give us a json file, looking like this: (most fields removed)
{
"Customer": [
{
"FieldNo": 1,
"FieldName": "No.",
"MappedName": "No.",
"Validate": "false"
},
{
"FieldNo": 2,
"FieldName": "Name",
"MappedName": "Name",
"Validate": "false"
},
{
"FieldNo": 3,
"FieldName": "Search Name",
"MappedName": "Search_Name",
"Validate": "false"
},
{
"FieldNo": 10022,
"FieldName": "Balance on Date (LCY)",
"MappedName": "Balance_on_Date__LCY_",
"Validate": "false"
}
],
"ValidateOnInsert": "false",
"ValidateOnModify": "false"
}
This file defines the columns we want in the file we’re getting from Business Central.
FieldNo | The Business Central Field Number |
FieldName | The Business Central Field Name (Not used, just for information) |
MappedName | The Column name in the file (element in XML, property in json) |
Validate | During data import, should this column be validated |
ValidateOnInsert | Should BCCL call the OnInsert trigger |
ValidateOnModify | Should BCCL call the OnModify trigger |
If we just want customer number and name, we can remove the rest from the mapping file.
{
"Customer": [
{
"FieldNo": 1,
"FieldName": "No.",
"MappedName": "Number",
"Validate": "false"
},
{
"FieldNo": 2,
"FieldName": "Name",
"MappedName": "Name",
"Validate": "false"
}
],
"ValidateOnInsert": "false",
"ValidateOnModify": "false"
}
(Notice that we changed the column name for No. to be Number.
And call BCCL again, this time adding the -m table18.mapping.json parameter:
BCCL -t getdata -s table=18 -f xml -o output.xml -m table18.mapping.json
will give us:
10000
Adatum Corporation
20000
Trey Research
30000
School of Fine Art
40000
Alpine Ski House
.....
Now that we’re done, we can use the –forget parameter to remove the remembered parameters.
BCCL --forget
SQL Support
If you specify the file type as SQL, GETDATA can write the data into a table on a Microsoft SQL Server. This video shows how to use SQL Server with BCCL: