Exporting data

Export tables, query results and script results with DUMP. PULL is a compatibility alias of DUMP (see "PULL, the compatibility alias"). The Import guide covers loading CSV data into an existing table.

Exporting data with DUMP

DUMP writes tabular data to one of three families of destination, all under one syntax:

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

None of them need to exist beforehand: a table's columns and types, or a spreadsheet's headers, are derived from the data itself. DUMP exports data; it is not a database backup tool.

The 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)

A column that cannot be mapped (large objects, binary, structural types) is rejected with a clear error before anything is written, for every format.

Time and timestamp values keep their fractional seconds. The text formats (CSV, TEXT, JSON, MD, HTML) write yyyy-MM-dd HH:mm:ss (JSON: yyyy-MM-ddTHH:mm:ss) and HH:mm:ss, followed by the fraction the value carries, in groups of three digits: 2015-05-07 09:49:01 stays as it is, 2015-05-07 09:49:01.016 keeps .016, and a microsecond value keeps its six digits. No .000 is added to a whole second. XLSX stores date and time values to the millisecond (the finest precision of Excel) and displays the milliseconds of the values that have some; ODS keeps the value as it is.

Time-zone-aware timestamps

BroadSQL exports time-zone-aware timestamps (TIMESTAMP WITH TIME ZONE, PostgreSQL's timestamptz, Oracle's TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE) deterministically: the same row gives the same file on every computer. BroadSQL never uses the computer's local time zone as an implicit conversion rule.

• The text formats keep the date, the time, the fractional seconds and the offset exactly as the database returns them. A value in UTC is written with +00:00.
• A spreadsheet cell has no time zone, so XLSX and ODS convert each value to UTC and store it as a real date/time cell, not as text. Sorting, filtering, formulas, formatting, pivot tables and date calculations work immediately in Excel and Calc, with no conversion of the column.
• DUMP / applies the same rules to the previous result.
• TIME WITH TIME ZONE values follow the same rules (UTC time in XLSX and ODS).
• A database driver that cannot return the offset gives BroadSQL only the moment itself: the text formats then write it in UTC (+00:00), never in the computer's time zone.

Time-zone-less DATE, TIME and TIMESTAMP values are exported as they are stored, with no time zone conversion in any format: 2024-01-15 10:00:00 stays 2024-01-15 10:00:00.

DUMP command and basic workflow

Run a query, look at the result, then export exactly that result with /:

SELECT * FROM COUNTRY WHERE CONTINENT = 'Europe';
DUMP /;

DUMP <source> [TO <destination>] [AS <format>] ;

<source> ::= <table or view name>
           | /
           | ( <query> )
           | LIB <script> [name=value ...]
           | @<script> [name=value ...]
           | API RESULT

