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:

AttributeDescription
typeType of the processor.
producerSettings that affect how the data is extracted.
See the full table of supported settings in the main section.
consumerSettings that affect how the data is transformed before processing.
See the full table of supported settings in the main section.
processorSettings 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

IdNameDescriptionTypeDefault ValueAllowed Values
extractTypeExtract typeData extraction mode. Denotes whether a single query or multiple segmented queries should be used to extract data.StringSINGLE_QUERYSINGLE_QUERY, SEGMENTS
segmentSizeSegment sizeSpecifies how many rows are read per segment during data extraction.
See extractType
Integer100000Any
fetchSizeFetch sizeNumber of rows to fetch per one server round trip.
May greatly affect extraction performance.
Integer10000Any
openNewConnectionsOpen 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.
BooleantrueAny
queryRowCountSelect row countQuery row count before performing export.
This will let you track export progress but may cause performance faults in some cases.
BooleantrueAny

Consumer Settings

IdNameDescriptionTypeDefault ValueAllowed Values
formatterProfileFormatting ProfileSpecifies the profile used for formatting data.StringAny
valueFormatValue FormattingSpecifies how the data is interpreted.StringUIUI, EDIT, NATIVE
lobExtractTypeBinaries PolicySpecifies how binaries are processed.StringINLINESKIP, FILES, INLINE
lobEncodingBinaries EncodingSpecifies how binaries are encoded.StringBINARYBASE64, HEX, BINARY, NATIVE
outputClipboardCopy to ClipboardSpecifies that the data should be copied to the clipboard rather written to files on a disk.BooleanfalseAny
outputFolderOutput DirectoryOutput directory pattern. Specifies there the output files should be located.
StringN/AAny
outputFilePatternOutput FilenameOutput filename pattern.String${table}_${timestamp}Any
outputEncodingOutput EncodingSpecifies the file encoding.StringUTF-8Any
outputEncodingBOMInsert BOMSpecifies 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.
BooleanfalseAny
outputTimestampPatternTimestamp PatternPattern used for the ${timestamp} variable in outputFolder and outputFilePattern.StringyyyyMMddHHmmAny
appendToFileAppend to the end of the fileIf file already exists, appends data at end of it.
Only works against compatible processors.
BooleanfalseAny
useSingleFileWrite to the single fileWrite all streams to the single file.
Only works against compatible processors.
BooleanfalseAny
compressResultsCompressSpecifies whether the output file should be compressed using ZIP.BooleanfalseAny
splitOutFilesSplit output fileSpecifies whether the output file should be split using the maxOutFileSize threshold. If size exceeds this threshold, a separate file is created and so on.BooleanfalseAny
maxOutFileSizeMaximum file sizeMaximum size of a single file.
See splitOutFiles
Integer10000000Any

Processor Settings

CSV

