How to Export MySQL Query Results to Excel or Text Files?

Saving MySQL Query Output to Excel or Text Files

Question: How can I export the results of a MySQL query to an Excel spreadsheet or a text file?

Answer:

MySQL provides a convenient method to store query results as a text file on the server. By utilizing extended options of INTO OUTFILE, you can generate comma-separated value (CSV) files that can be imported into spreadsheet applications like Excel or OpenOffice.

Syntax:

SELECT Your_Column_Name
FROM Your_Table_Name
INTO OUTFILE 'Filename.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

For example, to save the results of a query that fetches the order_id, product_name, and qty columns from the orders table:

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

This will create a CSV file with tab-separated values, where each row occupies a separate line. To customize the output, modify the options as follows:

• FIELDS TERMINATED BY ...: Specify the delimiter for separating fields (e.g., ',' for comma-separated).
• ENCLOSED BY ...: Surround each field with the specified character (e.g., '"' for double quotes).
• LINES TERMINATED BY ...: Define the line break character (e.g., 'n' for new lines).

Alternative Method:

You can also redirect the query output from your local client to a file:

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

This will execute the query and save the results to the specified file. Remember to use an absolute path for file storage or specify the directory using show variables like 'datadir';.

The above is the detailed content of How to Export MySQL Query Results to Excel or Text Files?. For more information, please follow other related articles on the PHP Chinese website!