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.
You can using any MySQL command line tool to do this by including the INTO OUTFILE clause on your SELECT statement:

SELECT ... FROM ... WHERE ... 
INTO OUTFILE 'file.csv'
FIELDS TERMINATED BY ','

See this link for detailed options.

Alternatively, you can use mysqldump to store dump into a separated value format using the --tab option, see this link.

mysqldump -u<user> -p<password> -h<host> --where=jtaskResult=2429 --tab=<file.csv> <database> TaskResult

Hint: If you don't specify an absoulte path but use something like INTO OUTFILE 'output.csv' or INTO OUTFILE './output.csv', it will store the output file to the directory specified by show variables like 'datadir';.

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

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.