4

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
Rajeev
  • 44,985
  • 76
  • 186
  • 285
  • Possible duplicate of [How to output MySQL query results in CSV format?](http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format) – Daniel Aug 29 '16 at 10:12
  • 1
    select * from Employee.conditions INTO OUTFILE '/tmp/output.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; – JYoThI Aug 29 '16 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. –  Oct 06 '16 at 04:47

1 Answers1

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

James Scott
  • 1,032
  • 1
  • 10
  • 17