How to Save MySQL Query Output to Excel or .txt File?

Saving MySQL Query Output to Excel or .txt File

To save the output of a MySQL query to a Microsoft Excel or .txt file, you can utilize the following methods:

Using INTO OUTFILE

MySQL provides the INTO OUTFILE syntax to easily export query results to a text file on the server. It allows you to create comma-separated value (CSV) files suitable for import into spreadsheets.

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

This query will generate a tab-separated file with each row on a separate line. You can modify the output format by setting field terminators, enclosures, and line separators.

Using a Client Output Redirection

Alternatively, you can execute the query from your local client and redirect the output to a local file:

mysql -user -pass -e "select cols from table where cols not null" > /tmp/output

This approach captures the query output to a local file named "output" in the "/tmp" directory.

Tips

• Ensure that the output file does not exist before executing the query.
• MySQL must have write permissions to the directory where the file is being saved.
• If an absolute path is not specified, the output file will be stored in the directory specified by SHOW VARIABLES LIKE 'datadir';.

The above is the detailed content of How to Save MySQL Query Output to Excel or .txt File?. For more information, please follow other related articles on the PHP Chinese website!