The typical way to achieve this is to export to CSV and then load the CSV into Excel.
TL;DR:
-
For a server-side Excel-friendly CSV file from a
SELECT
query, run this:SELECT ... FROM someTable WHERE etc INTO OUTFILE 'someTableExport.csv' CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY '\r\n';
-
For a server-side Excel-friendly CSV file use
mysqldump
like so:mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables table1 table2 table3
-
For a client-side Excel-friendly CSV file using
mysqlsh
(MySQL Shell) like so:mysqlsh --user="mysqlUserName" --host="serverHostName" --port=3306 --schema="databaseName" # Once connected, run this: util.exportTable("tableName", "file:///C:/Users/You/Desktop/test.csv", { dialect: "csv", fieldsEscapedBy: ""})
First, a caution about Excel:
Excel’s vs. MySQL’s default CSV formats:
Remember that Excel has its own underdocumented ideas about how CSV files should be formatted and these stand in-contrast to MySQL’s own ideas about CSV files; though Excel is largely compliant with RFC 4180 you still need to prod and poke MySQL and its associated tooling to generate CSV files that Excel won’t misinterpret:
Excel | MySQL (default) | MySQL (when configured) | |
---|---|---|---|
SQL NULL |
Zero-length value | Literal \N |
Literal NULL |
Text values that don’t contain commas, quotes, or line-breaks | Not enclosed | Not enclosed | Enclosed in " |
Text values that contain commas, quotes, or line-breaks | Enclosed in " |
Not enclosed | Enclosed in " |
Non-text values | Not enclosed | Not enclosed | Not enclosed |
Line-breaks and tabs in text values | Literal | Escaped as [\r]\n |
Literal |
Double-quotes in text values | Doubled-up "" |
Escaped as \" |
Doubled-up "" |
Field separator | , |
\t (Tab) |
, |
Record separator | \r\n |
\n |
\r\n |
Commas inside non-quoted text values | (Results in broken table data) | Not escaped | Will always be quoted if the value contains a comma |
UTF-8 support |
|
Use utf8mb4 .Do not specify the older broken utf8 or utf8mb3 encodings. |
Use utf8mb4 |
As per the table above, MySQL can generate Excel-friendly CSV files, excepting that SQL NULL
s will always be interpreted by Excel as literal text, though it’s trivial to use PowerQuery or even just Find-and-Replace in Excel to replace them with empty cells.
Excel and special CSV text markers
- If any of your text values start with
=
then you’ll need to manually fudge those as even when quoted Excel will interpret the text following=
as a formula instead of literal text. - Also watch out for leading text like
sep=
too.
Excel and UTF-8 encoding:
Surprisingly, it wasn’t until Excel was 31 years old (Excel 2016) when Excel added built-in support for UTF-8 encoding in files without needing a BOM, but it still defaults to importing and exporting CSV files using your system-default non-Unicode encoding (e.g. Windows-1252
).
- When importing CSV into Excel, be sure to select Codepage
65001
for correct UTF-8 handling as Excel still defaults to non-Unicode-based codepages for some reason.-
Note that opening a CSV file in Excel won’t display the Text Import Wizard. (As of Excel 2021) you need to copy-and-paste CSV text into Excel and use the popup menu to use the legacy (frozen-in-1994) wizard, or use Data > From Text/CSV on the ribbon to use the newer (but less flexible, imo) PowerQuery-based CSV import wizard:
-
Excel 2007-2013 Excel 2016+
-
-
Your options:
Option | SELECT INTO OUTFILE |
mysqldump --tab |
mysqldump > file.csv |
mysqlsh |
MySQL Workbench |
---|---|---|---|---|---|
Server-side CSV | True | True | True | True | Broken |
Remote (client-side) CSV | False | False | False | True | Broken |
MySQL Server version support | All versions | All versions | All versions | Only 5.7 and later | All versions |
Option 1: Exporting an Excel-friendly CSV using INTO OUTFILE
:
- You can do a server-side CSV export by using the
INTO OUTFILE
clause of aSELECT
query.- Because this is “normal” SQL that’s executed by the MySQL server this works regardless of whatever MySQL client tool you’re using, so you don’t need to install MySQL Workbench.
- …but because this is a server-side export you need to have permission to write to the server’s filesystem which you might not have, in which case consider using specialty export tools like
mysqldump
(see below).
- MySQL’s
OUTFILE
clause has a number of optional subclauses that must be specified for some-level of compatibility with Excel’s own CSV reader:FIELDS...
TERMINATED BY
(default:'\t'
, for Excel use','
)[OPTIONALLY] ENCLOSED BY
(default:''
, should be'"'
with theOPTIONALLY
keyword)ESCAPED BY
(default:'\\'
, for Excel use''
)
LINES...
TERMINATED BY
(default:'\n'
, for Excel use'\r\n'
)STARTING BY
(default:''
, for Excel you can omit this or use the MySQL default).
- Do not use
ENCLOSED BY
(without the precedingOPTIONALLY
keyword) as that will enquote all values, regardless of type (i.e. it will enquoteint
values which will cause Excel (by default) to interpret them as text (strings) instead of numbers).
- Note that there is no option to instruct MySQL to output SQL
NULL
s as empty-fields, and so Excel will interpet them as unquoted strings of text (i.e. as"NULL"
), so you’ll want to do a Find-and-Replace in Excel after importing the file. - If your
INTO OUTFILE <fileName>
filename (e.g.'someTableExport.csv'
above) is not an absolute path then it will be saved into your database’sdatadir
directory. RunSHOW VARIABLES LIKE 'datadir';
to get the path. Note that you might not necessarily have read/write permission for new files under that directory.
So your query (SELECT * FROM document WHERE documentid...
) would look something like this:
SELECT
*
FROM
document
WHERE
documentid IN ( SELECT documentid FROM TaskResult WHERE taskResult = 2429 )
INTO
OUTFILE 'someTableExport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY ''
LINES TERMINATED BY '\r\n';
Option 2: Exporting an Excel-friendly CSV using mysqldump
:
- To store dump into CSV file using the
--tab
command-line option, as permysqldump
‘s documentation. - Unfortunately
mysqldump
‘s--tab=
option won’t work for remote MySQL servers: this is because--tab="fileName.csv"
can only represent a path on the server.- While you can use
stdout
redirection to generate a local file (i.e.mysqldump --etc > output.csv
) you cannot use the--fields-terminated-by
and other format options withstdout
, making it useless for Excel-compatible output. So if you’re remote and cannotssh
-in then you will need to use MySQL Shell (mysqlsh
) instead (see below).
- While you can use
- Note that
mysqldump
does not supportSELECT
queries for dumping data: it does support simpleWHERE
-style filters with the--where=<expr>
option but this doesn’t support things like filtering using anINNER JOIN
(though as a workaround you couldSELECT
into a new table, then runmysqldump
on that new table. Note that you cannot useTEMPORARY TABLE
withmysqldump
as Temporary Tables are connection-scoped).
In the OP’s case, due to limitations inherent in how the --where=
command-line option works, they’ll want to export both tables (document
and TaskResult
) and apply their filter logic in Excel PowerQuery or similar. Perform the export like so:
mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables document TaskResult
-
The above command-line should work without modification in Windows’
cmd.exe
, macOS’szsh
, andbash
on Linux – providedmysqldump
is in yourPATH
. -
The use of hex-encoded chars means sidestepping the hassle of figurig out how to pass double-quotes and line-breaks as literals in your shell and terminal (
0x22
is"
,0x2C
is,
, and0x0D0A
is\r\n
). -
Avoid using the
--password=<value>
(aka-p<value>
) option on
themysqldump
command-line, as it will mean your password will be saved in plaintext to your terminal or console history file, which is an obvious massive security risk.- So if you’re in an interactive command-line session need to specify a password then
mysqldump
will prompt you for the password immediately when the program runs so it won’t be saved to your history file. - If you want to run
mysqldump
in a non-interactive context (e.g. from within a web-application, daemon, or other process) then there’s (normally) no history file to worry about, but you should still consider alternative approaches before resorting to handling passwords in an insecure way.
- So if you’re in an interactive command-line session need to specify a password then
-
If you don’t specify an absolute path but use a short (unqualified) filename like
INTO OUTFILE 'output.csv'
orINTO OUTFILE './output.csv'
theb it will store the output file to the directory specified bySHOW VARIABLES LIKE 'datadir';
.
Option 3: Exporting an Excel-friendly CSV using MySQL Workbench:
Unfortunately, you can’t (unless you don’t have any double-quotes anywhere in your data): As of late 2022 MySQL Workbench has an open bug regarding its own CSV export feature: output files never escape double-quote characters in text, so pretty much all CSV-handling software out there will report a malformed CSV file or import data to the wrong columns – so this makes it completely unsuitable for use with Excel.
Option 4: Exporting an Excel-friendly CSV using MySQL Shell (aka mysqlsh
):
- This is probably the simplest option, but you might need to install the MySQL Shell as it doesn’t come in-box in most MySQL installations.
- MySQL Shell supports connecting to MySQL Server 5.7 and later (but not older versions). If you’re still using MySQL Server 5.6 or earlier (then you really should update to 5.7 or later anyway) you’ll have to stick with
mysqldump
run locally on the MySQL Server itself (but you can use anssh
session, of course).- The new “MySQL X” protocol (
mysqlx://user@host/schema
) is not supported by MySQL 5.7, butmysqlsh
supports non-X connections with old-style command-line parameters.
- The new “MySQL X” protocol (
- Install MySQL Shell if it isn’t already installed.
- If you start MySQL Shell without any command-line arguments (e.g. because you use the Start Menu shortcut on Windows)) then use the
\connect
command to connect.-
For MySQL 5.7 use
\connect mysql://username@hostname
-
For MySQL 8.0+ there are a variety of ways to connect, including the “MySQL X” protocol as well as “Classic” connections. Consult the docs for more info.
-
If your username contains literal
@
characters then you need to percent-encode them (e.g. if you’re using Azure MySQL then your full username will be likeusername%40servername@servername.mysql.database.azure.com
). -
Immediately after you submit the
\connect
command you will be prompted for your password interactively.
-
- If you can start
mysqlsh
with arguments, then you can runmysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName"
directly without using the\connect
command. - Once connected, run the
util.exportTable(tableName, outputUri, options)
command with the following arguments:tableName
: your table name.- Unfortunately there doesn’t seem to be a way to apply a
WHERE
filter or export the results of aSELECT
query, (though as withmysqldump
you could always save your query results to a newTABLE
, then export that table, thenDROP TABLE
when you’re done with it. Remember thatTEMPORARY TABLE
won’t work here as tables created in one session aren’t visible from any other session – andmysqlsh
will have its own session.
- Unfortunately there doesn’t seem to be a way to apply a
outputUri
: To save the file locally use afile:///
URI.- On Windows you can use a forward-slash as a directory-name separator instead of a backslash. e.g.
file:///C:/Users/Me/Desktop/export.csv
.
- On Windows you can use a forward-slash as a directory-name separator instead of a backslash. e.g.
options
: To ensure compatibility with Excel specify{ dialect: "csv", fieldsEscapedBy: ""}
.- The
dialect: "csv"
option sets Excel-compatible defaults for all-but-one of theOUTFILE
parameters, so you must also specifyfieldsEscapedBy: ""
, otherwise SQLNULL
will be be rendered as\N
(literally) while double-quotes and line-breaks inside text values will be backslash-escaped, which Excel doesn’t support.
- The