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:
@export onstarts the export. It has no parameters.- One or more
@export setcommands set the file and the format. - The results of all queries that follow are exported, including the results of
@call,@descand@ddl. Statements that don't return a result, such as UPDATE, are run as usual. @export offends 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:
| Extension | Format |
|---|---|
.csv | CSV |
.xlsx, .xls | Excel |
.json | JSON |
.html | HTML |
.xml | XML |
.sql | SQL |
| Any other | CSV |
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
| Parameter | Default | Description |
|---|---|---|
| Filename | Required 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}$. | |
| Format | From the file extension | CSV, Excel, JSON, HTML, XML, SQL or TXT. See Choosing the format. |
| AppendFile | false | false, true or clear. See One file or several files. |
| Destination | File | File, Clipboard or SQL Commander. With Clipboard and SQL Commander, the output is collected in memory and delivered when the export ends. Excel requires File. |
| Encoding | As set in Tool Properties | The 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. |
| Settings | A settings file saved in the export dialogs. See Using saved settings. | |
| ShowNullAs | As set in Tool Properties | The text for NULL values. SQL and JSON always use null. |
| DateFormat | As set in Tool Properties | The format of date values. See Changing Date and Number Formats. |
| TimeFormat | As set in Tool Properties | The format of time values. |
| TimeStampFormat | As set in Tool Properties | The format of timestamp values. |
| NumberFormat | As set in Tool Properties | The format of integer values, for example #,###. Not used for SQL. |
| DecimalNumberFormat | As set in Tool Properties | The format of decimal values. Not used for SQL. |
| DecimalNumberSeparator | As set in Tool Properties | The decimal separator. Only used when NumberFormat or DecimalNumberFormat is set. If blank, the separator of your locale is used. |
| NumberGroupingSeparator | As set in Tool Properties | The 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. |
| BooleanTrueFormat | As set in Tool Properties | The text for true values, for example yes or 1. |
| BooleanFalseFormat | As set in Tool Properties | The text for false values, for example no or 0. |
| QuoteTextData | None | The quote character for text values: None, Single or Double. Not used for JSON. |
| QuoteAllValues | false | If true, all values are quoted, not only text values. |
| QuoteDuplicateEmbedded | true | If true, quote characters inside values are doubled. |
| TextFunction | None | None, Trim, Left Trim or Right Trim. Removes spaces from all text values. To trim only some columns, use TRIM() in the query instead. |
| BinaryFormat | Don't Export | How binary values are exported: Don't Export, Size, Value, Hex, Base64 or File. |
| BinaryFileDir | The system temporary folder | The 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}$. |
| CLOBFormat | Don't Export | How CLOB values are exported: Don't Export, Size, Value or File. |
| CLOBFileDir | The system temporary folder | The folder for the files when CLOBFormat is File. See BinaryFileDir. |
CSV parameters
| Parameter | Default | Description |
|---|---|---|
| CsvColumnDelimiter | As set in Tool Properties | The 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 ♖. |
| CsvRowDelimiter | As set in Tool Properties | The row delimiter, for example \n or \r\n. The default is \n. |
| CsvIncludeColumnHeader | true | If true, a row with the column names is included. |
| CsvIncludeColumnHeaderPerResult | false | If true and several results are exported to the same file, each result gets its own header row. |
| CsvColumnHeaderIsColumnAlias | As set in Tool Properties | If true, the column aliases in the query are used as column names. |
| CsvIncludeSQLCommand | Don't Include | Where to include the query: Don't Include, Top or Bottom. |
| CsvRowCommentIdentifier | The text to put before each line of the included query, for example #. | |
| CsvRemoveNewlines | false | If true, line breaks in values are removed. |
| CsvSplitFileSize | -1 | Splits 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.
| Parameter | Default | Description |
|---|---|---|
| TxtColumnDelimiter | A space | The text between columns. You can also use a Unicode code point, such as ♖. |
| TxtRowDelimiter | As set in Tool Properties | The row delimiter, for example \n or \r\n. |
| TxtAlignNumbers | Left | Left or Right. |
| TxtIncludeColumnHeader | true | If true, a row with the column names is included. |
| TxtIncludeColumnHeaderPerResult | false | If true and several results are exported to the same file, each result gets its own header. |
| TxtIncludeSQLCommand | Don't Include | Where to include the query: Don't Include, Top or Bottom. |
| TxtRemoveNewlines | false | If true, line breaks in values are removed. |
| TxtTruncateLength | -1 | The maximum length of a value. Longer values are cut and end with .... -1 means no limit. |
| TxtSplitFileSize | -1 | Splits the result into several files. See CsvSplitFileSize. |
Excel parameters
| Parameter | Default | Description |
|---|---|---|
| ExcelFileFormat | From the file extension | XLSX 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. |
| ExcelSheetName | The 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. | |
| ExcelTitle | A title at the top of the sheet. | |
| ExcelIntroText | A description at the top of the sheet. | |
| ExcelIncludeColumnHeader | true | If true, a row with the column names is included. |
| ExcelColumnHeaderIsColumnAlias | As set in Tool Properties | If true, the column aliases in the query are used as column names. |
| ExcelIncludeSQLCommand | None | Where to include the query: None, Top, Bottom, or Sheet for a separate sheet. |
| ExcelAutoResizeColumns | false | If true, the column widths are adjusted to the values. |
| ExcelTextDateTime | true | If true, date, time and timestamp values are exported as text. |
| ExcelTextOnly | false | If 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
| Parameter | Default | Description |
|---|---|---|
| JsonStyle | Array | Array for one array with all rows, or Rows for one object per line. |
| JsonColumnHeaderIsColumnAlias | As set in Tool Properties | If true, the column aliases in the query are used as property names. |
| JsonSplitFileSize | -1 | Splits the result into several files. See CsvSplitFileSize. |
HTML parameters
| Parameter | Default | Description |
|---|---|---|
| HtmlTitle | The title of the HTML page. | |
| HtmlIntroText | A description at the top of the page. | |
| HtmlPerTableHeader | Rows with the date, columns and table | HTML 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. |
| HtmlFooter | Generated by: DbVisualizer and its version | The text at the bottom of the page. Set it to an empty value to remove the footer. |
| HtmlIncludeSQLCommand | false | If true, the query is included. |
| HtmlColumnHeaderIsColumnAlias | As set in Tool Properties | If true, the column aliases in the query are used as column names. |
| HtmlConvertChars | true | If true, characters such as < and > are converted to < and >. Set it to false if the data contains HTML code. It also applies to XML. |
XML parameters
| Parameter | Default | Description |
|---|---|---|
| XmlStyle | DbVisualizer | DbVisualizer, XmlDataSet or FlatXmlDataSet. |
| XmlIntroText | A description of the XML document. | |
| XmlIncludeSQLCommand | false | If true, the query is included. |
| XmlColumnHeaderIsColumnAlias | As set in Tool Properties | If 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.
| Parameter | Default | Description |
|---|---|---|
| TableName | The table name to use in the statements, if DbVisualizer can't find it in the query. | |
| SqlIncludeCreateDDL | false | If true, a CREATE TABLE statement is included. |
| SqlIncludeSQLCommand | Don't Include | Where to include the query: Don't Include, Top or Bottom. |
| SqlIncludeAutoGeneratedValues | true | If false, auto-increment, identity, generated and computed columns are left out of the INSERT statements. |
| SqlQualifyObjectName | true | If true, table names are qualified with the schema, or with SqlQualifier. |
| SqlQualifier | The qualifier for table names. If not set, DbVisualizer uses the schema. | |
| SqlQualifyColumnName | false | If true, column names are qualified with the table name. |
| SqlDelimitedIdentifiers | true | If true, names are enclosed in SqlBeginIdentifier and SqlEndIdentifier. |
| SqlBeginIdentifier | The character before a delimited name, for example """" for ". A double quote must be doubled, because the value is also enclosed in double quotes. | |
| SqlEndIdentifier | The character after a delimited name. | |
| SqlSeparator | As set in Tool Properties | The statement delimiter, normally ;. |
| SqlRowCommentIdentifier | As set in Tool Properties | The 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. |
| SqlBeforeExportStmts | Statements to include at the start of the file, for example set foreign_key_checks = 0;. | |
| SqlAfterExportStmts | Statements to include at the end of the file, for example set foreign_key_checks = 1;. | |
| SqlMultiRowInsert | false | If true, each INSERT statement inserts several rows. See Multi-row INSERT and MERGE syntax. |
| SqlMultiRowInsertLimit | 10 | The maximum number of rows in each INSERT statement. |
| SqlMultiRowInsertTemplate | Depends on the database | multi-insert-sql92, multi-insert-oracle, multi-insert-union or multi-insert-cassandra. See Multi-row INSERT and MERGE syntax. |
| SqlMergeStatement | false | If true, MERGE statements are written instead of INSERT statements. You can't use both SqlMergeStatement and SqlMultiRowInsert. |
| SqlMergeStatementMatchColumns | A comma-separated list of the columns that identify a row. If not set, the primary key columns are used. | |
| SqlMergeTemplate | Depends on the database | single-merge-sql92 or single-merge-oracle. See Multi-row INSERT and MERGE syntax. |
| SqlGenCreateWithStorage | false | If true, storage options are included in the CREATE TABLE statement. Only for Db2 and Cassandra. |
| SqlGenerateSqlMode | false | If true, the SQL mode is included. Only for MariaDB and MySQL. |
| SqlSplitFileSize | -1 | Splits 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
- Export all or selected tables with the Export Schema or Export Table assistant.
- Export the results of a query by exporting the grid.