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.

CSV

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

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.

MODE APPEND keeps the worksheet and adds the rows of the result below its last row, when the worksheet's header row names the same columns, in the same order, as the result (without regard to case). The query and the data types are not examined. A worksheet that does not exist yet is created. When the columns differ, the file is not changed and the error names the difference. A result with no rows adds nothing; a NULL value is an empty cell. The rule is the one of MODE APPEND for H2 tables. OPEN may follow: AS XLSX MODE APPEND OPEN.

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 (after MODE APPEND, the total, not only the rows just added). 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.

Large spreadsheets

For a large result, write a new XLSX file or worksheet (or use MODE OVERWRITE): its memory use does not grow with the number of rows. Adding rows to an existing worksheet with MODE APPEND, and ODS files, are held in memory and are much slower; the exact behavior of each case is on the DUMP page. Two limits belong to spreadsheets:

• Plan free disk space for about twice the size of the final XLSX file while it is written: the rows wait in compressed temporary files, deleted at the end of the export.
• A worksheet holds at most 1048575 rows below its header. A larger result is refused before the file is replaced: use CSV or TEXT.

Example: a workbook with several worksheets

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

DUMP CURRENCY TO REPORT AS XLSX;
DUMP (SELECT TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES FROM COUNTRY GROUP BY CONTINENT) TO REPORT.BY_CONTINENT AS XLSX;

BroadSQL reports each export:

WORLD> DUMP CURRENCY TO REPORT AS XLSX;
166 row(s) exported to tab 'DATA' of c:\temp\REPORT.xlsx.
Completed: 2026-10-18 10:05:40 +02:00 (0:00:01).
WORLD> DUMP (SELECT TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES FROM COUNTRY GROUP BY CONTINENT) TO REPORT.BY_CONTINENT AS XLSX;
7 row(s) exported to tab 'BY_CONTINENT' of c:\temp\REPORT.xlsx.
Completed: 2026-10-18 10:05:45 +02:00 (0:00:00).

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

Example: a table to paste into a ticket

SELECT CODE, TRIM(NAME) AS NAME FROM COUNTRY WHERE REGION = 'Baltic Countries' ORDER BY CODE;
DUMP / TO baltic AS MD;

baltic.md contains:

| CODE | NAME |
| --- | --- |
| EST | Estonia |
| LTU | Lithuania |
| LVA | Latvia |

Related pages

• Import / Export overview
• Exporting data: syntax, sources, defaults, dates and times.
• Local H2 datasets: a local copy you can query.
• Application settings: CsvSeparator, DefaultFolder, DefaultFileFormat, MaxRowXLSX.
• Command reference: DUMP.

Found something wrong or unclear on this page? Tell us, with the page address.