CODEWITHSUNDEEP
mysql
Rajeev
Rajeev

Reputation: 46909

Mysql output data in csv without using outfile in query

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

Upvotes: 4

Views: 2618

Answers (1)

James Scott
James Scott

Reputation: 1074

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

Upvotes: 2

Related Questions