Skip to content
The latest version of DbVisualizer was released 2024-07-04DOWNLOAD HERE ->

@export - Export query result

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

Instead of viewing and exporting query results from Result Set grids, you can export the result of one or more queries to a file from a script. For very large results, this may be the preferred choice due to memory constraints.

To export a query result, create a script with

  1. an @export on command,
  2. an @export set command,
  3. one or more queries,
  4. an @export off command.

Here is a basic example:

@export on;
@export set filename="c:\Backups\Orders.csv";
select * from Orders;
@export off;

The @export set command takes a parameter name followed by an equal sign and a value. You can use the following parameters, where only filename is required and all names are case-insensitive:

ParameterDefaultValid Values
AppendFilefalsetrue, false, clear (i.e. start with a new file for the first result and then append to it) Note that when exporting to Excel and appending to an existing excel file any manually added formatting in the old sheets will be lost.
BinaryFileDirPath for data files when BinaryFormat is set to File, see Exporting a Table for details. Note! Variables for column names must include the scope option when entered manually into a script, e.g. /Users/hans/exp/${dbvis-date}$/${COUNTRIES||||||scope=post}$
BinaryFormatDon't ExportDon't Export, Size, Value, Hex, Base64, File
BooleanFalseFormatfalsefalse, no, 0, off (you can specify custom entries separated by comma)
BooleanTrueFormattruetrue, yes, 1, on (you can specify custom entries separated by comma)
CLOBFileDirPath for data files when CLOBFormat is set to File, see Exporting a Table for details. Note! Variables for column names must include the scope option when entered manually into a script, e.g. /Users/hans/exp/${dbvis-date}$/${COUNTRIES||||||scope=post}$
CLOBFormatDon't ExportDon't Export, Size, Value, File
CsvColumnDelimiter\t (TAB)The delimiter between columns in a CSV output. In addition to literals it can also be specified using Unicode Code Points as \u2656.
CsvIncludeColumnHeadertruetrue, false
CsvIncludeColumnHeaderPerResultfalsetrue, false (adds separate column headers for each query e.g. when you have several selects with different structure of the result sets)
CsvColumnHeaderIsColumnAliastruetrue, false
CsvIncludeSQLCommandDon't IncludeDon't Include, Top, Bottom
CsvSplitFileSize-1Split the result over multiple files if it is larger than the specified size, or -1 to never split. The size must be specified as size [ g | G | m | M | k | K ]
CsvRemoveNewlinesfalsetrue, false
CsvRowCommentIdentifier
CsvRowDelimiter\n\n (UNIX/Linux/macOS), \r\n (Windows) (you may set this to any literal)
DateFormatyyyy-MM-ddSee valid formats in Changing the Data Display Format document
DecimalNumberFormatUnformattedSee valid formats in Changing the Data Display Format document
DecimalNumberSeparator.The Decimal separator character to use. Note: this setting have no effect when DecimalNumberFormat or NumberFormat is Unformatted.
DestinationFileFile, Clipboard, SQL Commander
EncodingUTF-8Check supported encodings for all encodings. (Use the encoding in the Canonical Name for java.nioAPI column).
ExcelAutoResizeColumnsfalsetrue, false
ExcelColumnHeaderIsColumnAliastruetrue, false
ExcelFileFormatIf ExcelFileFormat is not specified, the file extension is used to set this parameter to one of the below - xls is for binary (or legacy) Excel max 65 535 rows - xlsx is the current and recommended OOXML format
ExcelIncludeColumnHeadertruetrue, false
ExcelIncludeSQLCommandfalsetrue, false
ExcelIntroTextAny description
ExcelSheetNameUsed when exporting to excel. Sets the name of exported excel sheet.
ExcelTextOnlyfalsetrue, false. Convert numeric values to text in the Excel file if true.
ExcelTextDateTimetruetrue, false. Convert date, time and timestamp data to text in the Excel file if true.
ExcelTitleAny title
FilenameThe output file name for exported file. This parameter is required if Destination="file". If setting a relative filename the output path depends on the current working folder set by any @cd command.
FormatBased on file extension, or CSV if noneCSV, HTML, XML, SQL, TXT, Excel, JSON. If Format is not specified, the file extension is used to determine the format. If there is no recognized file extension, CSV is used as the default.
HtmlColumnHeaderIsColumnAliastruetrue, false
HtmlConvertCharstruetrue, false. Set to false if you have HTML code in the exported data, so that e.g. < and > characters are not converted to &lt; and &gt;
HtmlFooter[ Generated by: DbVisualizer version ]Any text to use in the document footer. Can be set to blank to remove the footer.
HtmlIncludeSQLCommandfalsetrue, false
HtmlIntroTextAny description
HtmlPerTableHeaderE.g. | Date: | 2017-05-31 16:48:23 | | Columns: | 4 | | Table: | COUNTRIES |HTML code that describes the table. To fit into the rest of the HTML code, it must start with <tr> and end with </tr>. The pre-defined DbVisualizer variables can be used, e.g. ${dbvis-object}$ to include the table name.
------
HtmlTitleAny title
JSONColumnHeaderIsColumnAliastruetrue, false
JsonSplitFileSize-1Split the result over multiple files if it is larger than the specified size, or -1 to never split. The size must be specified as size [ g | G | m | M | k | K ]
JSONStyleArrayArray, Rows
NumberFormatUnformattedSee valid formats in Changing the Data Display Format document.
NumberGroupingSeparator,The number grouping separator to use. Example using NumberFormat=“#,###” and NumberGroupingSeparator="." the formatting of 2000 will produce the result 2.000. If using NumberGroupingSeparator="space" the result will be 2 000. Note: this setting have no effect when DecimalNumberFormat or NumberFormat is Unformatted.
QuoteAllValuesfalsetrue, false
QuoteDuplicateEmbeddedtruetrue, false (quote char is the same as QuoteTextData)
QuoteTextDataNoneNone, Single, Double
SettingsThe path to an XML file containing all settings
ShowNullAs(null)
SqlAfterExportStmtsAny statements to include in the script after the SQL statements for the exported objects, e.g. set foreign_key_checks = 1;
SqlBeforeExportStmtsAny statements to include in the script before the SQL statements for the exported objects, e.g. set foreign_key_checks = 0;
SqlBeginIdentifierCharacter to use to begin a quoted identifier. Note! To specify a double-quote, you must duplicate it since double-quote is also used to enclose the parameter value.
SqlBlockBeginDelimString to use to begin an SQL block when exporting complex DDL statements using the @ddl command.
SqlBlockEndDelimString to use to end an SQL block
SqlDelimitedIdentifierstruetrue, false
SqlEndIdentifierCharacter to use to end a quoted identifier. Note! To specify a double-quote, you must duplicate it since double-quote is also used to enclose the parameter value.
SqlGroupByObjectObject, Statement. Set to Object to generate DROP, CREATE, INSERT, and ALTER statements (where applicable) for each exported object in turn. Set to Statement to group all statements of the same type together, e.g. first DROP statements for all exported objects, then CREATE statements for all exported objects, etc.
SqlIncludeAutoGeneratedValuestruetrue, false. Set to false to exclude columns declared as AUTO_INCREMENT or IDENTITY in the INSERT statements.
SqlIncludeCreateDDLfalsetrue, false
SqlIncludeSQLCommandDon't IncludeDon't Include, Top, Bottom
SqlMultiRowInsertfalsetrue, false. If true Multiple rows are included in each INSERT statement. The number of rows to include in each INSERT statement is limited by the SqlMultiRowInsertLimit parameter.
SqlMultiRowInsertLimit10The maximum number of rows to include in a multirow INSERT.
SqlQualifierQualifier to use when qualifying table names. If not set, DbVisualizer tries to determine the schema and use it as the qualifier.
SqlQualifyColumnNamefalsetrue, false
SqlQualifyObjectNametruetrue, false
SqlRowCommentIdentifier--
SqlSeparator;Statement separator character.
SqlSplitFileSize-1Split the result over multiple files if it is larger than the specified size, or -1 to never split. The size must be specified as size [ g | G | m | M | k | K ]
TxtColumnDelimiter\t (TAB)The delimiter between columns in a CSV output. In addition to literals it can also be specified using Unicode Code Points as \u2656.
TxtIncludeColumnHeadertruetrue, false
TxtIncludeColumnHeaderPerResultfalsetrue, false
TxtColumnHeaderIsColumnAliastruetrue, false
TxtIncludeSQLCommandDon't IncludeDon't Include, Top, Bottom
TxtSplitFileSize-1Split the result over multiple files if it is larger than the specified size, or -1 to never split. The size must be specified assize [ g | G | m | M | k | K ]
TxtRemoveNewlinesfalsetrue, false Removes newlines when formatting text. This includes when displaying binary values as text.
TxtRowDelimiter\n\n (UNIX/Linux/macOS), \r\n (Windows) (you may set this to any literal)
TxtTruncateLength-1Specifies the length at which truncation of a column will happen.
TxtAlignNumbersleftleft, right
TableNameCan be set if DbVisualizer cannot determine the value for the ${dbvis-object}$ variable
TimeFormatHH:mm:ssSee valid formats in Changing the Data Display Format document
TimeStampFormatyyyy-MM-dd HH:mm:ss.SSSSSSSee valid formats in Changing the Data Display Format document
XmlColumnHeaderIsColumnAliastruetrue, false
XmlIncludeSQLCommandfalsetrue, false
XmlIntroTextAny description
XmlStyleDbVisualizerDbVisualizer, XmlDataSet, FlatXmlDataSet

