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.

mysqlexport MySQL   Export the Resultset to CSV file

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 (http://blog.sqlauthority.com)

6 thoughts on “MySQL – Export the Resultset to CSV file

  1. Hi guys, while executing query
    ——————————Start—————-
    SELECT * FROM EmployeeMaster
    INTO OUTFILE ‘C:/testing.csv’
    FIELDS ENCLOSED BY ‘”‘ TERMINATED BY ‘;’ ESCAPED BY ‘”‘
    LINES TERMINATED BY ‘\r\n’;
    —————————————–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

    Like

  2. 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’

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s