How to output MySQL query results in CSV format?

Question

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?

Here's what I'm doing now:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

Answer 1

To output MySQL query results in CSV format from the Linux command line, you can modify your query by adding the INTO OUTFILE 'filename.csv' FIELDS TERMINATED BY ',' clause. Optionally, you can include ENCLOSED BY '"' and ESCAPED BY '\\' clauses to enclose fields in double quotes and escape any quotes within the results, respectively. After making these modifications, execute the query, and the results will be written to the specified file in CSV format. Remember that the file will be created on the server, so ensure that the user has the necessary permissions to write files.

Answer 2

To output MySQL query results in CSV format...

SELECT order_id,product_name,qty+

FROM orders 

WHERE foo = 'bar' 

INTO OUTFILE '/var/lib/mysql-files/orders.csv' 

FIELDS TERMINATED BY ','

ENCLOSED BY '"' 

LINES TERMINATED BY '\n';

We are using this command but their column name will not be exported.

Check out our SQL Course to have a complete grip on SQL concepts.

Answer 3

I face the same When I use the `INTO OUTFILE` syntax to export query output to file, But i got this:

MySQL [mydb]> select name, phone from user INTO OUTFILE '/temp/myoutput.txt';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Finally, I used https://tableconvert.com/mysql-to-csv to convert mysql query output to CSV.

Also, you can also use this tool to convert to JSON, Excel, or even Markdown.

Answer 4

You can output MySQL query results in CSV format by using the SELECT ... INTO OUTFILE statement in MySQL.

Start by running the MySQL query from the Linux command line using the mysql command, providing the appropriate credentials (-u for the username, -p for the password) and specifying the database (-D for the database name).

To output the results in CSV format, modify your query as follows:

Add the INTO OUTFILE 'filename.csv' clause at the end of your query, where filename.csv is the desired name of the output file.

Use the FIELDS TERMINATED BY ',' clause to specify that the fields should be separated by commas.

Optionally, you can use the ENCLOSED BY '"' clause to enclose fields in double quotes.

If needed, you can use the ESCAPED BY '\\' clause to escape any quotes within the results.

After modifying your query, execute it, and the query results will be written to the specified file in CSV format.

Keep in mind that the output file will be created in the server's file system, not the local file system.

Make sure the user executing the query has the necessary permissions to write files on the server.

Answer 5

Modify your MySQL query by adding INTO OUTFILE 'filename.csv' FIELDS TERMINATED BY ',' to output results in CSV format.