Menu
My application runs on server 1 and DB is on server 2.I want to get the output of select query as a CSV to server 1 i.e. my application server.How can I achieve this. Select into outfile wont help because it dumps only on the local server so if I run query on DB server it would create the file on it not the application server. mysql -e option also doesent help because it does not dump as CSV. Could anyone suggest how to directly create file locally as CSV from remote server? Thanks.
471
Select the table of the database that you want to export and click on the Export tab from the right side. Select the CSV format from the Format drop-down list and click on the Go button. Select the Save File option and press the OK button. The file will be downloaded in the Downloads folder.
use the command insert...select to create a "result" table. The ideal scenario whould be to automatically create the fields of this result table, but this is not possible in mysql. create an ODBC connection to the database. use access or excel to extract the data and then save or process in the way you want.
You may use outfile and afterwards ftp the file to the local host or pass the result of a normal query to some sed/awk to convert it to csv?
What I found is:
mysql -umysqlusername -pmysqlpass databasename -B -e "select * from \`tablename\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysql_exported_table.csv
Besides that, we're going with the (s)ftp solution.
70
Newer versions of the MySQL clients (tested on 14.14) on Linux use tab delimiters by default when redirecting stdout to a file:
mysql -h serverhostname -uuserid -ppassword databasename -e 'select * from mytbale order by update_ts desc' >output.tsv
Most apps that accept csv will accept tsv, or you should be able to convert easily yourself, see How do I convert a .tsv to .csv?.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
