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

Saving MySQL Query Results to Excel or Text File

To save the output of a MySQL query to a Microsoft Excel sheet or a text file, MySQL provides the INTO OUTFILE command. This command allows you to write the results of a SELECT statement directly to a file on the server.

Creating a Tab-Separated Text File

To export your query results as a tab-separated text file, execute the following SQL statement:

<code class="sql">SELECT order_id, product_name, qty
INTO OUTFILE '/tmp/orders.txt'
FIELDS TERMINATED BY '\t';</code>

This will create a file named orders.txt in the /tmp directory with tab-separated values for each field in the query results.

Creating a Comma-Separated Text File (CSV)

To create a comma-separated text file, modify the INTO OUTFILE statement as follows:

<code class="sql">SELECT order_id, product_name, qty
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ',';</code>

This will generate a CSV file named orders.csv in the /tmp directory.

Enclosing Values in Double Quotes

You can optionally enclose field values in double quotes by adding the ENCLOSED BY clause:

<code class="sql">SELECT order_id, product_name, qty
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"';</code>

This will produce a CSV file where each field is enclosed in double quotes.

Redirecting Results to a Local File

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

<code class="bash">mysql -u username -p password -h hostname -D database_name -e "SELECT order_id, product_name, qty" > output.txt</code>

This will export the query results to the file output.txt.

Note:

• The output file should not exist before running the INTO OUTFILE command.
• The MySQL user must have write permissions to the directory where the file will be saved.

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