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.

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)

About these ads

4 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