Output Formats

BCCL supports five output formats for exporting data from Business Central: JSON, XML, CSV, Excel (XLSX), and SQL. Each format has specific use cases and configuration options. You can specify the format with the -f parameter, or let BCCL auto-detect it from the output file extension.

Format Overview

Format	Parameter	Extension	Best For
JSON	`-f json`	`.json`	API integration, JavaScript, web apps
XML	`-f xml`	`.xml`	Enterprise integration, XSLT processing
CSV	`-f csv`	`.csv`	Spreadsheets, database tools, simple imports
Excel	`-f xlsx`	`.xlsx`	End-user reports, manual review
SQL	`-f sql`	n/a	Direct SQL Server replication

Auto-Detection

When you specify an output file with -o, BCCL can auto-detect the format from the file extension:

BCCL -t getdata -s table=18 -o customers.csv     # auto-detects CSV
BCCL -t getdata -s table=18 -o customers.json    # auto-detects JSON
BCCL -t getdata -s table=18 -o customers.xml     # auto-detects XML
BCCL -t getdata -s table=18 -o customers.xlsx    # auto-detects Excel

If you omit both -f and -o, the output defaults to JSON on the console.

JSON Format

JSON is the default output format. Data is returned as an array of objects.

Example Command

BCCL -t getdata -s table=18 -f json -o customers.json

Example Output

[
  {
    "No.": "10000",
    "Name": "Adatum Corporation",
    "City": "Miami",
    "Balance (LCY)": 0
  },
  {
    "No.": "20000",
    "Name": "Trey Research",
    "City": "Chicago",
    "Balance (LCY)": 5230.75
  }
]

Console Output

Omit -o to print JSON directly to the console (stdout). This is useful for piping to other tools:

BCCL -t getdata -s table=18 -f json | jq '.[] | .Name'

Notes

Property names match BC field names (or MappedName from mapping file)
Numbers are formatted as strings by default
Binary fields (when -b true) are Base64-encoded strings

XML Format

XML output wraps records in a root element with individual record elements.

Example Command

BCCL -t getdata -s table=18 -f xml -o customers.xml

Example Output

<root>
  <Customer>
    <No_>10000</No_>
    <Name>Adatum Corporation</Name>
    <City>Miami</City>
    <Balance__LCY_>0</Balance__LCY_>
  </Customer>
  <Customer>
    <No_>20000</No_>
    <Name>Trey Research</Name>
    <City>Chicago</City>
    <Balance__LCY_>5230.75</Balance__LCY_>
  </Customer>
</root>

Notes

Field names are sanitized for XML compatibility (special characters replaced with underscores)
The record element name comes from the table name
MappedName in the mapping file must also be XML-safe
Use the -r parameter when importing XML to specify the record path

CSV Format

CSV (Comma-Separated Values) is widely supported by spreadsheets and database tools.

Example Command

BCCL -t getdata -s table=18 -f csv -o customers.csv

Example Output

"No.","Name","City","Balance (LCY)"
"10000","Adatum Corporation","Miami","0"
"20000","Trey Research","Chicago","5230.75"

Configuration Parameters

Parameter	Short	Description	Default
`--delimiter`	`-l`	Field delimiter character	`,`
`--quotechar`	`-c`	Quote character	`"`
`--ignorequotes`	`-q`	Treat quotes as literal text	`false`
`--header`	`-h`	Include header row	`true`
`--language`	`-a`	Language ID for regional formatting	`0` (ISO8601)

Special Delimiters

Escape	Character
`\t`	Tab (TSV format)
`\n`	Newline
`\0`	Null
`&#\;`	Backslash

Tab-separated files omit quote characters. For pipe-separated: `-l "	"`.

See CSV Formatting for detailed formatting options.

Excel (XLSX) Format

BCCL can export data directly to Excel spreadsheet files.

Example Command

BCCL -t getdata -s table=18 -f xlsx -o customers.xlsx

Notes

