CODEWITHSUNDEEP
mysqlcsvexport
Scott Morrison
Scott Morrison

Reputation: 153

How do I export csv file to my computer in mysql

I am trying to export a table from a remote server to my 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 : Can't create/write to file '/var/lib/mysql/C:\Users\Sleep Shop\Desktop\MySQL Scripts/outfile.csv' (Errcode: 2)

I'm thinking there is something fundamental I don't understand about this procedure, probably something to do the table being at a remote server. Can anyone help?

I used this code to tell a spot 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 the file but it contains no records and I get this error:

failed : Field separator argument is not what is expected;

Upvotes: 4

Views: 7700

Answers (1)

prograhammer
prograhammer

Reputation: 20590

Change the query like this:

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 your remote server's directory here: /var/lib/mysql/export.csv (or possibly /var/lib/mysql/data/your-db-name/export.csv)

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

Or you can use phpMyAdmin and click on the table, then click the "export" tab, and then you will see an option to select "CSV" from the format dropdown. This may not work if your table is too large (depends on phpMyAdmin's settings or PHP's settings on how long a script can run).

Upvotes: 4

Related Questions