*nix: utility to export mysql query result to Exel (xls)

Question

Is there any console utility which can export mysql sql query result to Exel (xls) file?

Answer 1

You can use this syntax:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

It will create CSV file with the result of your query that can be imported into Excel.

Answer 2

You can use mysqldump to output CSV that Excel will understand.

mysqldump -u<user> -p --tab=<path> --fields-enclosed-by=\" --fields-terminated-by=, <database>

There are some caveats though, from memory:

• It will use OUTFILE so the client must be located on the same machine as the server.
• The user that mysqld is running as will need write permissions to the output path.
• You may want to re-chown the output files.
• You'll need to rename the output files from .txt to .csv.
• It will output .sql files as well. You can delete these.

If it's something you wish to do regularly, rather than a one off, then it would be preferable to script without the use of mysqldump. I have created a tool that we use internally that makes use of Python's MySQL and CSV modules, which works without the caveats above.

Answer 3

Maybe not the answer you would expect, but at May's NLUUG conference, I saw a presentation from the developer of libferris. At the time I thought it a hobby project, more than a project having real technical uses, but over time I changed my opinion.

The whole idea is to mount (yes, mount) your database as a filesystem and then manipulate your data through that. The developer mentioned specifically that it would be possible to use the mounted MySQL database, completely readable and writable, through Excel.

Might be a tad too much though. You should look into exporting to csv (easily scriptable) and importing that into Excel.