DBeaver Documentation

DOWNLOAD pdf

Export Command

Overview

The @export command allows you to open the data transfer wizard with prefilled settings.

It may be helpful in case you're editing several SQL queries and want to quickly perform the export of the produced results without creating any additional data transfer tasks.

Disclaimer: This article describes supported settings by the @export command, their purpose, and allowed values. Generally, this article contains every setting accessible in the data transfer wizard. Settings are written in the order they appear in the wizard, so you can always look at the wizard to quickly locate any of these settings.

Usage

The body of the command consists of JSON text, which looks like this:

{
    "type": <ID of the processor>,
    "producer": {
        <producer settings>
    },
    "consumer": {
        <consumer settings>
    },
    "processor": {
        <processor-specific settings>
    },
}

Due to certain limitations, it must be written on a single line, without line delimiters:

@export { "type": "csv", "producer": { ... }, "consumer": { ... }, "processor": { ... } }

The command itself doesn't do anything. It must be followed by any other query:

@export { "type": "csv", "producer": { ... }, "consumer": { ... }, "processor": { ... } }

SELECT * FROM Album;

You can either execute each line separately, or execute the entire script at once.

Settings

Here's the description of each attribute:

Attribute Description
type Type of the processor.
producer Settings that affect how the data is extracted.
See the full table of supported settings in the main section.
consumer Settings that affect how the data is transformed before processing.
See the full table of supported settings in the main section.
processor Settings that affect how the data is processed. This includes formatting, transformations, etc.
These settings are specific to the processor specified by the type attribute.
See the full table of supported processors in the main section.

Producer Settings

Id Name Description Type Default Value Allowed Values
extractType Extract type Data extraction mode. Denotes whether a single query or multiple segmented queries should be used to extract data. String SINGLE_QUERY SINGLE_QUERY, SEGMENTS
segmentSize Segment size Specifies how many rows are read per segment during data extraction.
See extractType
Integer 100000 Any
fetchSize Fetch size Number of rows to fetch per one server round trip.
May greatly affect extraction performance.
Integer 10000 Any
openNewConnections Open new connection(s) Open new physical connection for data reading.
Makes great sense if you are going to continue to work with your database during the export process.
Boolean true Any
queryRowCount Select row count Query row count before performing export.
This will let you track export progress but may cause performance faults in some cases.
Boolean true Any

Consumer Settings

Id Name Description Type Default Value Allowed Values
formatterProfile Formatting Profile Specifies the profile used for formatting data. String Any
valueFormat Value Formatting Specifies how the data is interpreted. String UI UI, EDIT, NATIVE
lobExtractType Binaries Policy Specifies how binaries are processed. String INLINE SKIP, FILES, INLINE
lobEncoding Binaries Encoding Specifies how binaries are encoded. String BINARY BASE64, HEX, BINARY, NATIVE
outputClipboard Copy to Clipboard Specifies that the data should be copied to the clipboard rather written to files on a disk. Boolean false Any
outputFolder Output Directory Output directory pattern. Specifies there the output files should be located.
String N/A Any
outputFilePattern Output Filename Output filename pattern. String ${table}_${timestamp} Any
outputEncoding Output Encoding Specifies the file encoding. String UTF-8 Any
outputEncodingBOM Insert BOM Specifies whether the byte order mark should be written to the output file.
Common for encoding such as UTF-16LE, UTF-16BE, UTF-32LE, and UTF-32LE.
Boolean false Any
outputTimestampPattern Timestamp Pattern Pattern used for the ${timestamp} variable in outputFolder and outputFilePattern. String yyyyMMddHHmm Any
appendToFile Append to the end of the file If file already exists, appends data at end of it.
Only works against compatible processors.
Boolean false Any
useSingleFile Write to the single file Write all streams to the single file.
Only works against compatible processors.
Boolean false Any
compressResults Compress Specifies whether the output file should be compressed using ZIP. Boolean false Any
splitOutFiles Split output file Specifies whether the output file should be split using the maxOutFileSize threshold. If size exceeds this threshold, a separate file is created and so on. Boolean false Any
maxOutFileSize Maximum file size Maximum size of a single file.
See splitOutFiles
Integer 10000000 Any

