phpmyadmin Question posted in
The official documentation can be found here.

How to export a CSV file from the MySQL command line – Phpmyadmin

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 tofile_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 'rn'

but the error remained the same

ERROR: Unknown command '''.
   ->     LINES TERMINATED BY 'rn';

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

Tags:

2

Answers


  1. 0

    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 tofile_name.csv'   
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" ESCAPED BY '\'  
LINES TERMINATED BY 'n'
    Login or Signup to reply.
  2. 0

    Use this 

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

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search