How to output MySQL query results in CSV format

0 votes

Is there an easy way to run a MySQL query from the window 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

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.

Aug 18, 2020 in PHP by kartik
• 37,520 points
2,563 views

1 answer to this question.

0 votes

Hello @kartik,

Using this command columns names will not be exported.

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';

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

Hope it helps!!
Thank you!!

answered Aug 18, 2020 by Niroj
• 82,840 points

Related Questions In PHP

0 votes
1 answer

How to convert PHP code to MySQL query to CSV?

Hello @kartik, Try this: SELECT * INTO OUTFILE "c:/mydata.csv" FIELDS ...READ MORE

answered Aug 20, 2020 in PHP by Niroj
• 82,840 points
3,094 views
0 votes
1 answer

How to get database structure in MySQL via query

Hello @kartik, Use this: DESCRIBE table; You can also use SHOW ...READ MORE

answered Aug 26, 2020 in PHP by Niroj
• 82,840 points
874 views
0 votes
1 answer

How to order results with findBy() in Doctrine

Hii @kartik, Try out this code $ens = $em->getRepository('AcmeBinBundle:Marks')               ...READ MORE

answered Apr 20, 2020 in PHP by Niroj
• 82,840 points
10,356 views
0 votes
1 answer

How to convert from MySQL datetime to another format with PHP?

Hello, To convert a date retrieved from MySQL ...READ MORE

answered May 19, 2020 in PHP by Niroj
• 82,840 points
3,523 views
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,840 points
23,012 views
0 votes
1 answer

What is redirection in Laravel?

Named route is used to give specific ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,840 points
2,995 views
0 votes
1 answer

How to install Laravel via composer?

Hello, This is simple you just need to ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,840 points
2,918 views
+1 vote
1 answer

What are named routes in Laravel and How can specify route names for controller actions?

Hey @kartik, Named routing is another amazing feature of ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,840 points
44,692 views
0 votes
1 answer

How to convert timestamp to date in MySQL query?

Hello @kartik, Try this: DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS ...READ MORE

answered Aug 19, 2020 in PHP by Niroj
• 82,840 points
7,873 views
0 votes
1 answer

How to save MySQL query output to excel or .txt file?

Hello @kartik, You can write following codes to ...READ MORE

answered Aug 20, 2020 in PHP by Niroj
• 82,840 points
3,952 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP