Menu
How do I store a MySQL query result into a local CSV file? I don't have access to the remote machine.
346
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.
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.
Click Query, and then click Results to File. Enter and then execute the SQL statement. In the Save Results dialog box, specify the following settings: Save In: Select a directory in which to save the file.
You're going to have to use the command line execution '-e' flag.
$> /usr/local/mysql/bin/mysql -u user -p -h remote.example.com -e "select t1.a,t1.b from db_schema.table1 t1 limit 10;" > hello.txt Will generate a local hello.txt file in your current working directory with the output from the query.
153
We can use command line execution '-e' flag and a simple python script to generate result in csv format.
create a python file (lets name = tab2csv) and write the following code in it..
#!/usr/bin/env python import csv import sys tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab) comma_out = csv.writer(sys.stdout, dialect=csv.excel) for row in tab_in: comma_out.writerow(row) Run the following command by updating mysql credentials correctly
mysql -u orch -p -h database_ip -e "select * from database_name.table_name limit 10;" | python tab2csv > outfile.csv Result will be stored in outfile.csv.
44
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
