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,500 points
881 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,720 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,720 points
1,001 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,720 points
259 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,720 points
3,746 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,720 points
843 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,720 points
13,992 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,720 points
1,574 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,720 points
1,299 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,720 points
18,485 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,720 points
6,308 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,720 points
2,343 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP