How to export csv file to my computer in mysql

Question

I am trying to export a table from a remote server to a desktop computer in csv format. I have this code:

select * from order into outfile 'C:\Users\Sleep Shop\Desktop\MySQL Scripts/outfile.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'; 

but I get this error:

failed: cannot create / write to file '/ var / lib / mysql / C: \ Users \ Sleep Shop \ Desktop \ MySQL Scripts / outfile.csv' (error code: 2)

I think that there is something fundamental that I do not understand in this procedure, probably something that needs to be done for a table located on a remote server. Can anybody help?

I used this code to indicate the location on the server to create the file:

 select * from orders into outfile '/var/www/test/outfile.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'; 

It creates a file but contains no entries, and I get this error:

fail: The field separator argument is not as expected;

Answer 1

Modify the query as follows:

 select * from `order` into outfile 'export.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; 

Then you will find the file in the remote server directory here: /var/lib/mysql/export.csv (or perhaps / var / lib / mysql / data / your -db-name / export.csv)

Connect to your server via SSH ( use putty ) and transfer the file to your computer or move the file to a directory that accepts FTP access, and you can download it using an FTP client (i.e. filezilla, winSCP).

Or you can use phpMyAdmin and click on the table and then click on the “Export” tab, and then you will see the “CSV” option in the format drop-down list. This may not work if your table is too large (depends on phpMyAdmin settings or PHP settings on how long the script can run).