Examples

Here are a few examples using some of these settings.

Automatic table name to file mapping

This example shows how to make the filename the same as the table name in the select statement. The example also shows several select statements. Each will be exported in the SQL format. Since the filename is defined to be automatically set, this means that there will be one file per result set and each file is named by the name of its table.

@export on;
@export set filename="c:\Backups\${dbvis-object}$" format="sql";
select * from Orders;
select * from Products;
select * from Transactions;
@export off;

There must be only one table name in a select statement in order to automatically set the filename with the ${dbvis-object}$ variable, i.e if the select joins from several tables or pseudo tables are used, you must explicitly name the file.

The ${dbvis-object}$ variable is not substituted with a table name if using the AppendFile="true/clear" parameter.

Multiple results to a single file

This example shows how all result sets can be exported to a single file. The AppendFile parameter supports the following values.

  • true The following result sets will all be exported to a single file
  • false Turn off the append processing
  • clear Same as the true value but this will in addition clear the file before the first result set is exported
@export on;
@export set filename="c:\Backups\alltables.sql" appendfile="clear" format="sql";
select * from Orders;
select * from Products;
select * from Transactions;
@export off;

Using predefined settings

If you save settings when exporting a table or a schema, you can use the Settings parameter to reference the settings file.

@export on;
@export set settings="c:\tmp\htmlsettings.xml" filename="c:\Backups\${dbvis-object}$";
select * from Orders;
select * from Products;
select * from Transactions;
@export off;

Limit the number of exported rows

You can use the @set maxrows command in combination with the @export command to override the Max Rows field value in the SQL Commander tab toolbar.

@set maxrows 10;
@export on;
@export set filename="c:\Backups\alltables.sql" format="sql";
select * from Orders;
select * from Products;
select * from Transactions;
@export off;

If Max Rows is set to a positive number, you can use the @set maxrows command to set it to -1 to export all rows.

Don't Export

Other Ways to Export Table Data