• <source>: what to export: - a table or view name: every row of that table. A name containing spaces or other special characters is written in double quotes (DUMP "Sales Data" TO sales AS CSV). BroadSQL finds the table the name designates the same way as DESCR (the name as typed, then in upper case, then in lower case) and reads exactly that table, so DUMP "Customer" never exports a different table named CUSTOMER; - /: the previous tabular result (see "The previous result"); - a query in parentheses: run now; the parentheses are mandatory. The query may use variables written ${name} (see Script variables), passed to the database as typed values; the rest of the command (TO, AS) never replaces ${name}; - LIB <script> or @<script>: the final result of a Scripts Library script (see Exporting a script's result); - API RESULT: the last RUN result (see Exporting an API execution result).
• TO <destination> is optional. Without it, the file is named after the table, after the script (DUMP LIB sales.sql writes sales.<ext>), or after the DefaultExtFileName setting (results) for / and a query. Files are written to DefaultFolder (see Application settings).
• The destination's shape depends on the format: - AS XLSX/AS ODS/AS H2 take <name>[.<worksheet-or-table>]. Omitted, the worksheet or table is DATA: DUMP COUNTRY TO REPORT AS XLSX; is the same as DUMP COUNTRY TO REPORT.DATA AS XLSX;. - AS CSV/AS TEXT/AS JSON/AS MD/AS HTML take a bare <name>: a dot is a syntax error, since a flat file has no worksheet to address. - AS H2 always needs an explicit TO <connection>.<table>.
• AS <format> is optional. Omitted, BroadSQL uses DefaultFileFormat from BroadSQL.ini: DUMP COUNTRY TO WORKCOPY; means DUMP COUNTRY TO WORKCOPY.DATA AS <DefaultFileFormat>;. An explicit AS <format> always wins. If DefaultFileFormat is absent, ODS is used; if it is present but not one of XLSX/ODS/CSV/TXT, DUMP refuses the form without AS with a configuration error. Fix the setting, or give AS <format> explicitly.
• MODE applies to AS H2, AS XLSX and AS ODS (see MODE OVERWRITE, MODE APPEND and MODE APPEND KEY). MODE OVERWRITE, the default, rewrites the table or the named worksheet. MODE APPEND adds the rows to an existing table or worksheet with the same columns, names and order as the result, whatever the query. MODE APPEND KEY is for AS H2 only. The flat formats always fully rewrite the whole file.
• The Connections Definition File ($CDF) is never a destination: DUMP, PULL and EXPORT refuse it (see Local H2 datasets).

DUMP <table> alone, with nothing else on the line, keeps its original file naming: see "DUMP <table> alone".

The previous result (DUMP /)

DUMP / exports the most recent tabular result of the session, exactly as it was displayed: the query is not run again, so rows added or changed since then do not appear in the file.

SELECT ID, NAME, POPULATION FROM CITY WHERE COUNTRYCODE = 'FRA' ORDER BY POPULATION DESC;
DUMP / TO french_cities AS CSV;
DUMP / TO french_cities.TOP AS XLSX;

• Commands that do not produce a table (HELP, SET ..., an UPDATE) leave the previous result in place, so DUMP / still exports it.
• With no previous result in the session, DUMP / fails with "No previous tabular result is available to dump" and writes nothing.
• Only a complete result is exported. The limit is the one you configured: if the display stopped at MaxRowsOnScreen, DUMP / refuses (it would otherwise write a partial file) and suggests running the query through DUMP (<query>) instead, which exports every row. A result displayed in full is exported in full, whatever its size.
• A value the database can display but not return with its type (for example NaN or Infinity in a numeric column) never prevents the SELECT from being displayed. DUMP / then refuses to export that result, naming the column and the value, and writes nothing: convert the column to text in the query (for example CAST(<column> AS VARCHAR)), run it again, then DUMP /.

Practical examples

Export a query to CSV for a colleague (CsvSeparator decides between ; and ,):

DUMP (SELECT ID, NAME FROM CUSTOMER WHERE COUNTRY = 'FR') TO customers_fr AS CSV;

2 row(s) exported to C:\BroadSQL\export\customers_fr.csv.

ID;NAME
1;Ann
3;Chloe

Export what you just looked at, without running the query again, in the format set by DefaultFileFormat:

SELECT * FROM ORDERS WHERE STATUS = 'FAILED';
DUMP / TO failed_orders;

The format of each destination is described in Files and spreadsheets and Local H2 datasets.

Not supported

PULL, the compatibility alias

PULL runs exactly like DUMP: same sources, optional TO, destinations, formats, defaults and files, so every earlier PULL command keeps working. PULL / is the displayed result, like DUMP /: the query is never run again. Only its success message still says "pulled into". Use DUMP in new scripts. See PULL.

DUMP <table> alone

DUMP <table> with nothing else on the line keeps its original file naming and format choice:

• The file is written to DefaultFolder, named after the table exactly as typed (schema included: DUMP PUBLIC.CUSTOMER produces PUBLIC.CUSTOMER.<ext>); a spreadsheet's worksheet is named after the table too.
• At or below MaxRowXLSX rows (or when the row count cannot be determined), the format is DefaultFileFormat; above it, the file is always a tab separated .txt.
• The console reports "N records extracted to <file>".

The file itself is written like any other DUMP: a .csv uses CsvSeparator, and an .xlsx or .ods also gets the QUERIES worksheet.

Add AS <format> or TO <destination> to use the options described above instead, for example DUMP PUBLIC.CUSTOMER AS CSV;.

Scripts from earlier releases

Earlier releases taught two commands that still run, unchanged, but are no longer documented:

MS Access files are the one exception: DUMP has no .mdb format. EXPORT <file>.mdb; still writes the legacy Access 2000 (.mdb) format, using the Jackcess library, for scripts that rely on it; this format is not developed further.

Related command reference

• Import / Export overview: choosing a destination, and the other pages of this section.
• DUMP: exact command arguments and examples.
• PULL: the compatibility alias.
• LOAD and Import: loading CSV data back into a table.
• Application settings: DefaultFolder, DefaultFileFormat, DefaultExtFileName, CsvSeparator, MaxRowXLSX.