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.
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:
To see the relation of a particular table, use the following command:
SELECT * FROM tablename;Code language: SQL (Structured Query Language) (sql)
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.
Step 4: After executing the above command successfully check the Uploads folder and find if the CSV file has been created or not.
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.
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”.
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.
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.
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.
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.
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.
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