Mysql – Exported CSV from MySQL doesn’t appear in specified directory

csvexportMySQL

I'm trying to figure out the syntax to export the results of a MySQL query to the desktop as a CSV file via SSH. Here's what I'm trying:

 SELECT * INTO OUTFILE 'C:\Users\Jim Smith\Desktop\zyzyz.csv'
 FIELDS TERMINATED BY ',' 
 OPTIONALLY ENCLOSED BY '\"'  
 LINES TERMINATED BY '\r\n' 
 from <table> limit 5;

The query itself works fine, but the file doesn't appear on the desktop. What should I change to get it to show up?

Best Answer

In windows use \\ or / while using in path of file. your command should be as follow

SELECT * INTO OUTFILE 'C:\\Users\\Jim Smith\\Desktop\\zyzyz.csv' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'
from limit 5;

SELECT * INTO OUTFILE 'C:/Users/Jim Smith/Desktop/zyzyz.csv' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'
from limit 5;

Hope it helps

Related Solutions

How to Import CSV with Multiple Field Separators in MySQL

Since you are not allowed to script but SQL try the following:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\"');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

There you go, another text file that has the doubled double quotes stripped.

If you want all doubled double quotes replaced with single quotes, do this UPDATE:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\'');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

Now, go import that file (improved_importfile.txt) and have fun with it.

Give it a Try !!!

MySQL query with an OUTFILE to csv not working when called from a batch file

I bet that the double quote is the culprit: it terminates the string at ENCLOSED BY ', and the rest derails the whole thing. The solution is very simple: just escape the double quote in your SQL string:

... ENCLOSED BY '\"' ...

(I have to tell that on my local Windows box and psql (instead of mysql) client I don't have to escape the double quote.)

Best Answer

Related Solutions

How to Import CSV with Multiple Field Separators in MySQL

MySQL query with an OUTFILE to csv not working when called from a batch file

Related Question