IdNameDescriptionTypeDefault ValueAllowed Values
extensionFile extension StringcsvAny
delimiterDelimiterColumn delimiter. You can use special characters \ + t,n,rString,Any
rowDelimiterRow delimiterRow delimiter. Default is system-specific line feed delimiter. You can use special characters \ + t,n,rStringdefaultdefault, \n, \r, \r\n, \n\r
headerHeaderCSV header settingsStringtopnone, top, bottom
headerFormatHeader formatHeader formatStringlabellabel, description, both
escapeCharacters escapeBad characters escaping model (surrounded with quotes or escaped with '\' character)Stringquotesquotes, escape
quoteCharQuote characterCharacter which will be used to quote strings (space means no quote)String"Any
quoteAlwaysQuote alwaysQuote all cell values. Cannot be used with "quoteNever"Stringdisableddisabled, all, strings, all but numbers, all but nulls
quoteNeverQuote neverDo not quote cell values. Cannot be used with "quoteAlways"BooleanfalseAny
nullStringNULL stringString which will be used instead of NULL valuesStringAny
formatNumbersFormat numbersFormat numeric values using locale settingsBooleanfalseAny

DbUnit

IdNameDescriptionTypeDefault ValueAllowed Values
upperCaseTableNameForce upper case table name BooleantrueAny
upperCaseColumnNamesForce upper case column names BooleantrueAny
extensionFile extension StringxmlAny
includeNullValuesInclude NULL values in export  BooleantrueAny
nullValueStringReplace NULL values with String[NULL]Any

HTML

IdNameDescriptionTypeDefault ValueAllowed Values
extensionFile extension StringhtmlAny
tableHeaderOutput table headerOutput query or table name as first row in generated tableBooleantrueAny
columnHeadersOutput column headersOutput column names as extra row in generated tableBooleantrueAny
extractImagesImagesExtract images to graphic filesBooleantrueAny

JSON

IdNameDescriptionTypeDefault ValueAllowed Values
printTableNamePrint table name BooleantrueAny
formatDateISOFormat dates in ISO 8601 BooleantrueAny
extensionFile extension StringjsonAny

Markdown.table

IdNameDescriptionTypeDefault ValueAllowed Values
extensionFile extension StringmdAny
nullStringNULL stringString which will be used instead of NULL valuesStringAny
formatNumbersFormat numbersFormat numeric values using locale settingsBooleanfalseAny
showHeaderSeparatorShow header separatorPrint header separator (---). Required for GitHub markdown.BooleantrueAny
confluenceFormatConfluence formatUse Confluence format (special format of header and no separator line)BooleanfalseAny

SQL

IdNameDescriptionTypeDefault ValueAllowed Values
includeAutoGeneratedInclude generated columnsInclude auto-generated columns (e.g. auto-increment) in SQL INSERTBooleanfalseAny
extensionFile extension StringsqlAny
nativeFormatNative date/time formatUse native date/time format in INSERT statementsBooleantrueAny
omitSchemaOmit schema nameOmit schema/catalog name in INSERT statementsBooleanfalseAny
rowsInStatementData rows per statementNumber of data rows per single insert statementInteger10Any
lineBeforeRowsInsert line before rowsInsert line feed before values (for multi-row inserts)BooleantrueAny
keywordCaseKeyword caseYou can choose lower or upper keyword caseStringupperupper, lower
identifierCaseIdentifier caseYou can choose lower or upper keyword case for table and column namesStringas isas is, upper, lower
upsertKeywordUpsert keywordYou can choose different upsert keywordsStringINSERTINSERT, INSERT ALL, UPDATE OR, UPSERT INTO, REPLACE INTO, ON DUPLICATE KEY UPDATE, ON CONFLICT
insertOnConflictOn conflict expressionExpression for the end of the statement. Enter the required value in this field.
This is database specific setting
StringAny

Source.code

IdNameDescriptionTypeDefault ValueAllowed Values
languageLanguageProgramming languagesStringPHP &lt; 5.4PHP &lt; 5.4, PHP 5.4+
formatDateISOPHPFormat dates in ISO 8601 BooleantrueAny
extensionFile extension StringphpAny
quoteCharQuote characterCharacter which will be used to quote stringsString"", '
rowDelimiterRow delimiterRow delimiter. Default is system-specific line feed delimiter.
You can use special characters \ + t,n,r
Stringdefaultdefault, \n, \r, \r\n, \n\r

TXT

IdNameDescriptionTypeDefault ValueAllowed Values
extensionFile extension StringtxtAny
batchSizeBatch size String200Any
minColumnLengthMin column length String3Any
maxColumnLengthMax column length String0Any
showNullsShow NULLs BooleanfalseAny
delimHeaderShow header delimiter BooleantrueAny
delimLeadingShow leading delimiter BooleantrueAny
delimTrailingShow trailing delimiter BooleantrueAny
delimBetweenShow in-between delimiter BooleantrueAny

XML

IdNameDescriptionTypeDefault ValueAllowed Values
extensionFile extension StringxmlAny

XLSX

IdNameDescriptionTypeDefault ValueAllowed Values
extensionFile extension StringxlsxAny
rownumberRow number(s)Set row index as first columnBooleanfalseAny
borderBorder styleCell borders styleStringTHINNONE, THIN, THICK
nullStringNULL stringString which will be used instead of NULL valuesStringAny
headerColumn names as headerUse column name as first rowBooleantrueAny
headerfontHeader row fontFirst row font propertiesStringBOLDNONE, BOLD, ITALIC, STRIKEOUT, UNDERLINE
trueStringBoolean string TRUEString which will be used instead of TRUE boolean valuesStringtrueAny
falseStringBoolean string FALSEString which will be used instead of FALSE boolean valuesStringfalseAny
exportSqlExport SQLExport SQL to a second sheetBooleanfalseAny
splitSqlTextSplit SQL TextSplit exported SQL on rows by CRBooleanfalseAny
splitByRowCountMax row on sheetSplit by row countInteger1048575Any
splitByColNumColumn groupColumn number for grouping rows on sheet by column valueInteger0Any
dateFormatExcel date formatExcel date and time format (e.g. m/d/yy h:mm) it can be changed in Excel applicationStringm/d/yym/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

Did we resolve your issue?