Export

Export query results with PULL, or use EXPORT and DUMP for their existing file-output workflows. The Import guide covers loading CSV data into an existing table.

Exporting data with PULL

PULL copies a query's current results into one of three families of destination, all under one unified syntax:

• a table of a local H2 database (AS H2);
• a tab of an Excel or OpenDocument spreadsheet file (AS XLSX / AS ODS);
• a whole flat file, CSV, plain text, JSON, Markdown, or HTML (AS CSV / AS TXT / AS JSON / AS MD / AS HTML).

None of the three need to exist beforehand: a table's columns and types, or a spreadsheet's headers, are derived from the query itself.

The PULL examples below use the sample WORLD database's COUNTRY and CITY tables, which ship with BroadSQL for exactly this purpose.

COUNTRY (ACTIVITY_STATUS_ID, CAPITAL, CODE, CODE2, CONTINENT, CURRENCY_CODE, FIPS, GEONAMEID, GNP,
         GNPOLD, GOVERNMENTFORM, HEADOFSTATE, INDEPYEAR, INSERTION_DATE, ISO_NUMERIC, LAST_UPDATE,
         LIFEEXPECTANCY, LOCALNAME, NAME, NEIGHBOURS, PHONE, POPULATION, REGION, SURFACEAREA, TLD)

CITY (ACTIVITY_STATUS_ID, COUNTRYCODE, DISTRICT, ID, INSERTION_DATE, LAST_UPDATE, NAME, POPULATION)

Recommended alternative to EXPORT/DUMP for a one-shot export: PULL lets you name the destination format and file explicitly, in one command, instead of EXPORT's mode-toggle workflow or DUMP's automatic row-count-based format choice, and it's the only way to get JSON, Markdown, or HTML output at all, since EXPORT/DUMP don't have those. EXPORT/DUMP still work exactly as before and remain supported (see the EXPORT and DUMP section below), but for a new script, PULL is the more explicit, more predictable choice. Two differences worth knowing before you switch: PULL's CSV/TXT output doesn't honor SET SEP (see "Writing to a text file" below), and PULL rejects a column it can't map (large objects, binary, structural types) with a clear error rather than writing whatever the driver happens to return for it; true for every format, including the spreadsheet and H2 destinations. Oracle's TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE columns are supported despite not being one of the standard JDBC date/time types: every format writes them as a plain timestamp, without the zone or offset.

PULL command and basic workflow

Connect to the source database, run a query, then export it with / as the source. For example, on the sample WORLD connection:

SELECT * FROM COUNTRY WHERE CONTINENT = 'Europe';
PULL / TO REPORT.EUROPE AS XLSX OPEN;

PULL executes the source query again; it does not copy a frozen display buffer. Use a bare table name to export all its rows, or supply the query in parentheses.

PULL <source> TO <name>.<table> AS H2 [MODE OVERWRITE | MODE APPEND KEY(<column>) [FORCE]] ;
PULL <source> TO <name>.<tab>   AS XLSX | ODS [OPEN] ;
PULL <source> TO <name>         AS CSV | TXT | JSON | MD | HTML ;

<source> ::= <table or view name>
           | /
           | ( <query> )

• <source>: what to copy: - a bare table/view name: shortcut for SELECT * FROM <name> - /: the last query you ran - a parenthesized query: parentheses are always mandatory here
• The destination's shape depends on the format: - AS H2/AS XLSX/AS ODS take <name>.<table-or-tab>: a dot is required. - AS CSV/AS TXT/AS JSON/AS MD/AS HTML take a bare <name>: a dot is a syntax error, since a flat file has no tab/table part to address after one.
• MODE is only meaningful for AS H2 (see "MODE OVERWRITE"/"MODE APPEND KEY" below). Every other format doesn't accept a MODE clause at all: each always fully (re)writes its destination (the named tab for XLSX/ODS, the whole file for every flat-file format).

Writing to a local H2 database (AS H2)

<name> (before the dot): the target H2 database. If it already names a connection, that connection is reused (it must be H2; PULL refuses to touch a connection of any other type). Otherwise a new H2 database is created automatically and registered under that name. <table> (after the dot): the table to write into that database.

A newly created connection is registered as a standalone connection (no Database Group) in the built-in LOCAL environment; it is never attributed to an existing Database Group. Both the Environment and Database Group can be changed afterward for that connection from CONFIG.

Provenance (BROADSQL_PULL_AUDIT)

