CODEWITHSUNDEEP
mysqlsqlselectxamppinto-outfile
jasw
jasw

Reputation: 33

How to export a CSV file from the MySQL command line

I have a MySQL database containing a single table which is used as a temporary storage point for manipulating and querying data sets and is periodically deleted and replaced with new data. What I would like to be able to do is export the data from the MySQL command line to use for other purposes. I am using the XAMPP Apache server package with phpMyAdmin on Windows 10.

The issue I am having is the INTO OUTFILE syntax I am using returns an error relating to '\n'. Below is an example of the syntax:

SELECT *  
FROM tablename  
WHERE work_complete = 'Yes' 
INTO OUTFILE 'C:\file path for the file to be exported to\file_name.csv'   
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "''"   
ESCAPED BY '\'  
LINES TERMINATED BY '\n';

I have spent some time researching this without any luck, I have even tried using 

LINES TERMINATED BY '\r\n'

but the error remained the same

ERROR: Unknown command '\''.
   ->     LINES TERMINATED BY '\r\n';

If anyone could provide any tips that would be greatly appreciated.

Upvotes: 0

Views: 6712

Answers (2)

GMB
GMB

Reputation: 222432

The problem is not in LINES TERMINATED BY but in ESCAPED BY.

This:

ESCAPED BY '\'

Is invalid syntax, because the backslash is interpreted as an escape character for the following quote. A decent text editor should let you see that.

You need to escape the backslash, like so:

ESCAPED BY '\\'

Alternatively, you can also use '\b':

ESCAPED BY '\b'

Another probem is that OPTIONALLY ENCLOSED accepts only a single character, while you are giving it two single quotes.

In your query:

SELECT *  FROM tablename WHERE work_complete = 'Yes' 
INTO OUTFILE 'C:\file path for the file to be exported to\file_name.csv'   
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" ESCAPED BY '\\'  
LINES TERMINATED BY '\n'

Upvotes: 1

nbk
nbk

Reputation: 49373

Use this 

SELECT *  
FROM tablename  
#WHERE work_complete = 'Yes' 
INTO OUTFILE 'C:\file path for the file to be exported to\file_name.csv'   
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

I used some other syntax as you and deleted the OPTIONALLY which mysql doesn't like at that place

Upvotes: 1

Related Questions