Exporting results of a Mysql query to excel?

Question

My requirement is to store the entire results of the query

SELECT * FROM document 
WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429)

to an Excel file.

Answer 1

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	Excel 2007-2013: - Requires leading UTF-8 BOM Excel 2016+: - Handles BOM-less UTF-8 with some prodding	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 NULLs 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 a SELECT 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 the OPTIONALLY 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 preceding OPTIONALLY keyword) as that will enquote all values, regardless of type (i.e. it will enquote int 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 NULLs 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's datadir directory. Run SHOW 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 per mysqldump'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 with stdout, making it useless for Excel-compatible output. So if you're remote and cannot ssh-in then you will need to use MySQL Shell (mysqlsh) instead (see below).
• Note that mysqldump does not support SELECT queries for dumping data: it does support simple WHERE-style filters with the --where=<expr> option but this doesn't support things like filtering using an INNER JOIN (though as a workaround you could SELECT into a new table, then run mysqldump on that new table. Note that you cannot use TEMPORARY TABLE with mysqldump 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's zsh, and bash on Linux - provided mysqldump is in your PATH.

• 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 ,, and 0x0D0A is \r\n).

• Avoid using the --password=<value> (aka -p<value>) option on the mysqldump 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.

• If you don't specify an absolute path but use a short (unqualified) filename like INTO OUTFILE 'output.csv' or INTO OUTFILE './output.csv' theb it will store the output file to the directory specified by SHOW 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 an ssh session, of course).
  • The new "MySQL X" protocol (mysqlx://user@host/schema) is not supported by MySQL 5.7, but mysqlsh supports non-X connections with old-style command-line parameters.

1. Install MySQL Shell if it isn't already installed.
2. 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 like username%40servername@servername.mysql.database.azure.com).

   • Immediately after you submit the \connect command you will be prompted for your password interactively.

3. If you can start mysqlsh with arguments, then you can run mysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName" directly without using the \connect command.
4. 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 a SELECT query, (though as with mysqldump you could always save your query results to a new TABLE, then export that table, then DROP TABLE when you're done with it. Remember that TEMPORARY TABLE won't work here as tables created in one session aren't visible from any other session - and mysqlsh will have its own session.
   • outputUri: To save the file locally use a file:/// 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.
   • options: To ensure compatibility with Excel specify { dialect: "csv", fieldsEscapedBy: ""}.
     • The dialect: "csv" option sets Excel-compatible defaults for all-but-one of the OUTFILE parameters, so you must also specify fieldsEscapedBy: "", otherwise SQL NULL 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.

Answer 2

Good Example can be when incase of writing it after the end of your query if you have joins or where close :

 select 'idPago','fecha','lead','idAlumno','idTipoPago','idGpo'
 union all
(select id_control_pagos, fecha, lead, id_alumno, id_concepto_pago, id_Gpo,id_Taller,
id_docente, Pagoimporte, NoFactura, FacturaImporte, Mensualidad_No, FormaPago,
Observaciones from control_pagos
into outfile 'c:\\data.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n');

Answer 3

Use the below query:

 SELECT * FROM document INTO OUTFILE 'c:/order-1.csv' FIELDS TERMINATED BY ','  
 ENCLOSED BY '"' LINES TERMINATED BY '\n';

Answer 4

In my case, I need to dump the sql result into a file on the client side. This is the most typical use case to off load data from the database. In many situations, you don't have access to the server or don't want to write your result to the server.

mysql -h hostname -u username -ppwd -e "mysql simple sql statement that last for less than a line" DATABASE_NAME > outputfile_on_the.client

The problem comes when you have a complicated query that last for several lines; you cannot use the command line to dump the result to a file easily. In such cases, you can put your complicated query into a file, such as longquery_file.sql, then execute the command.

mysql -h hn -u un -ppwd < longquery_file.sql DBNAME > output.txt

This worked for me. The only difficulty with me is the tab character; sometimes I use for group_cancat(foo SEPARATOR 0x09) will be written as '\t' in the output file. The 0x09 character is ASCII TAB. But this problem is not particular to the way we dump sql results to file. It may be related to my pager. Let me know when you find an answer to this problem. I will update this post.

Answer 5

The quick and dirty way I use to export mysql output to a file is

$ mysql <database_name> --tee=<file_path>

and then use the exported output (which you can find in <file_path>) wherever I want.

Note that this is the only way you have in order to avoid databases running using the secure-file-priv option, which prevents the usage of INTO OUTFILE suggested in the previous answers:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Answer 6

For SHOW DATABASES and SHOW TABLES

The INTO OUTFILE thing won't work for SHOW TABLES and SHOW DATABASES query.

In that case you could do:

1. SHOW DATABASES

mysql -u <user> -p <password> -e 'SHOW DATABASES' > <path_to_file>

2. SHOW TABLES

mysql -u <user> -p <password> -e 'SHOW TABLES FROM <db_name>' > <path_to_file>

Tip: The -e flag stands for execute

References:

• Show Databases/Tables INTO OUTFILE
• MYSQL --execute=statement

Answer 7

In my case, INTO OUTFILE didn't work as the MySQL user was different than the logged-in user. Also, I couldn't use inline variant as the my query was quite big.

I ended up using this and it was so much easier. This probably won't support CSV or either output, but if you need output as it is, this will work.

mysql> tee /tmp/my.out;

Source: https://alvinalexander.com/mysql/how-save-output-mysql-query-file/

Answer 8

I only have client-side access, but were unable to run util.exportTable, so I used:

mysql --default-character-set=utf8mb4 -uUSERNAME -pSECRET -P3306 -hHOSTNAME -Ddatabase_name -e "SELECT * FROM table_name;" | sed 's/\t/","/g;s/^/"/;s/$/"/;' | sed 's/""""/""/g;' > output.csv

Answer 9

This is an old question, but it's still one of the first results on Google. The fastest way to do this is to link MySQL directly to Excel using ODBC queries or MySQL For Excel. The latter was mentioned in a comment to the OP, but I felt it really deserved its own answer because exporting to CSV is not the most efficient way to achieve this.

ODBC Queries - This is a little bit more complicated to setup, but it's a lot more flexible. For example, the MySQL For Excel add-in doesn't allow you to use WHERE clauses in the query expressions. The flexibility of this method also allows you to use the data in more complex ways.

MySQL For Excel - Use this add-in if you don't need to do anything complex with the query or if you need to get something accomplished quickly and easily. You can make views in your database to workaround some of the query limitations.