How do I export csv file to my computer in mysql

Question

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;

Is it this directory exist in your pc ? or you have enough space ? — Ala Alam Falaki
– Ala Alam Falaki, Commented May 22, 2013 at 16:34
How are you accessing the remote server? Using ssh/putty? The remote server is thinking the file path you have given is located somewhere on it's own drive. Notice the error is pointing to a linux location "/var/lib/...". You need to let the server write the CSV file to a valid location on it's own drive, then you can transfer the file to your local PC. — prograhammer
– prograhammer, Commented May 22, 2013 at 16:38
have you tried changing the path to C:/Users/Sleep Shop/Desktop/MySQL Scripts/outfile.csv ? — MISJHA
– MISJHA, Commented May 22, 2013 at 16:42
This directory exists on my pc, I have plenty of space. I am accessing the server through phpMyAdmin. — Scott Morrison
– Scott Morrison, Commented May 22, 2013 at 16:46
I thought it may be a path problem, ok so I will attempt to write to a directory there, is there a resource you can point me to that tells me how to transfer a file? — Scott Morrison
– Scott Morrison, Commented May 22, 2013 at 16:47

Community · Accepted Answer · 2017-03-20 10:04:20Z

4

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).

edited Mar 20, 2017 at 10:04

CommunityBot

11 silver badge

answered May 22, 2013 at 16:59

prograhammer

20.7k13 gold badges95 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Scott Morrison Over a year ago

Thanks David, I've done something like that and can create a file in a specified directory, but it's empty, see update above.

Scott Morrison Over a year ago

Sorry David, I get this error from it:Failed to execute SQL : SQL select * from order into outfile 'export.csv' FIELDS TERMINATED BY ',' ENCLOSED BY " ESCAPED BY '\\' LINES TERMINATED BY '\n'; failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order into outfile 'export.csv' FIELDS TERMINATED BY ',' ENCLOSED BY " ESCAPED B' at line 1

prograhammer Over a year ago

"order" is a reserved word, enclose it inside backticks (see updated query). dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Scott Morrison Over a year ago

I copy and pasted your code, this is what I got: Failed to execute SQL : SQL select * from order into outfile 'export.csv' FIELDS TERMINATED BY ',' ENCLOSED BY " ESCAPED BY '\\' LINES TERMINATED BY '\n'; failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '" ESCAPED BY '\\\\' LINES TERMINATED BY '\\n'' at line 1 *the tick backs are there they just don't show up in this comment.

prograhammer Over a year ago

ok, sorry, put the '"' back for enclosed by: ENCLOSED BY '"' The query now works for me, I just tried it.

|

Collectives™ on Stack Overflow

How do I export csv file to my computer in mysql

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related