9. Export data using WbExport

9.1. Parameters for the type TEXT
9.2. Parameters for type XML
9.3. Parameters for type SQLUPDATE, SQLINSERT or SQLDELETEINSERT
9.4. Parameters for Spreadsheet types (ods, xslx, xls)
9.5. Compressing export files
9.6. Examples

The WbExport command can be used to export the contents of the database into external files. Several types of output files are supported. The WbExport command can either be used from within the GUI like any other command (such as UPDATE or INSERT), or it can be used as part of a SQL script that is run in batch mode.

The WbExport command exports either the result of the next SQL statement (which has to produce a result set) or the content of the table(s) specified with the -sourceTable parameter. The data is directly written to the output file and not loaded into memory. The export file(s) can be compressed ("zipped") on the fly. WbImport can import the zipped (text or XML) files directly without the need to unzip them.

If you want to save the data that is currently displayed in the result area into an external file, please use the Save Data as feature. You can also use the Database Explorer to export multiple tables.

[Note]

When using a SELECT based export, you have to run both statements (WbExport and SELECT) as one script. Either select both statements in the editor and choose SQLExecute selected, or make the two statements the only statements in the editor and choose SQLExecute all.

You can also export the result of a SELECT statement, by selecting the statment in the editor, and then choose SQLExport query result.

When exporting data into a Text or XML file, the content of BLOB columns is written into separate files. One file for each column of each row. Text files that are created this way can most probably only be imported using SQL Workbench/J as the main file will contain the filename of the BLOB data file instead of the actual BLOB data. The only other application that I know of, that can handle this type of imports is Oracle's SQL*Loader utility. If you run the text export together with the parameter -writeoracleloader=true the control file will contain the approriate definitions to read the BLOB data from the external file.

To be able to generate the binary Microsoft Excel format, you need to download the POI library from the Apache project's website: http://poi.apache.org/. Any version greater than 2.5 is fine. The distribution contains a jar file named e.g. poi-3.0.1-FINAL-20070705.jar (exact name depends on the version). This jar file needs to be copied into the same directory as sqlworkbench.jar under the name poi.jar.

If an export to an Excel spreadsheet is needed, it is recommended to use the Excel XML format, as that doesn't need an external library (just like exporting to the OpenDocument format).

The command supports the following parameters:

ParameterDescription
-type

Possible values: text, sqlinsert, sqlupdate, sqldeleteinsert, xml, ods, xsl, xslt, html

Defines the type of the output file. sqlinsert will create the necessary INSERT statements to put the data into a table. If the records may already exist in the target table but you don't want to (or cannot) delete the content of the table before running the generated script, SQL Workbench/J can create a DELETE statement for every INSERT statement. To create this kind of script, use the sqldeleteinsert type.

In order for this to work properly the table needs to have keycolumns defined, or you have to define the keycolumns manually using the -keycolumns switch.

sqlupdate will generate UPDATE statements that update all non-key columns of the table. This will only generate valid UPDATE statements if at least one key column is present. If the table does not have key columns defined, or you want to use different columns, they can be specified using the -keycolumns switch.

ods will generate a spreadsheet file in the OpenDocument format that can e.g. be opened with OpenOffice.org.

xlsx will generate a spreadsheet file in the Microsoft Excel XML format and is the recommended format for creating Excel spreadsheets.

xls will generate a spreadsheet file in the propriatary (binary) format for Microsoft Excel.

-createDir If this parameter is set to true, SQL Workbench/J will create any needed directories when creating the output file.
-sourcetable

Defines a list of tables to be exported. If this switch is used, -outputdir is also required unless exactly one table is specified. If one table is specified, the -file parameter is used to generate the file for the table. If more then one table is specified, the -outputdir parameter is used to defined the directory where the generated files should be stored. Each file will be named as the exported table with the approriate extension (.xml, .sql, etc). You can specify * as the table name which will then export all tables accessible by the current user.