Every successful PULL ... AS H2 also records one row in BROADSQL_PULL_AUDIT, a table BroadSQL maintains automatically inside the destination database, so opening that H2 file later, even much later, still answers "where did this table come from, when was it last refreshed, from what query and connection, how many rows." One row per execution (never overwritten, so repeated refreshes build up a full history): query it like any other table:

SELECT * FROM BROADSQL_PULL_AUDIT
WHERE TARGET_TABLE = 'CITY'
ORDER BY EXECUTED_AT DESC;

BROADSQL_PULL_AUDIT is a reserved name: it cannot itself be used as a PULL ... AS H2 destination table.

Source form

Destination

MODE OVERWRITE

MODE APPEND KEY(<column>) [FORCE]

How MODE APPEND KEY(<column>) [FORCE] behaves

• First run (the target table doesn't exist yet): behaves like OVERWRITE's table creation, minus the drop: every row the query returns is loaded.
• Later runs: BroadSQL reads the current maximum value of <column> already in the target table, then only fetches and inserts source rows whose <column> is strictly greater. No row already in the target is ever updated or deleted.
• Re-running with no new source rows inserts zero rows: safe to run again at any time.
• <column> must be a single column of a numeric or date/time type (composite keys are not supported by APPEND).
• The source query may use JOIN (any kind: INNER/LEFT/RIGHT/FULL OUTER), but not a comma-joined or derived (subquery) FROM, nor GROUP BY/HAVING/ORDER BY/UNION/LIMIT/ OFFSET/FETCH; see "Not supported" below for the exact boundary.
• Before inserting, BroadSQL checks whether <column> could ever be NULL for this query. A confirmed-NULL-capable key is always refused: a NULL key can never satisfy the "greater than the last watermark" delta filter, so that row would be silently and permanently skipped by every future PULL. When BroadSQL cannot determine the answer (a computed/aliased key column, e.g. COALESCE(...)), it also refuses by default; add FORCE after KEY(<column>) to proceed anyway once you have checked yourself that the key can never be NULL for this query.
• Using an outer join (LEFT/RIGHT/FULL OUTER JOIN) always prints a caution note, regardless of the check above: an outer join can produce a genuinely NULL key for an unmatched row (e.g. a country with no cities yet), and depending on the database you're pulling from, BroadSQL's check may not be able to catch that case reliably; in some cases it can say a key is safe when it actually is not. If you use an outer join with APPEND KEY(...), double-check yourself that the key column can never be NULL for a row this query can return.

Typical use: mirror a table that gets old rows purged at the source (e.g. every 3 months) into a local H2 database with a much longer retention window, kept current by re-running the same command, with no manual bookkeeping:

PULL CITY TO ARCHIVE.CITY AS H2 MODE APPEND KEY(ID);

Run again next week, next month, whenever: only the rows added at the source since the last run are added locally; everything already pulled stays exactly as first captured.

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. Unlike AS H2, there is no connection lookup: it's just a file. <tab> (after the dot): the tab to write. Writing a tab that already exists in the file erases and replaces just that tab: every other tab is left completely untouched, whether the file was built by an earlier PULL or by something else entirely (a hand-built spreadsheet, or one produced by EXPORT/DUMP). This is the main advantage over EXPORT/DUMP: several PULL calls, each naming a different tab, build up one multi-tab file over time.

Every file also gets a QUERIES tab, maintained automatically: one row per data tab, recording that tab's query, the date it was last pulled, the source connection, and how many rows it holds. Re-pulling a tab updates its row in place rather than adding a new one. QUERIES is a reserved tab name: you can't PULL a tab called QUERIES yourself.

Add OPEN after AS XLSX/AS ODS to have BroadSQL open the resulting file automatically once the export succeeds, using whatever application is registered on your system for that file type (Excel, LibreOffice Calc, or anything else you have set as the default; BroadSQL does not require or launch Excel specifically):

PULL COUNTRY TO REPORT.COUNTRIES AS XLSX OPEN;

If the export itself fails, the file is never opened. If the export succeeds but the file could not be opened (no application registered for the file type, or no desktop integration available in your session), BroadSQL still reports the export as successful and simply prints a second warning: the file itself is always kept.

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

<name>: a plain file name with no dot at all (a flat file has no tab/table part to address after one), resolved to <default export folder>/<name>.<extension>. Always a full, clean overwrite of the whole file: there's no equivalent of AS XLSX/AS ODS's "just this tab" for a single flat file, and no per-file metadata (each file stays plain and self-contained; nothing is prepended to it).

CSV / TXT

The CSV field separator is not SET SEP. AS CSV uses a dedicated setting, CsvSeparator in BroadSQL.ini (a single character, e.g. ; or ,, defaults to ; if the setting is absent). AS TXT always uses a tab, regardless of any setting. Neither is affected by SET SEP, and there is no option to pick a different separator (e.g. a pipe) for a PULL-generated CSV/TXT file: a universal, well-formed file is preferred over a customizable one here. If you need a specific, non-standard separator, use EXPORT/DUMP with SET SEP instead (see the EXPORT and DUMP section below).

Both formats quote a field that contains the separator, a double quote, or a line break (doubling any embedded quote), so a value like O'Brien, Jr. never corrupts the file even with a comma separator.

JSON

A single JSON array, one object per row, keyed by column name: the shape most tools expect when asked for "the data as JSON". A number is written as a real JSON number when it's exact; a DECIMAL too precise to survive as a JSON number (which most parsers treat as a double) falls back to a quoted string holding its exact value, the same rule already used for Excel. Dates/times are ISO-8601 (2024-01-15, 14:30:00, 2024-01-15T14:30:00), the convention most JSON-consuming tools expect. A NULL is a literal, unquoted null.

Markdown

A GitHub-Flavored-Markdown table (header row, separator row, data rows): pastes cleanly into GitHub/ GitLab issues and PRs, Confluence, Notion, and Slack. A literal | in a value is escaped as \|; an embedded line break becomes <br> (rendered as a line break by every major GFM tool, since a raw newline would otherwise split one row into two broken ones). A NULL is a blank cell.

HTML

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

Exporting an API execution result

PULL API RESULT TO ... is the fourth PULL source form: the direct sibling of / (the last SQL query held in memory), for the last RUN result held in memory instead (see universal_api_client.md, "Universal API Client"). Every destination kind above works exactly the same way:

PULL API RESULT TO WORKCOPY.USERS AS H2;
PULL API RESULT TO REPORT.USERS AS XLSX;
PULL API RESULT TO USERS AS CSV;

The rows exported are the same columns/rows already shown on screen as a table by RUN (see universal_api_client.md, "Tabular results") - every column is text (VARCHAR); there is no numeric/date typing for an API result this release. MODE APPEND is not supported for this source (there is no source-side SQL shape to validate a delta filter against) - only the default MODE OVERWRITE applies. AS JSON for this source exports the flattened tabular shape (its columns), not the original raw API response body - the original raw body stays reachable separately, unaffected by any PULL.

Not supported

Exporting with EXPORT and DUMP

BroadSQL writes query results to a local file in one of two ways:

• EXPORT <fileName> (synonyms EXP, EXTRACT, EXT): a toggle. Once turned on, every SQL statement you run afterwards writes its result to that file instead of the screen, until EXPORT is called again with no argument (turns it off) or with a new file name. See command reference.
• DUMP <tableName>: a one-shot export of an entire table's content, in a single step. See command reference.

Both share the same destination-folder rule, the same extension-to-format mapping, and the same DefaultFileFormat setting described below.

Where files are written

• EXPORT: if fileName includes a directory, that directory must already exist or the command fails with an error. If it doesn't include one, the file is written to DefaultFolder (see Application settings).
• DUMP: always writes to DefaultFolder, named after the table exactly as typed (schema included, e.g. DUMP PUBLIC.CUSTOMER produces PUBLIC.CUSTOMER.<ext>). There is no option to choose the destination or file name. Unlike EXPORT, DUMP does not check that DefaultFolder exists first.

Choosing a format

The format is determined by the file's extension:

If EXPORT is given a file name with no extension, one is appended automatically based on DefaultFileFormat (see below): .xlsx, .ods, .csv, or .txt. DUMP never receives an extension from the user; it always picks one itself:

• At or below the MaxRowXLSX threshold (or when the row count can't be determined), it uses the extension matching DefaultFileFormat.
• Above MaxRowXLSX, it always writes a tab-separated .txt file, regardless of DefaultFileFormat or the configured FieldsSeparator.

DefaultFileFormat

INI setting ([General], see Application settings) controlling the default format above. Accepted values: XLSX, ODS, CSV, TXT (not case-sensitive). It is the only optional export-related setting:

• Missing from the file: BroadSQL prints an INFO message at startup and uses ODS.
• Present but not one of the four values: the value is ignored, BroadSQL prints an INFO message at startup explaining why, and uses ODS.
• Valid: used as given, no message.

This setting only supplies a default. Naming an extension explicitly always wins: `EXPORT totals.csv always produces a CSV file no matter what DefaultFileFormat` says.

CSV vs. TXT

Before this setting existed, .csv and .txt produced an identical file; only the extension differed. They are now distinct:

• .csv always uses a comma, regardless of FieldsSeparator or SET SEPARATOR.
• .txt (and any other/unrecognized extension) uses the configured separator (FieldsSeparator in the INI, or whatever SET SEPARATOR last set for the session, tab by default).

Both otherwise share the same writer and behavior (see "CSV / TXT" below).

Interrupting an export

Pressing CTRL+C during an .xlsx, .ods, .csv, or .txt export stops the write cleanly without closing the database connection: rows written so far stay in the file, the rest of the result set is discarded. (Not supported for .mdb.)

Per-format details

XLSX (Excel)

• A NULL value produces a blank cell (not 0/false/a placeholder date).
• DATE/TIME/TIMESTAMP columns get a real, typed Excel cell with a matching format (yyyy-MM-dd, hh:mm:ss, yyyy-MM-dd hh:mm:ss): the time-of-day on a TIMESTAMP is preserved, not truncated to midnight. Oracle's TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE columns get the same treatment, written as a plain timestamp (the zone or offset is not preserved).
• BIGINT/DECIMAL/NUMERIC values are written as a real (sortable, calculable) number when that round-trips exactly through a double; otherwise the cell falls back to exact text, since Excel numbers cannot represent arbitrary precision.
• The data sheet has an AutoFilter, a frozen header row, and bounded column widths.
• The sheet name is derived from the table/query and sanitized (Excel-forbidden characters removed, truncated to 31 characters). A second sheet named "Query" records the exact SQL text and when it ran.
• A sheet is capped at 1,048,576 data rows (Excel's own limit); beyond that, the export stops cleanly, the file remains valid, and a warning reports how many rows were and weren't exported.
• Binary columns (BINARY/VARBINARY/LONGVARBINARY/OTHER/DATALINK) are written as the text "Binary content, not exported to Excel"; a NULL binary value produces a blank cell.
• BLOB/CLOB/NCLOB are not handled explicitly; they fall into the generic text case.
• .xls (the legacy 97-2003 binary format) is not supported.

ODS (OpenDocument Spreadsheet)

• Same NULL-handling, typed DATE/TIMESTAMP cells (Oracle's TIMESTAMP WITH TIME ZONE/ TIMESTAMP WITH LOCAL TIME ZONE included), and 1,048,576-row safety cap as XLSX above (the cap here is a memory safeguard, not an ODF format limit; unlike XLSX's streaming writer, the whole sheet is held in memory before being saved).
• TIME columns have no dedicated cell type in the underlying library and are written as formatted text (HH:mm:ss).
• BIGINT/DECIMAL/NUMERIC values are written with their exact decimal value, with no precision loss to guard against (unlike XLSX, the format doesn't round-trip through a double).
• The header row is bold with a light blue background. There is no equivalent of Excel's "Query" recap sheet.
• Binary columns get the same "Binary content, not exported to ODS" placeholder as XLSX, but a NULL binary value correctly produces a blank cell.
• BLOB/CLOB/NCLOB are not handled explicitly; they fall into the generic text case.

CSV / TXT

• RFC 4180 quoting: a value containing the separator, a double quote, or a line break is wrapped in double quotes (internal quotes doubled).
• A true SQL NULL produces an empty field; the literal text "null" (or any other value) is written as-is; the two are never confused.
• Always written as UTF-8 with a leading BOM, independent of the console's own encoding, so tools like Excel recognize accented characters correctly when opening the file directly.
• Re-running EXPORT in append mode to an existing, non-empty file does not repeat the header row.

MDB (MS Access), legacy format, frozen

This format is frozen (28/08/2026): still fully supported, but not receiving further development. It is not part of PULL's unified formats (AS H2/AS XLSX/AS ODS/AS CSV/AS TXT/AS JSON/ AS MD/AS HTML, see the PULL sections above) and there is no plan to add it there. If your workflow depends on .mdb export and you'd like to see it continue evolving, let us know: real usage is exactly what would justify further investment.

• Written in the Access 2000 (.mdb) file format via Jackcess, not .accdb (the format Access itself has defaulted to since Access 2007).
• The table name is derived from the query/table the same way as the Excel sheet name (unsanitized).
• If the target .mdb file already exists, a new table is added into it; existing tables are left untouched. If it doesn't exist, a new database file is created.

Related command reference

• PULL: exact command arguments and examples.
• LOAD and Import: loading CSV data back into a table.
• Application settings: DefaultFolder, DefaultFileFormat, MaxRowXLSX, FieldsSeparator.
• EXPORT, DUMP, SET SEPARATOR command reference.