Mysql output data in csv without using outfile in query

Question

In the following command how can i output the data to be separated by comma(i.e, csv) ,I want the output in csv but i do not want to use the into outfile .Any way of doing this by changuing the query,

  mysql -uroot -ppassword -h112.30.16.11 -e 'select * from Employee.conditions' > /home/tom/preweb/static/users/aa.com/output.csv

Possible duplicate of How to output MySQL query results in CSV format? — Daniel
– Daniel, Commented Aug 29, 2016 at 10:12
select * from Employee.conditions INTO OUTFILE '/tmp/output.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; — JYoThI
– JYoThI, Commented Aug 29, 2016 at 10:14
into outfile always generates the output in the same server as that of host. So, if you want the result in a separate server, mysql command is the way to go. — user1741851
– user1741851, Commented Oct 6, 2016 at 4:47

James Scott · Accepted Answer · 2016-08-29 11:05:16Z

2

Try this, note each column name and delimiters will have to be added separately.

mysql -uroot -ppassword -h112.30.16.11 -e 'SELECT CONCAT(col1, ',', col2, ',', col3) FROM agents;' > /home/tom/preweb/static/users/aa.com/output.csv

Update based on comment:

This can be made to by dynamic by using prepared statements, in this case the table name should be added in the first parameter where it says 'table-name'

mysql -uroot -ppassword -h112.30.16.11 -e "SET @VTable = 'table-name'; SET @VAllCols = CONCAT('SELECT CONCAT(',(SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ',\',\',') FROM information_schema.columns WHERE TABLE_NAME = @VTable GROUP BY table_name),') FROM ', @VTable, ';'); PREPARE stmt FROM @VAllCols; EXECUTE stmt; DEALLOCATE PREPARE stmt;" > /home/tom/preweb/static/users/aa.com/output.csv

Let me know if you need anything else like column headings.

Regards,

James

edited Aug 29, 2016 at 11:05

answered Aug 29, 2016 at 10:42

James Scott

1,0821 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rajeev Over a year ago

This works but how to concat in the case of select * from table;

Collectives™ on Stack Overflow

Mysql output data in csv without using outfile in query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related