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');