Processor Settings

In this section, we will explore the variety of processor settings available in DBeaver. Navigate through the links provided in the Options column for detailed instructions on using each processor type.

Id Name Description Options
csv CSV Export to CSV file(s) Navigate to CSV
dbunit DbUnit Export to DbUnit XML file(s) Navigate to DbUnit
html HTML Export to HTML file(s) Navigate to HTML
json JSON Export to JSON file(s) Navigate to JSON
markdown.table Markdown Export to markdown file(s) Navigate to Markdown
sql SQL Export to SQL INSERT statements Navigate to SQL
source.code Source code Export to source code array Navigate to Source Code
txt TXT Export to plain text format Navigate to TXT
xml XML Export to XML file(s) Navigate to XML
xlsx XLSX Export to XLSX (Excel spreadsheet) format Navigate to XLSX

CSV processor

Id Name Description Type Default Value Allowed Values
extension File extension   String csv Any
delimiter Delimiter Column delimiter. You can use special characters \ + t,n,r String , Any
rowDelimiter Row delimiter Row delimiter. Default is system-specific line feed delimiter. You can use special characters \ + t,n,r String default default, \n, \r, \r\n, \n\r
header Header CSV header settings String top none, top, bottom
headerFormat Header format Header format String label label, description, both
escape Characters escape Bad characters escaping model (surrounded with quotes or escaped with '\' character) String quotes quotes, escape
quoteChar Quote character Character which will be used to quote strings (space means no quote) String " Any
quoteAlways Quote always Quote all cell values. Cannot be used with "quoteNever" String disabled disabled, all, strings, all but numbers, all but nulls
quoteNever Quote never Do not quote cell values. Cannot be used with "quoteAlways" Boolean false Any
nullString NULL string String which will be used instead of NULL values String Any
formatNumbers Format numbers Format numeric values using locale settings Boolean false Any

DbUnit processor

Id Name Description Type Default Value Allowed Values
upperCaseTableName Force upper case table name   Boolean true Any
upperCaseColumnNames Force upper case column names   Boolean true Any
extension File extension   String xml Any
includeNullValues Include NULL values in export   Boolean true Any
nullValueString Replace NULL values with   String [NULL] Any

HTML processor

Id Name Description Type Default Value Allowed Values
extension File extension   String html Any
tableHeader Output table header Output query or table name as first row in generated table Boolean true Any
columnHeaders Output column headers Output column names as extra row in generated table Boolean true Any
extractImages Images Extract images to graphic files Boolean true Any

JSON processor

Id Name Description Type Default Value Allowed Values
printTableName Print table name   Boolean true Any
formatDateISO Format dates in ISO 8601   Boolean true Any
extension File extension   String json Any

Markdown processor

Id Name Description Type Default Value Allowed Values
extension File extension   String md Any
nullString NULL string String which will be used instead of NULL values String Any
formatNumbers Format numbers Format numeric values using locale settings Boolean false Any
showHeaderSeparator Show header separator Print header separator (---). Required for GitHub markdown. Boolean true Any
confluenceFormat Confluence format Use Confluence format (special format of header and no separator line) Boolean false Any

SQL processor

Id Name Description Type Default Value Allowed Values
includeAutoGenerated Include generated columns Include auto-generated columns (e.g. auto-increment) in SQL INSERT Boolean false Any
extension File extension   String sql Any
userTableName Target table name You can specify the name of the target table. It will be used as is to generate an INSERT statement.
If you want the table name also contains the scheme or/and catalog identifier, you can prescribe it in this field by yourself.
By default, the name of the original table is used.
String Any
nativeFormat Native date/time format Use native date/time format in INSERT statements Boolean true Any
omitSchema Omit schema name Omit schema/catalog name in INSERT statements Boolean false Any
rowsInStatement Data rows per statement Number of data rows per single insert statement Integer 10 Any
lineBeforeRows Insert line before rows Insert line feed before values (for multi-row inserts) Boolean true Any
keywordCase Keyword case You can choose lower or upper keyword case String upper upper, lower
identifierCase Identifier case You can choose lower or upper keyword case for table and column names String as is as is, upper, lower
upsertKeyword Upsert keyword You can choose different upsert keywords String INSERT INSERT, INSERT ALL, UPDATE OR, UPSERT INTO, REPLACE INTO, ON DUPLICATE KEY UPDATE, ON CONFLICT
insertOnConflict On conflict expression Expression for the end of the statement. Enter the required value in this field.
This is database specific setting
String Any

Source code processor

Id Name Description Type Default Value Allowed Values
language Language Programming languages String PHP &lt; 5.4 PHP &lt; 5.4, PHP 5.4+
formatDateISOPHP Format dates in ISO 8601   Boolean true Any
extension File extension   String php Any
quoteChar Quote character Character which will be used to quote strings String " ", '
rowDelimiter Row delimiter Row delimiter. Default is system-specific line feed delimiter.
You can use special characters \ + t,n,r
String default default, \n, \r, \r\n, \n\r

TXT processor

Id Name Description Type Default Value Allowed Values
extension File extension   String txt Any
batchSize Batch size   String 200 Any
minColumnLength Min column length Minimum column length.
If the cell value is smaller, then spaces will be added to the specified length.
String 3 Any
maxColumnLength Max column length Maximum column length.
If the cell value is longer than the specified parameter, then it will be crop.
String 0 Any
showNulls Show NULLs Show NULLs if this setting is enabled or nothing if disabled Boolean false Any
delimHeader Show header delimiter Will add hyphen characters in the first row without a header, or between the header and data. Boolean true Any
delimLeading Show leading delimiter Will add a pipe character at the start of the row. Boolean true Any
delimTrailing Show trailing delimiter Will add a pipe character at the end of the row. Boolean true Any
delimBetween In-between delimiter Will add the chosen custom character (ex. pipe, space, or nothing) between data values. String | Any
showHeader Print header Print column names on the top.
You can disable this setting for the multi-export along with the header delimiter setting
Boolean true Any

XML processor

Id Name Description Type Default Value Allowed Values
extension File extension   String xml Any
includeDoctype Include DOCTYPE declaration   Boolean false Any

XLSX processor

Id Name Description Type Default Value Allowed Values
extension File extension   String xlsx Any
rownumber Row number(s) Set row index as first column Boolean false Any
border Border style Cell borders style String THIN NONE, THIN, THICK
nullString NULL string String which will be used instead of NULL values String Any
header Header format Header format String label label, description, both, none
headerfont Header row font First row font properties String BOLD NONE, BOLD, ITALIC, STRIKEOUT, UNDERLINE
trueString Boolean string TRUE String which will be used instead of TRUE boolean values String true Any
falseString Boolean string FALSE String which will be used instead of FALSE boolean values String false Any
trimString Trim strings Trim all string values (remove extra leading and trailing spaces). Boolean false Any
exportSql Export SQL Export SQL to a second sheet Boolean false Any
splitSqlText Split SQL Text Split exported SQL on rows by CR Boolean false Any
splitByRowCount Max row on sheet Split by row count Integer 1048575 Any
splitByColNum Column group Column number for grouping rows on sheet by column value Integer 0 Any
dateFormat Excel date format Excel date and time format (e.g. m/d/yy h:mm) it can be changed in Excel application String m/d/yy m/d/yy, d-mmm-yy, d-mmm, mmm-yy, h:mm AM/PM, h:mm:ss AM/PM, h:mm, h:mm:ss, m/d/yy h:mm
appendStrategy Append strategy A strategy that will be used when appending data to an existing file String create new sheets create new sheets, use existing sheets