Only .xlsx format is supported (not .xls)
You must specify an output file (-o); Excel cannot be written to console
The first row contains column headers
Data types are preserved where possible (numbers, dates, text)

SQL Format

SQL format writes data directly to a Microsoft SQL Server table, bypassing file output entirely.

Example Command

BCCL -t getdata -s table=18 -f sql -o Customer --sqlconnect "Server=localhost;Database=BCData;Trusted_Connection=yes"

Required Parameters

Parameter	Description
`-f sql`	Specifies SQL output format
`-o`	Target SQL table name (not a file path). Mandatory: if omitted, nothing is written to SQL and the data is printed to the console as JSON without an error.
`--sqlconnect`	SQL Server connection string

If a SQL export produces no rows and no error, check -o first. To separate BC-side from SQL-side problems, run the same command with -f json (no -o): if data comes back, connection, authentication, and table selection are fine and the problem is on the SQL side. See SQL Output Issues.

Optional Parameters

Parameter	Description	Default
`--sqlprecommand`	SQL executed before import	none
`--sqlzerodate`	Date value for empty BC dates	`1900-01-01`

Pre-Import Commands

Use --sqlprecommand to run SQL before the import starts:

BCCL -t getdata -s table=18 -f sql -o Customer --sqlconnect "..." --sqlprecommand "TRUNCATE TABLE Customer"

This is useful for full-load scenarios where you want to replace all data.

Upsert Behavior

SQL output uses upsert logic:

If a record with matching primary key exists, it is updated
If no matching record exists, a new record is inserted
Primary key matching uses the BC table's primary key fields

Background Workers

SQL imports run in parallel using background workers:

Up to 10 concurrent workers process data pages simultaneously
Each page is committed independently
This significantly improves throughput for large datasets

Connection String Examples

Windows Authentication:

Server=localhost;Database=BCData;Trusted_Connection=yes

SQL Server Authentication:

Server=dbserver.example.com;Database=BCData;User Id=sa;Password=yourpassword

SQL Type Mapping

BC Field Type	SQL Type
Text, Code	nvarchar(length)
Date	date
DateTime	datetime
Decimal	decimal(38,20)
Integer, Option	int
BigInteger, Duration	bigint
Boolean	bit
GUID	uniqueidentifier
Blob, Media, MediaSet	image
RecordID	varbinary(448)

BigInteger and Duration must be bigint. Declare BC BigInteger and Duration fields as bigint in the target SQL table. Declaring them as int causes System.OverflowException: Value was either too large or too small for an Int32. as soon as a value exceeds the Int32 range. Note that with BCCL 2.x clients the same exception can occur even when the column is bigint, because those clients could handle such fields internally as Int32; the BCCL 3.0 client resolves this. Generate the CREATE TABLE scripts with the SQL Bundle Builder rather than writing them by hand so the type mapping is correct. See the overflow entry in Common Issues.

Option and Enum fields are stored identically in the database and both map to int. A field that Microsoft re-declares from Option to Enum keeps the same field number and the same stored values, so it is not a change at the data level; Enum exists as an AL coding convenience so one value set can be shared across fields. See the Option and Enum questions in Frequently Asked Questions and the video explanation of options and enums.

Choosing the Right Format

Scenario	Recommended Format
Quick data inspection	JSON (console)
API integration	JSON
Enterprise data exchange	XML
Spreadsheet users	CSV or Excel
SQL Server replication	SQL
Scheduled automated sync	SQL
End-user reports	Excel

Output Formats

Format Overview

Auto-Detection

JSON Format

Example Command

Example Output

Console Output

Notes

XML Format

Example Command

Example Output

Notes

CSV Format

Example Command

Example Output

Configuration Parameters

Special Delimiters

Excel (XLSX) Format

Example Command

Notes

SQL Format

Example Command

Required Parameters

Optional Parameters

Pre-Import Commands

Upsert Behavior

Background Workers

Connection String Examples

SQL Type Mapping

Choosing the Right Format

Related Topics

Send Feedback