MySQL – Export the Resultset to CSV file

In SQL Server, you can use BCP command to export the result set to a csv file. In MySQL too, You can export data from a table or result set as a csv file in many methods. Here are two methods.

Method 1 : Make use of Work Bench

If you are using Work Bench as a querying tool, you can make use of it’s Export option in the result window. Run the following code in Work Bench

SELECT db_names FROM mysql_testing;

The result will be shown in the result windows. There is an option called “File”. Click on it and it will prompt you a window to save the result set (Screen shot attached to show how file option can be used). Choose the directory and type out the name of the file.

MySQL - Export the Resultset to CSV file mysqlexport

Method 2 : Make use of OUTFILE command

You can do the export using a query with OUTFILE command as shown below

SELECT db_names FROM mysql_testing
INTO OUTFILE 'C:/testing.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

After the execution of the above code, you can find a file named testing.csv in C drive of the server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – SSMS: Disk Usage Report
Next Post
Developer’s Life – Every Developer is a Captain America

Related Posts

7 Comments. Leave new

  • Hi guys, while executing query
    ——————————Start—————-
    SELECT * FROM EmployeeMaster
    INTO OUTFILE ‘C:/testing.csv’
    FIELDS ENCLOSED BY ‘”‘ TERMINATED BY ‘;’ ESCAPED BY ‘”‘
    LINES TERMINATED BY ‘rn’;
    —————————————–END—–
    we are getting error
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘INTO’.
    Plz help me what is going wrong..
    Thanks

    Reply
  • dear sir one help. how to backup(godaddy database mssql) to my local system or anoter server.
    thanks
    R.rengaraj

    Reply
  • Shashank Aggarwal
    September 26, 2014 10:34 pm

    hello sir,
    i m getting error “Incorrect Syntax near the keyword ‘into’ ” when i m running command :-

    select * from EMP
    into OutFile ‘F:\sql\output.csv’
    Fields Enclosed By ‘ ” ‘ Terminated by ‘;’ Escaped by ‘ ” ‘
    Lines Terminated by ‘\r\n’

    Reply
  • Incorrect syntax near the keyword ‘INTO’.

    Above error display when i am executing your code.

    Reply
  • Hello Sir,
    When I am executing the above code I am getting “Incorrect syntax near the keyword INTO”

    Reply
  • YEs I am also getting the same errorr

    Reply

Leave a Reply

Menu