BroadSQL BroadSQL Downloads
On this page
Documentation / Import / Export: Files and spreadsheets

Files and spreadsheets

DUMP writes a whole flat file (CSV, TEXT, JSON, Markdown, HTML) or one worksheet of a spreadsheet (XLSX, ODS). The syntax common to every destination, the sources and the defaults are in Exporting data.

Writing to a flat file (AS CSV / AS TEXT / AS JSON / AS MD / AS HTML)

<name>: a plain file name with no dot at all, resolved to <default export folder>/<name>.<extension>. Always a full, clean overwrite of the whole file, with no per-file metadata: each file stays plain and self-contained.

CaseExample
New CSV fileDUMP COUNTRY TO COUNTRIES AS CSV;
New TEXT file (.txt), tab separatedDUMP COUNTRY TO COUNTRIES AS TEXT;
New JSON file: an array of objects, one per rowDUMP COUNTRY TO COUNTRIES AS JSON;
New Markdown file: a GitHub-Flavored-Markdown tableDUMP COUNTRY TO COUNTRIES AS MD;
New HTML file: a bare <table> fragment, ready to paste into an email or wiki pageDUMP COUNTRY TO COUNTRIES AS HTML;
Running the same DUMP again: the file is fully rewritten, not appended toDUMP COUNTRY TO COUNTRIES AS CSV;

CSV

AS CSV writes standard CSV with one of two separators, chosen by the CsvSeparator setting in BroadSQL.ini:

CsvSeparatorSeparator
SEMICOLON (default when absent);
COMMA,

No other separator is supported, and there is no per-command separator. A value that contains the separator, a double quote or a line break is wrapped in double quotes, with embedded quotes doubled, so a value like O'Brien, Jr. never corrupts the file. The first line holds the column names. A NULL and an empty string both produce an empty field. Files are UTF-8 with a leading byte order mark, so Excel recognizes accented characters when opening them directly.

If CsvSeparator holds any other value, BroadSQL reports it at startup and AS CSV fails with a configuration error until it is corrected; the other formats are not affected.

TEXT

AS TEXT is always UTF-8, tab separated text (file extension .txt), whatever CsvSeparator says. Quoting and NULL handling are the same as for CSV. AS TXT is accepted as a synonym.

JSON

A single JSON array, one object per row, keyed by column name. A number is written as a real JSON number when it is exact; a DECIMAL too precise to survive as a JSON number (which most parsers treat as a double) is written as a quoted string holding its exact value, the same rule as for Excel. Dates and times are ISO-8601 (2024-01-15, 14:30:00, 2024-01-15T14:30:00); a time-zone-aware timestamp keeps its offset (2024-01-15T10:00:00+05:00, see Time-zone-aware timestamps). A NULL is a literal, unquoted null. The CSV separator setting has no effect on JSON.

Markdown

A GitHub-Flavored-Markdown table (header row, separator row, data rows) that pastes cleanly into GitHub and GitLab issues, Confluence, Notion and Slack. A literal | in a value is escaped as \|; an embedded line break becomes <br>. A NULL is a blank cell.

HTML

A bare <table>...</table> fragment, deliberately not a full web page (no <html>, <head> or <body>), so it pastes cleanly into an existing email or wiki page. The header row is lightly styled inline (bold, light background) so the styling survives the paste. &, < and > are escaped; a NULL is an empty cell.

Writing to a spreadsheet (AS XLSX / AS ODS)

<name> (before the dot): a plain file name, resolved to <default export folder>/<name>.xlsx or .ods. <worksheet> (after the dot, DATA when omitted): the worksheet to write. Writing a worksheet that already exists in the file erases and replaces just that worksheet: every other worksheet is left untouched, whether the file was built by an earlier DUMP or by hand. Several DUMP commands, each naming a different worksheet, build up one multi-sheet file over time.

CaseExample
New file, worksheet DATADUMP COUNTRY TO REPORT AS XLSX;
Second DUMP into the same file, another worksheet; both now coexistDUMP CITY TO REPORT.CITIES AS XLSX;
Running the first DUMP again; only DATA is rebuilt, CITIES is untouchedDUMP COUNTRY TO REPORT AS XLSX;
ODS instead of Excel: same syntaxDUMP COUNTRY TO REPORT.COUNTRIES AS ODS;

Numbers, dates, times and timestamps are written as real spreadsheet values, not text. A time-zone-aware timestamp becomes a date/time cell in UTC (see Time-zone-aware timestamps).

Every file also gets a QUERIES worksheet, maintained automatically: one row per data worksheet, recording its source, the date it was last written, the source connection and how many rows it holds. QUERIES is a reserved name.

Add OPEN after AS XLSX/AS ODS to have BroadSQL open the file once the export succeeds, using whatever application is registered on your system for that file type:

DUMP COUNTRY TO REPORT.COUNTRIES AS XLSX OPEN;

If the export fails, the file is never opened. If the export succeeds but the file cannot be opened, the export is still reported as successful, a warning is printed, and the file is kept.

Example: a workbook with several worksheets

Each DUMP names a worksheet; the others are kept. Two commands build one workbook:

DUMP CUSTOMER TO REPORT AS XLSX;
DUMP (SELECT COUNTRY, COUNT(*) AS CUSTOMERS FROM CUSTOMER GROUP BY COUNTRY) TO REPORT.BY_COUNTRY AS XLSX;
4 row(s) exported to tab 'DATA' of C:\BroadSQL\export\REPORT.xlsx.
3 row(s) exported to tab 'BY_COUNTRY' of C:\BroadSQL\export\REPORT.xlsx.

REPORT.xlsx now holds the worksheets DATA, BY_COUNTRY and the automatic QUERIES worksheet. Running the first command again rebuilds DATA only.

Example: a table to paste into a ticket

SELECT ID, NAME FROM CUSTOMER WHERE COUNTRY = 'FR';
DUMP / TO french AS MD;
| ID | NAME |
| --- | --- |
| 1 | Ann |
| 3 | Chloe |