If you want to export tables from a different user or schema you can use a schema name combined with a wildcard e.g. -sourcetable=otheruser.*. In this case the generated output files will contain the schema name as part of the filename (e.g. otheruser.person.txt). When importing these files, SQL Workbench/J will try to import the tables into the schema/user specified in the filename. If you want to import them into a different user/schema, then you have to use the -schema switch for the import command.

-outputDirWhen using the -sourcetable switch with multiple tables, this parameter is mandatory and defines the directory where the generated files should be stored.
-continueOnErrorWhen exporting more than one table, this parameter controls whether the whole export will be terminated if an error occurs during export of one of the tables.
-encodingDefines the encoding in which the file should be written. Common encodings are ISO-8859-1, ISO-8859-15, UTF-8 (or UTF8). To get a list of available encodings, execut WbExport with the parameter -showencoding
-showEncodingsDisplays the encodings supported by your Java version and operating system. If this parameter is present, all other parameters are ignored.
-lineEnding

Possible values are: crlf, lf

Defines the line ending to be used for XML or text files. crlf puts the ASCII characters #13 and #10 after each line. This is the standard format on Windows based systems. dos and win are synonym values for crlf, unix is a synonym for lf.

lf puts only the ASCII character #10 at the end of each line. This is the standard format on Unix based systems (unix is a synonym value for this format).

The default line ending used depends on the platform where SQL Workbench/J is running.

-header

Possible values: true, false

If this parameter is set to true, the header (i.e. the column names) are placed into the first line of output file. The default is to not create a header line. You can define the default value for this parameter in the file workbench.settings. This parameter is valid for text and spreadsheet (OpenDocument, Excel) exports.

-compress

Selects whether the output file should be compressed and put into a ZIP archive. An archive will be created with the name of the specified outputfile but with the extension zip. The archive will then contain the specified file (e.g. if you specify data.txt, an archive data.zip will be created containing exactly one entry with the name data.txt). If the exported result set contains BLOBs, they will be stored in a separate archive, named data_lobs.zip.

When exporting multiple tables using the -sourcetable parameter, then SQL Workbench/J will create one ZIP archive for each table in the specified output directory with the filename "tablename".zip. For any table containing BLOB data, one additional ZIP archive is created.

-clobAsFile

Possible values: true, false

For SQL, XML and Text export this controls how the contents of CLOB fields are exported. Usually the CLOB content is put directly into the output file When generating SQL scripts with WbExport this can be a problem as not all DBMS can cope with long character literals (e.g. Oracle has a limit of 4000 bytes). When this parameter is set to true, SQL Workbench/J will create one file for each CLOB column value. This is the same behaviour as with BLOB columns.

Text files that are created with this parameter set to true, will contain the filename of the generated output file instead of the actual column value. When importing such a file using WbImport you have to specify the -clobIsFilename=true parameter. Otherwise the filenames will be stored in the database and not the clob data. This parameter is not necessary when importing XML exports, as WbImport will automatically recognize the external files.

[Note]

SQL scripts (-type=sqlinsert) generated with -clobAsFile=true can only be run with SQL Workbench/J!

All CLOB files that are written using the encoding specified with the -encoding switch. If the -encoding parameter is not specified the default file encoding will be used.

-lobIdCols

When exporting CLOB or BLOB columns as external files, the filename with the LOB content is generated using the row and column number for the currently exported LOB column (e.g. data_r15_c4.data). If you prefer to have the value of a unique column combination as part of the file name, you can specify those columns using the -lobIdCols parameter. The filename for the LOB will then be generated using the base name of the export file, the column name of the LOB column and the values of the specified columns. If you export your data into a file called user_info and specify -lobIdCols=id and your result contains a column called img, the LOB files will be named e.g. user_info_img_344.data

-extensionColumn

When exporting CLOB or BLOB columns as external files, the extension of the generated filenames can be defined based on a column of the result set. If the exported table contains more than one type of BLOBs (e.g. JPEG, GIF, PDF) and your table stores the information to define the extension based on the contents, this can be used to re-generate proper filenames.

This parameter only makes sense if exactly one BLOB column is exported.

-filenameColumn

When exporting CLOB or BLOB columns as external files, the complete filename can be taken from a column of the result set (instead of dynamically creating a new file based on the row and column numbers).

This parameter only makes sense if exactly one BLOB column is exported.

-showProgress

Valid values: true, false, <numeric value>

Control the update frequence in the statusbar (when running in GUI mode). The default is every 10th row is reported. To disable the display of the progress specifiy a value of 0 (zero) or the value false. true will set the progress interval to 1 (one).

9.1. Parameters for the type TEXT

ParameterDescription
-delimiterThe given string sequence will be placed between two columns. The default is a tab character (-delimiter=\t
-dateFormatThe date format to be used when writing date columns into the output file.
-timestampFormatThe format to be used when writing datetime (or timestamp) columns into the output file.
-quoteChar

The character (or sequence of characters) to be used to enclose text (character) data if the delimiter is contained in the data. By default quoting is disabled until a quote character is defined. To set the double quote as the quote character you have to enclose it in single quotes: -quotechar='"'

-quoteCharEscaping

Possible values: none, escape, duplicate

Defines how quote characters that appear in the actual data are written to the output file.

If no quote character has been defined using the -quoteChar switch, this option is ignored.

If escape is specified a quote character (defined through -quoteChar) that is embedded in the exported (character) data is written as e.g. here is a \" quote character.

If duplicate is specified, a quote character (defined through -quoteChar) that is embedded in the exported (character) data is written as two quotes e.g. here is a "" quote character.

-quoteAlways

Possible values: true, false

If quoting is enabled (via -quotechar, then character data will normally only be quoted if the delimiter is found inside the actual value that is written to the output file. If -quoteAlways=true is specified, character data will always be enclosed in the specified quote character. This parameter is ignored if not quote character is specified. If you expect the quote character to be contained in the values, you should enable character escaping, otherwise the quote character that is part of the exported value will break the quote during import.

-decimal The decimal symbol to be used for numbers. The default is a dot (e.g. 3.14152)
-escapeText

This parameter controls the escaping of non-printable or non-ASCII characters. Valid options are ctrl which will escape everything below ASCII 32 (newline, tab, etc), 7bit which will escape everything below ASCII 32 and above 126, 8bit which will escape everything below ASCII 32 and above 255 and extended which will escape everything outside the range [32-126] and [161-255]

This will write a unicode representation of the character into the text file e.g. \n for a newline, \u00F6 for ö. This file can only be imported using SQL Workbench/J (at least I don't know of any DBMS specific loader that will decode this properly)

If character escaping is enabled, then the quote character will be escaped inside quoted values and the delimiter will be escaped inside non-quoted values. The delimiter could also be escaped inside a quoted value if the delimiter falls into the selected escape range (e.g. a tab character).

-formatFile

Possible values: oracle, sqlserver

This parameter controls the creation of a control file for the bulk load utilities of Oracle and Microsoft SQL Server. oracle will create a control file for Oracle's SQL*Loader utility, sqlserver will create a format file for Microsoft's bcp utility. The format file has the same filename as the output file but with the ending .ctl for Oracle and .fmt for SQL Server.

You can specify both formats. In that case two control files will be created.

Note that the generated control file will most probably need some adjustments before you can actually use it.

9.2. Parameters for type XML

ParameterDescription
-tableThe given tablename will be put into the <table> tag as an attribute.
-dateFormatThe date format to be used when writing date columns into the output file.
-timestampFormatThe format to be used when writing datetime (or timestamp) columns into the output file.
-decimalThe decimal symbol to be used for numbers. The default is a dot (e.g. 3.14152)
-useCDATA

Possible values: true, false

Normally any character data written into the xml file will be processed to escape XML characters (e.g. < will be written as &lt;). If you don't want that escaping, set -usecdata=true and all character data (VARCHAR, etc) will be enclosed in a CDATA section.

With -cdata=true a HTML value would be written like this:

<![CDATA[<b>This is a title</b>]]>

With -cdata=false (the default) a HTML value would be written like this:

&lt;b&gt;This is a title&lt;/b&gt;

-stylesheetThe name of the XSLT stylesheet that should be used to transform the SQL Workbench/J specific XML file into a different format. If -stylesheet is specified, -xsltoutput has to be specified as well.
-xsltOutputThe resulting output file (specified with the -file parameter), can be transformed using XSLT after the export has finished. This parameter then defines the name of the outputfile of the transformation.
-verboseXML

Possible values: true, false

This parameter controls the tags that are used in the XML file and minor formatting features. The default is -verboseXML=true and this will generate more readable tags and formatting. However the overhead imposed by this is quite high. Using -verboseXML=false uses shorter tag names (not longer then two characters) and does put more information in one line. This output is a harder to read for a human but is smaller in size which could be important for exports with large result sets.

9.3. Parameters for type SQLUPDATE, SQLINSERT or SQLDELETEINSERT

ParameterDescription
-table Define the tablename to be used for the UPDATE or INSERT statements. This parameter is required if the SELECT statement has multiple tables in the FROM list. table.
-charfunc

If this parameter is given, any non-printable character in a text/character column will be replaced with a call to the given function with the ASCII value as the parameter.

If -charfunc=chr is given (e.g. for an Oracle syntax), a CR (=13) inside a character column will be replaced with:

INSERT INTO ... VALUES ('First line'||chr(13)||'Second line' ... )

This setting will affect ASCII values from 0 to 31

-concat If the parameter -charfunc is used SQL Workbench/J will concatenate the individual pieces using the ANSI SQL operator for string concatenation. In case your DBMS does not support the ANSI standard (e.g. MS ACCESS) you can specify the operator to be used: -concat=+ defines the plus sign as the concatenation operator.
-blobType

Possible values: file, dbms, ansi

This parameter controls how BLOB data will be put into the generated SQL statements. By default no conversion will be done, so the actual value that is written to the output file depends on the JDBC driver's implementation of the Blob interface.

The parameter value file, will cause SQL Workbench/J to write the contents of each blob column into a separate file. The SQL statement will contain the SQL Workbench/J specific extension to read the blob data from the file. For details please refer to BLOB support. If you are planning to run the generated SQL scripts using SQL Workbench/J this is the recommended format.

[Note]

When using -blobType=file the generated SQL script can only be run with SQL Workbench/J!

The parameter value ansi, will generate "binary strings" that are compatible with the ANSI definition for binary data. MySQL and Microsoft SQL Server support these kind of literals.

The parameter value dbms, will create a DBMS specific "binary string". MySQL, HSQLDB, H2 and PostgreSQL are known to support literals for binary data. For other DBMS using this option will still create an ansi literal but this might result in an invalid SQL statement.

-sqlDateLiterals

Possible values: jdbc, ansi, dbms, default

This parameter controls the generation of date or timestamp literals. By default literals that are specific for the current DBMS are created. You can also choose to create literals that comply with the JDBC specification or ANSI SQL literals for dates and timestamps.

jdbc selects the creation of JDBC compliant literals. These should be usable with every JDBC based tool, including your own Java code: {d '2004-04-28'} or {ts '2002-04-02 12:02:00.042'}. This is the recommended format if you plan to use SQL Workbench/J (or any other JDBC based tool) to run the generated statements.

ansi selects the creation of ANSI SQL compliant date literals: DATE '2004-04-28' or TIMESTAMP '2002-04-02 12:04:00'. Please consult the manual of the target DBMS, to find out whether it supports ANSI compliant date literals.

default selects the creation of quoted date and timestamp literals in ISO format (e.g. '2004-04-28'). Several DBMS support this format (e.g. Postgres, Microsoft SQL Server)

dbms selects the creation of specific literals to be used with the current DBMS (using e.g. the to_date() function for Oracle). The format of these literals can be customized if necessary in workbench.settings using the keys workbench.sql.literals.[type].[datatype].pattern where [type] is the type specified with this parameter and [datatype] is one of time, date, timestamp. If you add new literal types, please also adjust the key workbench.sql.literals.types which is used to show the possible values in the GUI (auto-completion "Save As" dialog, Options dialog). If no type is specified (or dbms), SQL Workbench/J first looks for an entry where [type] is the current dbid. If no value is found, default is used.

You can define the default literal format to be used for the WbExport command in the options dialog.

-commitEvery

A numeric value which identifies the number of INSERT or UPDATE statements after which a COMMIT is put into the generated SQL script.

-commitevery=100

will create a COMMIT; after every 100th statement.

If this is not specified one COMMIT; will be added at the end of the script. To suppress the final COMMIT, you can use -commitEvery=none. Passing -commitEvery=atEnd is equivalent to -commitEvery=0

-createTable

Possible values: true, false

If this parameter is set to true, the necessary CREATE TABLE command is put into the output file. This parameter is ignored when creating UPDATE statements.

-keyColumns

A comma separated list of column names that occur in the table or result set that should be used as the key columns for UPDATE or DELETE

If the table does not have key columns, or the source SELECT statement uses a join over several tables, or you do not want to use the key columns defined in the database, this key can be used to define the key columns to be used for the UPDATE statements. This key overrides any key columns defined on the base table of the SELECT statement.

9.4. Parameters for Spreadsheet types (ods, xslx, xls)

ParameterDescription
-pageTitleThe name to be used for the worksheet

9.5. Compressing export files

The WbExport command supports compressing of the generated output files. This includes the "main" export file and any associated LOB files.

When using WbImport you can import the data stored in the archives without unpacking them. Simply specify the archive name with the -file parameter. SQL Workbench/J will detect that the input file is an archive and will extract the information "on the fly". Assume the following export command:

WbExport -type=text -file=/home/data/person.txt -compress=true -sourcetable=person;

This command will create the file /home/data/person.zip that will contain the specified person.txt. To import this export into the table employee, you can use the following command:

WbImport -type=text -file=/home/data/person.zip -table=employee;

Assuming the PERSON table had a BLOB colum (e.g. a picture of the person), the WbExport command would have created an additional file called person_blobs.zip that would contain all BLOB data. The WbImport command will automatically read the BLOB data from that archive.

9.6. Examples

WbExport -type=text
         -file='c:/data/data.txt'
         -delimiter='|'
         -decimal=',';
SELECT * FROM data_table;

Will create a text file with the data from data_table. Each column will be separated with the character | Each fractional number will be written with a comma as the decimal separator. As the SELECT statement retrieves all rows and columns from the table, this could also be written as:

WbExport -type=text
         -file='c:/data/data.txt'
         -delimiter='|'
         -decimal=','
         -sourcetable=data_table;

To export all tables from the current connection into tab-separated files and compress the files, you can use the following statement:

WbExport -type=text
         -outputDir=c:/data/export
         -compress=true				 
         -sourcetable=*;

This will create one zip file for each table containing the exported data as a text file. If a table contains BLOB columns, the blob data will be written into a separate zip file.

The files created by the above statement can be imported into another database using the following command:

WbImport -type=text
         -sourceDir=c:/data/export
         -checkDependencies=true;

To generate a file that contains INSERT statements that can be executed on the target system, the following command can be used:

WbExport -type=sqlinsert
         -file='c:/data/newtable.sql'
         -table=newtable;
SELECT * FROM table1, table2
WHERE table1.column1 = table2.column1;

will create a SQL scripts which inserts the data from table1 and table2 into a table called newtable. If the parameter -table is omitted, the creation of SQL INSERT statements is only possible, if the SELECT is based on a single table (or view).

WbExport and the "Max. Rows" option

There is a difference in the behaviour of the command regarding the "Max. Rows" setting in the GUI. When you use the WbExport command together with a SELECT query, the Max. Rows setting will be respected by the SELECT statement (and you will see a warning that the result set was limited). When you use the WbExport with the -sourcetable switch, the "Max. Rows" setting will not be respected, and all rows from the table will be written into the specified file.