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

User · Answer

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

User · Answer

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

User · Answer

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 lt user gt  -p lt password gt  -h lt host gt  --where jtaskResult 2429 --tab  lt file csv gt   lt database gt  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

User · Answer

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  gt  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  lt  longquery file sql DBNAME  gt  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

User · Answer

Use the below query    SELECT   FROM document INTO OUTFILE  c  order-1 csv  FIELDS TERMINATED BY        ENCLOSED BY     LINES TERMINATED BY   n

User · Answer

The quick and dirty way I use to export mysql output to a file is    mysql  lt database name gt  --tee  lt file path gt   and then use the exported output  which you can find in  lt file path gt   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

[mysql] Exporting results of a Mysql query to excel?

Examples related to mysql

Examples related to excel

Examples related to export