Export MySQL Table to a CSV File – Easy Guide

Export MySQL Table To A CSV File

In this tutorial we’ll be learning how to convert a MySQL table to a CSV file, using the MySQL command line and MySQL Workbench.

CSV files or comma-separated value files are simple text files, which store data and separate data from each other using commas and newlines. CSV files are widely used because of their transferability from one application to another, you can open a CSV file using google sheets, MS Excel, SQL, etc. These files are saved with a .csv extension.

Also read: How to Import CSV File in MySQL Database?

Export MySQL table to a CSV file using MySQL Command Line

Step 1: Open MySQL Command Line Client – You can open MySQL command Line by searching for MySQL Command Line Client on the windows menu.

Open MySQL Command Line
Open MySQL Command Line

Otherwise, you can just open cmd and type the following command to connect to MySQL:

MySQL -u localhost -p
Code language: SQL (Structured Query Language) (sql)

Enter the password and you’ll be connected to MySQL.

Step 2: Check if the table which you want to convert to a CSV file exists on the database

To see all the databases

SHOW DATABASES;
Code language: SQL (Structured Query Language) (sql)

To use the database from the list, use the following command:

USE databasename;
Code language: SQL (Structured Query Language) (sql)

To see all the tables present in the database, use the following command:

Code language: SQL (Structured Query Language) (sql)

To see the relation of a particular table, use the following command:

SELECT * FROM tablename;
Code language: SQL (Structured Query Language) (sql)
Sample Table To Csv
Select a Table from Database

Step 3: Execute the query for exporting MySQL table to CSV file

The conversion of files like CSV to MySQL table or vice versa can only be performed by users who have to write privileges, even after that sometimes you might face a problem. The best course of action is that you fire the below command:

SHOW VARIABLES LIKE "secure_file_priv";
Code language: SQL (Structured Query Language) (sql)

This will show you the path of the folder to which you can save your converted CSV file without having the write privilege.

Now, let’s write the command to export the MySQL table to a CSV file.

SELECT * FROM tablename INTO OUTFILE "C:\\ProgramDATA\\MySQL Server 8.0\\Uploads\\filename.csv" FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Code language: SQL (Structured Query Language) (sql)

The path within the quotation mark will have double backslashes, and at the end please mention the filename with .csv extension so that MySQL could create a new file and save the data table there.

Command For Converting MySQL Table To Csv File
Command For Converting MySQL Table To CSV File

Step 4: After executing the above command successfully check the Uploads folder and find if the CSV file has been created or not.

Check The Uploads Folder For CSV File
Check The Uploads Folder For CSV File

This is how you extract a CSV file from the MySQL table using the MySQL command line.

Export MySQL table to a CSV file using MySQL Workbench

Step 1: Open MySQL Workbench and connect to MySQL instance.

Open MySQL Workbench And Connecting To MySQL Instance
Open MySQL Workbench And Connecting To MySQL Instance

Step 2: Open the Schema tab, and select the data and corresponding table you want to convert to a CSV file. Right-click on the table and select the option called “Table Data Export Wizard”.

Click On Table Data Export Wizard
Click On Table Data Export Wizard

Step 3: A Table Data Export window will pop up. In this window, you’ll be given to select the columns you want to include in the CSV file you want to export. By default, all the columns will be selected, if in case you want to exclude any of the columns then you can simply uncheck the corresponding tab and press the next button.

Select Data For Export
Select Data for Export

Step 4: In the next step you need to provide the path where you want to save the application and you’ll be given an option of exporting data as a CSV file or a JSON file, the default will remain CSV let it remain as default, and click on next. Remember at the end of the path you’ve given make sure you’ve mentioned the name of the CSV file you want to create with the .csv extension.

Select Output File Location
Select Output File Location

Step 5: This is the final step to export a table, Export Data, you will be prompted with two activities that MySQL will perform, Prepare Export and Export Data to file. You will be provided with the Show Logs button on the left, if you want to see the logs while the process occurs you can click on that otherwise simply click on next.

Export Data
Export Data

Step 6: In this tab, you’ll be prompted to show you the export result. You can see the path from here if you don’t remember and click on Finish.

Export Result
Export Result

Step 7: Open the folder you mentioned in the path, and you’ll be able to see the CSV file you just exported from MySQL.

Check For The CSV File
Check For The CSV File

These were the two ways through which you can export any table from MySQL and convert it into a CSV file.

Conclusion

Exporting MySQL databases and tables to CSV files is a great way to back up your data. It is also a convenient way to move your data to another system. By following the steps in this article, you can easily export your MySQL data to CSV files.

Useful Resource:

https://dev.mysql.com/doc/workbench/en/wb-admin-export-import.html