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: