Skip to content

Exporting Results (@export)

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

With the @export client-side command, you can write the results of queries in an SQL script to files instead of showing them in grids. Because it is a script, you can save it, run it again, and run it from the command-line interface to automate exports. For large results, @export also uses less memory than exporting a grid, because the rows are written to the file as they are read.

You can export to these formats: CSV, Excel (XLSX and XLS), JSON, HTML, XML, SQL (INSERT statements) and TXT (text with fixed-width columns).

Quick start

This script exports the ACTOR table to a CSV file:

@export on;
@export set Filename="/tmp/actor.csv";
SELECT * FROM actor;
@export off;

When you run the script in SQL Commander, the result is written to the file instead of being shown in a grid, and the Log tab shows the name of the file.

How an export works

An export is a block of statements in a script:

  1. @export on starts the export. It has no parameters.
  2. One or more @export set commands set the file and the format.
  3. The results of all queries that follow are exported, including the results of @call, @desc and @ddl. Statements that don't return a result, such as UPDATE, are run as usual.
  4. @export off ends the export. It has no parameters.

In SQL Commander, @export exports at most the number of rows set in the Max Rows field in the toolbar. To export all rows, add @set maxrows -1; before the export. See Exporting all rows.

You can change most parameters between queries using another @export set command, but not Format, Destination or ExcelFileFormat after the first result has been exported.

File names and formats

Choosing the format

If you don't set Format, it is chosen from the extension in Filename:

ExtensionFormat
.csvCSV
.xlsx, .xlsExcel
.jsonJSON
.htmlHTML
.xmlXML
.sqlSQL
Any otherCSV

For TXT, and for other extensions, set Format explicitly.

One file or several files

With a fixed file name, what happens to the results of several queries depends on AppendFile:

  • false (default): each result overwrites the file, so the file only contains the last result.
  • true: the results are added to the end of the file. If the file already exists, its content is kept.
  • clear: the file is emptied when the first result is exported, and the following results are added to it.

For Excel files, each result is written to a new sheet. With true, the sheets are added to the existing workbook.

To export each result to its own file, use the ${dbvis-object}$ variable in the file name. It is replaced with the name of the table in the query, or the first table if the query uses several. The file extension isn't added automatically, so include it in the file name:

@export set Filename="/tmp/${dbvis-object}$.csv";

You can also split a large result into several files with CsvSplitFileSize, JsonSplitFileSize, SqlSplitFileSize or TxtSplitFileSize. The files are named name_1.ext, name_2.ext, and so on.

Parameters for @export set

The parameters are written as Name="value". The names and the values in the lists are case-insensitive, unless stated otherwise. A relative Filename is relative to the script location, or to the folder set with @cd.

Many defaults come from Tools → Tool Properties, so that the export uses the same formats as the grids.

Parameters for all formats

ParameterDefaultDescription
FilenameRequired when Destination is File. The file to export to. Missing folders are created. You can use DbVisualizer variables, such as ${dbvis-object}$ and ${dbvis-date}$.
FormatFrom the file extensionCSV, Excel, JSON, HTML, XML, SQL or TXT. See Choosing the format.
AppendFilefalsefalse, true or clear. See One file or several files.
DestinationFileFile, Clipboard or SQL Commander. With Clipboard and SQL Commander, the output is collected in memory and delivered when the export ends. Excel requires File.
EncodingAs set in Tool PropertiesThe character encoding of the file, for example UTF-8. The default is the Default File Encoding setting in Tools → Tool Properties, in the General / Files category. Use the names in the Canonical Name for java.nio API column of the supported encodings.
SettingsA settings file saved in the export dialogs. See Using saved settings.
ShowNullAsAs set in Tool PropertiesThe text for NULL values. SQL and JSON always use null.
DateFormatAs set in Tool PropertiesThe format of date values. See Changing Date and Number Formats.
TimeFormatAs set in Tool PropertiesThe format of time values.
TimeStampFormatAs set in Tool PropertiesThe format of timestamp values.
NumberFormatAs set in Tool PropertiesThe format of integer values, for example #,###. Not used for SQL.
DecimalNumberFormatAs set in Tool PropertiesThe format of decimal values. Not used for SQL.
DecimalNumberSeparatorAs set in Tool PropertiesThe decimal separator. Only used when NumberFormat or DecimalNumberFormat is set. If blank, the separator of your locale is used.
NumberGroupingSeparatorAs set in Tool PropertiesThe thousands separator, or Space for a space. Only used when NumberFormat or DecimalNumberFormat is set. For example, with NumberFormat="#,###" and NumberGroupingSeparator=".", 2000 is exported as 2.000.
BooleanTrueFormatAs set in Tool PropertiesThe text for true values, for example yes or 1.
BooleanFalseFormatAs set in Tool PropertiesThe text for false values, for example no or 0.
QuoteTextDataNoneThe quote character for text values: None, Single or Double. Not used for JSON.
QuoteAllValuesfalseIf true, all values are quoted, not only text values.
QuoteDuplicateEmbeddedtrueIf true, quote characters inside values are doubled.
TextFunctionNoneNone, Trim, Left Trim or Right Trim. Removes spaces from all text values. To trim only some columns, use TRIM() in the query instead.
BinaryFormatDon't ExportHow binary values are exported: Don't Export, Size, Value, Hex, Base64 or File.
BinaryFileDirThe system temporary folderThe folder for the files when BinaryFormat is File. See Exporting a Table. Variables for column names must include the scope option, for example /tmp/${dbvis-date}$/${COUNTRIES||||||scope=post}$.
CLOBFormatDon't ExportHow CLOB values are exported: Don't Export, Size, Value or File.
CLOBFileDirThe system temporary folderThe folder for the files when CLOBFormat is File. See BinaryFileDir.

CSV parameters

ParameterDefaultDescription
CsvColumnDelimiterAs set in Tool PropertiesThe column delimiter. The default is a tab, as set in Copy Grid Cells in CSV Format in Tool Properties. You can also use a Unicode code point, such as ♖.
CsvRowDelimiterAs set in Tool PropertiesThe row delimiter, for example \n or \r\n. The default is \n.
CsvIncludeColumnHeadertrueIf true, a row with the column names is included.
CsvIncludeColumnHeaderPerResultfalseIf true and several results are exported to the same file, each result gets its own header row.
CsvColumnHeaderIsColumnAliasAs set in Tool PropertiesIf true, the column aliases in the query are used as column names.
CsvIncludeSQLCommandDon't IncludeWhere to include the query: Don't Include, Top or Bottom.
CsvRowCommentIdentifierThe text to put before each line of the included query, for example #.
CsvRemoveNewlinesfalseIf true, line breaks in values are removed.
CsvSplitFileSize-1Splits the result into several files of at most this size, for example 10M. Use a number of bytes or a number followed by K, M or G. -1 means no split.

TXT parameters

The TXT format writes the values in fixed-width columns.

ParameterDefaultDescription
TxtColumnDelimiterA spaceThe text between columns. You can also use a Unicode code point, such as ♖.
TxtRowDelimiterAs set in Tool PropertiesThe row delimiter, for example \n or \r\n.
TxtAlignNumbersLeftLeft or Right.
TxtIncludeColumnHeadertrueIf true, a row with the column names is included.
TxtIncludeColumnHeaderPerResultfalseIf true and several results are exported to the same file, each result gets its own header.
TxtIncludeSQLCommandDon't IncludeWhere to include the query: Don't Include, Top or Bottom.
TxtRemoveNewlinesfalseIf true, line breaks in values are removed.
TxtTruncateLength-1The maximum length of a value. Longer values are cut and end with .... -1 means no limit.
TxtSplitFileSize-1Splits the result into several files. See CsvSplitFileSize.

Excel parameters

ParameterDefaultDescription
ExcelFileFormatFrom the file extensionXLSX or XLS. If the file name has no .xls or .xlsx extension, XLSX is used. XLS files can have at most 65,535 rows and 256 columns.
ExcelSheetNameThe name of the sheet. You can use ${dbvis-object}$. If not set, the table name is used. Names are shortened to 31 characters and made unique.
ExcelTitleA title at the top of the sheet.
ExcelIntroTextA description at the top of the sheet.
ExcelIncludeColumnHeadertrueIf true, a row with the column names is included.
ExcelColumnHeaderIsColumnAliasAs set in Tool PropertiesIf true, the column aliases in the query are used as column names.
ExcelIncludeSQLCommandNoneWhere to include the query: None, Top, Bottom, or Sheet for a separate sheet.
ExcelAutoResizeColumnsfalseIf true, the column widths are adjusted to the values.
ExcelTextDateTimetrueIf true, date, time and timestamp values are exported as text.
ExcelTextOnlyfalseIf true, numbers are exported as text. If false, a ShowNullAs value that is a number, such as 0, is also exported as a number in numeric columns.

JSON parameters

ParameterDefaultDescription
JsonStyleArrayArray for one array with all rows, or Rows for one object per line.
JsonColumnHeaderIsColumnAliasAs set in Tool PropertiesIf true, the column aliases in the query are used as property names.
JsonSplitFileSize-1Splits the result into several files. See CsvSplitFileSize.

HTML parameters

ParameterDefaultDescription
HtmlTitleThe title of the HTML page.
HtmlIntroTextA description at the top of the page.
HtmlPerTableHeaderRows with the date, columns and tableHTML rows shown above each table. They must start with <tr> and end with </tr>. You can use DbVisualizer variables, such as ${dbvis-object}$ for the table name.
HtmlFooterGenerated by: DbVisualizer and its versionThe text at the bottom of the page. Set it to an empty value to remove the footer.
HtmlIncludeSQLCommandfalseIf true, the query is included.
HtmlColumnHeaderIsColumnAliasAs set in Tool PropertiesIf true, the column aliases in the query are used as column names.
HtmlConvertCharstrueIf true, characters such as < and > are converted to &lt; and &gt;. Set it to false if the data contains HTML code. It also applies to XML.

XML parameters

ParameterDefaultDescription
XmlStyleDbVisualizerDbVisualizer, XmlDataSet or FlatXmlDataSet.
XmlIntroTextA description of the XML document.
XmlIncludeSQLCommandfalseIf true, the query is included.
XmlColumnHeaderIsColumnAliasAs set in Tool PropertiesIf true, the column aliases in the query are used as column names.

SQL parameters

The SQL format writes INSERT statements, and optionally CREATE TABLE or MERGE statements.

ParameterDefaultDescription
TableNameThe table name to use in the statements, if DbVisualizer can't find it in the query.
SqlIncludeCreateDDLfalseIf true, a CREATE TABLE statement is included.
SqlIncludeSQLCommandDon't IncludeWhere to include the query: Don't Include, Top or Bottom.
SqlIncludeAutoGeneratedValuestrueIf false, auto-increment, identity, generated and computed columns are left out of the INSERT statements.
SqlQualifyObjectNametrueIf true, table names are qualified with the schema, or with SqlQualifier.
SqlQualifierThe qualifier for table names. If not set, DbVisualizer uses the schema.
SqlQualifyColumnNamefalseIf true, column names are qualified with the table name.
SqlDelimitedIdentifierstrueIf true, names are enclosed in SqlBeginIdentifier and SqlEndIdentifier.
SqlBeginIdentifierThe character before a delimited name, for example """" for ". A double quote must be doubled, because the value is also enclosed in double quotes.
SqlEndIdentifierThe character after a delimited name.
SqlSeparatorAs set in Tool PropertiesThe statement delimiter, normally ;.
SqlRowCommentIdentifierAs set in Tool PropertiesThe comment prefix for the included query, normally --.
SqlBlockBeginDelim--/The text before a statement block, used for complex statements exported with @ddl.
SqlBlockEndDelim/The text after a statement block.
SqlBeforeExportStmtsStatements to include at the start of the file, for example set foreign_key_checks = 0;.
SqlAfterExportStmtsStatements to include at the end of the file, for example set foreign_key_checks = 1;.
SqlMultiRowInsertfalseIf true, each INSERT statement inserts several rows. See Multi-row INSERT and MERGE syntax.
SqlMultiRowInsertLimit10The maximum number of rows in each INSERT statement.
SqlMultiRowInsertTemplateDepends on the databasemulti-insert-sql92, multi-insert-oracle, multi-insert-union or multi-insert-cassandra. See Multi-row INSERT and MERGE syntax.
SqlMergeStatementfalseIf true, MERGE statements are written instead of INSERT statements. You can't use both SqlMergeStatement and SqlMultiRowInsert.
SqlMergeStatementMatchColumnsA comma-separated list of the columns that identify a row. If not set, the primary key columns are used.
SqlMergeTemplateDepends on the databasesingle-merge-sql92 or single-merge-oracle. See Multi-row INSERT and MERGE syntax.
SqlGenCreateWithStoragefalseIf true, storage options are included in the CREATE TABLE statement. Only for Db2 and Cassandra.
SqlGenerateSqlModefalseIf true, the SQL mode is included. Only for MariaDB and MySQL.
SqlSplitFileSize-1Splits the result into several files. See CsvSplitFileSize.

Multi-row INSERT and MERGE syntax

Databases use different syntax for INSERT statements with several rows and for MERGE statements. DbVisualizer has a template for each syntax, and by default it uses the one that fits the database you export from. If you are going to run the exported statements in another type of database, set SqlMultiRowInsertTemplate or SqlMergeTemplate to the template for that database.

The examples below show the statements that each template generates for a table TAB with two rows.

With SqlMultiRowInsert="true", each INSERT statement inserts several rows. SqlMultiRowInsertTemplate sets the syntax:

multi-insert-sql92

INSERT INTO TAB (ID, FI, LA) VALUES
    (1, 'abc', 'def'),
    (2, 'abc', 'def');

multi-insert-oracle

INSERT ALL
INTO TAB (ID, FI, LA) VALUES (1, 'abc', 'def')
INTO TAB (ID, FI, LA) VALUES (2, 'abc', 'def')
SELECT 1 FROM dual;

multi-insert-union

INSERT INTO TAB (ID, FI, LA)
SELECT 1, 'abc', 'def' UNION ALL
SELECT 2, 'abc', 'def';

multi-insert-cassandra

--/
BEGIN BATCH
INSERT INTO TAB (ID, FI, LA) VALUES (1, 'abc', 'def');
INSERT INTO TAB (ID, FI, LA) VALUES (2, 'abc', 'def');
APPLY BATCH;
/

With SqlMergeStatement="true", a MERGE statement is written for each row. It inserts the row if it doesn't exist, and updates it otherwise. SqlMergeTemplate sets the syntax:

single-merge-sql92

MERGE INTO TAB A USING
(SELECT 1 AS ID, 'abc' AS FI, 'def' AS LA) B
ON (A.ID = B.ID)
WHEN NOT MATCHED THEN
INSERT (ID, FI, LA) VALUES (B.ID, B.FI, B.LA)
WHEN MATCHED THEN
UPDATE SET FI = B.FI, LA = B.LA;

single-merge-oracle is the same, but with FROM dual in the SELECT.

Examples

Exporting each table to its own file

@export on;
@export set Filename="/tmp/backup/${dbvis-object}$.sql" Format="SQL";
SELECT * FROM actor;
SELECT * FROM film;
SELECT * FROM customer;
@export off;

The results are exported to actor.sql, film.sql and customer.sql.

Exporting all results to one file

@export on;
@export set Filename="/tmp/backup/alltables.sql" Format="SQL" AppendFile="clear";
SELECT * FROM actor;
SELECT * FROM film;
SELECT * FROM customer;
@export off;

AppendFile="clear" empties the file at the start, and adds each result to it. Without AppendFile, the file would only contain the last result.

Exporting to an Excel file with one sheet per table

@export on;
@export set Filename="/tmp/backup/tables.xlsx" ExcelSheetName="${dbvis-object}$" AppendFile="clear";
SELECT * FROM actor;
SELECT * FROM film;
@export off;

Exporting all rows

In SQL Commander, the number of exported rows is limited by the Max Rows field. To export all rows, set Max Rows to -1 with @set maxrows before the export:

@set maxrows -1;
@export on;
@export set Filename="/tmp/backup/film.csv";
SELECT * FROM film;
@export off;

You can also use @set maxrows to export only the first rows, for example @set maxrows 10;. When you run a script from the command-line interface, all rows are exported by default. See also Setting Max Rows and Max Chars.

Using saved settings

When you export a table, a schema or a grid, you can save the settings to a file. To use them in a script, set Settings to the file. Parameters that you set in the same command override the settings in the file:

@export on;
@export set Settings="/tmp/htmlsettings.xml" Filename="/tmp/backup/${dbvis-object}$.html";
SELECT * FROM actor;
SELECT * FROM film;
@export off;

Other ways to export data