How can I export MySQL query results to Excel or .TXT files?

Saving MySQL Query Output to Excel or .TXT Files

MySQL offers a convenient method for exporting query results to data files. Users can choose to save these files in either .txt or Microsoft Excel format.

.TXT File Output

MySQL allows for seamless export of query results to .txt files using the INTO OUTFILE extension. With this feature, users can create comma-separated value (CSV) files compatible with spreadsheet applications like Excel.

To export query results to a .txt file, follow these steps:

1. Craft a SELECT statement to retrieve the desired data.
2. Utilize the INTO OUTFILE clause to specify the output file path and modifiers.
3. Execute the query.

For example, to export data from the "orders" table to a file named "/tmp/orders.txt," use the following:

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

This will create a tab-separated file with each row on a separate line.

Excel File Output

MySQL does not directly support exporting data to Excel files. However, users can leverage the .txt file export method and then manually import the CSV file into Excel.

Command-Line Export

Alternatively, users can grab the query output via the client by executing the query and redirecting the output to a local file:

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

This approach allows for exporting query results to either .txt or Excel files.

The above is the detailed content of How can I export MySQL query results to Excel or .TXT files?. For more information, please follow other related articles on the PHP Chinese website!