MySQL – Export the Resultset to CSV file
- by Pinal Dave
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)Filed under: MySQL, PostADay, SQL, SQL Authority, SQL Query, SQL Tips and Tricks, T SQL Tagged: CSV
