How do i export mysql workbench results to excel?

-type

Possible values: text, sqlinsert, sqlupdate, sqldeleteinsert, sqlmerge, xml, ods, xlsm, xls, xlsx, html, json

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.

sqlmerge will generate statements that will result in INSERT or UPDATE type of statements. The exact syntax depends on the current database. To select a syntax for a different DBMS, use the parameter -mergeType.

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 be opened e.g. with OpenOffice.org.

xlsm will generate a spreadsheet file in the Microsoft Excel 2003 XML format ("XML Spreadsheet"). When using Microsoft Office 2010, this export format should should be saved with a file extension of .xml in order to be identified correctly.

xls will generate a spreadsheet file in the proprietary (binary) format for Microsoft Excel (97-2003). The file poi.jar is required.

xlsx will generate a spreadsheet file in the default format introduced with Microsoft Office 2007. Additional external libraries are required in order to be able to use this format. Please read the note at the beginning of this section.

This parameter supports auto-completion.

The output file to which the exported data is written.

This parameter is ignored if -outputDir is also specified.

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.

This parameter supports auto-completion.

Define the schema in which the table(s) specified with -sourceTable are located. This parameter only accepts a single schema name. If you want to export tables from more than one schema, you need to fully qualify them as shown in the description of the -sourceTable parameter.

This parameter supports auto-completion.

Selects the object types to be exported. By default only TABLEs are exported. If you want to export the content of VIEWs or SYNONYMs as well, you have to specify all types with this parameter.

-sourceTable=* -types=VIEW,SYNONYM or -sourceTable=T% -types=TABLE,VIEW,SYNONYM

This parameter supports auto-completion.

The tables listed in this parameter will not be exported. This can be used when all but a few tables should be exported from a schema. First all tables specified through -sourceTable will be evaluated. The tables specified by -excludeTables can include wildcards in the same way, -sourceTable allows wildcards.

-sourceTable=* -excludeTables=TEMP* will export all tables, but not those starting with TEMP.

This parameter supports auto-completion.

Define a common prefix for all tables listed with -sourceTable. When this parameter is specified the existence of each table is not tested any longer (as it is normally done).

When this parameter is specified the generated statement for exporting the table is changed to a SELECT * FROM [prefix]tableName instead of listing all columns individually.

This can be used when exporting views on tables, when for each table e.g. a view with a certain prefix exists (e.g. table PERSON has the view V_PERSON and the view does some filtering of the data.

This parameter can not be used to select tables from a specific schema. The prefix will be prepended to the table's name.

Defines 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, execute WbExport with the parameter -showEncodings. This parameter is ignored for XLS, XLSX and ODS exports.

This parameter supports auto-completion and if it is invoked for this parameter, it will show a list of encodings defined through the configuration property workbench.export.defaultencodings This is a comma-separated list that can be changed using WbSetConfig

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.

This parameter supports auto-completion.

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.

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 output file 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.

Defines an additional WHERE clause that is appended to all SELECT queries to retrieve the rows from the database. No validation check will be done for the syntax or the columns in the where clause. If the specified condition is not valid for all exported tables, the export will fail.

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.

SQL exports (-type=sqlinsert) generated with -clobAsFile=true can only be used 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.

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

When exporting CLOB or BLOB columns as external files, the generated files can be distributed over several directories to avoid an excessive number of files in a single directory. The parameter lobsPerDirectory defines how many LOB files are written into a single directory. When the specified number of files have been written, a new directory is created. The directories are always created as a sub-directory of the target directory. The name for each directory is the base export filename plus "_lobs" plus a running number. So if you export the data into a file "the_big_table.txt", the LOB files will be stored in "the_big_table_lobs_1", "the_big_table_lobs_2", "the_big_table_lobs_3" and so on.

The directories will be created if needed, but if the directories already exist (e.g. because of a previous export) their contents will not be deleted!

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 name based on the row and column numbers).

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

Possible values: true,false

Controls whether results are appended to an existing file, or overwrite an existing file. This parameter is only supported for text, SQL, XLS and XLSX export types.

When used with XLS oder XSLX exports, a new worksheet will be created.

The locale (language) to be used when formatting date and timestamp values. The language will only be relevant if the date or timestamp format contains placeholders that are language dependent (e.g. the name of the month or weekday).

This parameter supports code-completion.

Possible values: file, dbms, ansi, base64, pghex

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. It is only valid for Text, SQL and XML exports, although not all parameter values make sense for all export types.

The type base64 is primarily intended for Text exports.

The type pghex is intended to be used for export files that should be imported using PostgreSQL's COPY command.

The types dbms and ansi are intended for SQL exports and generate a representation of the binary data as part of the SQL statement. DBMS will use a format that is understood by the DBMS you are exporting from, while ansi will generate a standard hex based representation of the binary data. The syntax generated by the ansi format is not understood by all DBMS!

Two additional SQL literal formats are available that can be used together with PostgreSQL: pgDecode and pgEscape. pgDecode will generate a hex representation using PostgreSQL's decode() function. Using decode is a very compact format. pgEscape will use PostgreSQL's escaped octets, and generates much bigger statements (due to the increase escaping overhead).

When using file, base64 or ansi the file can be imported using WbImport

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 that SQL scripts generated with -blobType=file can only be used 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.

This parameter supports auto-completion.

Using these parameters, arbitrary text can be replaced during the export. -replaceExpression defines the regular expression that is to be replaced. -replaceWith defines the replacement value. -replaceExpression='(\n|\r\n)' -replaceWith=' ' will replace all newline characters with a blank.

The search and replace is done on the "raw" data retrieved from the database before the values are converted to the corresponding output format. In particular this means replacing is done before any character escaping takes place.

Because the search and replace is done before the data is converted to the output format, it can be used for all export types (text, xml, Excel, ...).

Only character columns (CHAR, VARCHAR, CLOB, LONGVARCHAR) are taken into account.

Possible values: true, false

If this parameter is set to true, values from CHAR columns will be trimmed from trailing whitespace. This is equivalent to the Trim CHAR data in the connection profile.

These parameters define the values that should be exported for real <tt>boolean</tt> values. Both parameters must be specified. If only one of is provided, it is ignored.

Valid values: true, false, <numeric value>

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

How do I export data from MySQL workbench to excel?

How do I export SQL Workbench query results?

How do I export data from MySQL workbench?

How do you save MySQL query result in a Excel file?