Friday, March 29, 2024
HomeHow ToExport a MySQL Table to a CSV File Methods Overview

Export a MySQL Table to a CSV File Methods Overview

To perform successful export of a MySQL table to a CSV file you will need the dbForge Studio for MySQL database management tool. You can download it for free and evaluate its features during a 30-day trial period.

A comma-separated values (CSV) file is a simple file format that is widely supported, so it is often used to move tabular data between different computer programs that support the format. CSV file is a text format for a database table. Each record in the table is one line of the text file. Each field value of a record is separated from the next with a comma. For example, a CSV file might be used to transfer information from a database to a spreadsheet or another database. Of course, there are more advanced formats to store data, for example, XML, but CSV does have one advantage over XML. CSV has much lower overhead, thereby using much less bandwidth and storage than XML.

Now let’s analyse how one can save data from a MySQL table to a CSV file.

The first and very likely the easiest way is to change Storage Engine to CSV and save it to some archived file with such name as table_name.CSV. To do this it is necessary to execute:

ALTER TABLE table_name ENGINE=CSV;

If we execute such statement for the sakila.actor table, we will instantly get the “Cannot delete or update a parent row: a foreign key constraint.” message.
OK, let’s delete all foreign keys referencing this table and execute the DDL statement once more. Now we will get the “The used table type doesn’t support AUTO_INCREMENT columns.” message.
Let’s remove the AUTO_INCREMENT property from the actor_id column and try to execute our statement again. Now we get the “Too many keys specified; max 0 keys allowed” message.
So we should delete all indexes, unique keys, and primary keys, and only after this you get a CSV file you can work with on the server machine.
As we can see, this is not a very easy way of saving MySQL table data to the CSV format, because we had to ruin referential integrity of our database and make too many changes to the schema created in the first place. The second shortcoming is that it is impossible to save N number of rows.

The second way does not have the shortcomings listed above. To perform the requested operation, you should execute:

SELECT * INTO OUTFILE 'sakila_actor.csv' FROM sakila.actor;

But by default the field delimiter is TAB, data is saved without quoting, and information regarding date and time is saved in an unusual format. That’s why it is necessary to customize the settings in the following way:

SELECT actor_id, first_name, last_name, DATE_FORMAT(last_update,'%m/%d/%Y %H:%i:%s')
INTO OUTFILE 'sakila_actor.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n'
FROM sakila.actor
LIMIT 10;

Excellent! Now we can save the needed fields and the needed number of table rows.
And this way also has some shortcomings. For example, it is impossible to execute this statement several times. In this case the “File ‘sakila_actor.csv’ already exists” error arises. But the common shortcoming of both these ways is that the file is always saved on the server side. Not all users that have permissions to select data have access to the file system.

We offer you a way that doesn’t have any shortcomings mentioned above.

Let’s see how easy it is just to export data from a MySQL table to the CSV format and save the resulting file locally using a dbForge Studio for MySQL.

Select the needed table in Database Explorer and select where to export data from the popup menu.

MySQL Data Export: Context Menu
MySQL Data Export: Context Menu

After that in Data Export Wizard that appeared select the needed format – CSV.

MySQL Data Export Wizard: CSV Format
MySQL Data Export Wizard: CSV Format

On the next page of the wizard we can set the column separator and quote string we need, but as the preset variant is suitable for us, we go to the next page of the wizard.

MySQL Data Export Wizard: Column Separator and Quote String
MySQL Data Export Wizard: Column Separator and Quote String

On the Data Format page you can set the format in which integer and float numbers, empty strings, date and time should be saved, but as the preset format is suitable for us, we go to the next page of the wizard.

MySQL Data Export Wizard: Date and Time Format
MySQL Data Export Wizard: Date and Time Format

On the next page we can limit the number of exported rows and press the data export button.

MySQL Data Export Wizard: Range of Rows
MySQL Data Export Wizard: Range of Rows

OK! The data was successfully exported to a CSV file. If you want, you can save the settings specified earlier as a template and use them when exporting data from this or any other table next time.

MySQL Data Export Wizard: Export Finish and Save Template
MySQL Data Export Wizard: Export Finish and Save Template

Of course, it is not necessary to perform all these tiresome steps and it is possible to click the export button on the first page of the wizard.

Our tool supports command line in the 5 version of dbForge Studio for MySQL to save export project and to start export of data from the needed tables and in the set cyclic periods of time or in the specified time with the help of Windows Scheduler.

What do you think about the ways of data export described in this article? How do you usually export data?

RELATED ARTICLES

4 COMMENTS

  1. Hi,
    You can also:
    * mysqldump –tab=/path/for/export
    This still means you have to invoke it locally (ie on the server side), but it solves the “single file” problem

    Or, you can:
    * mysql -h remote_host -e “SELECT * FROM my_schema.my_table” –batch –silent > my_file.csv
    Which solves all aforementioned problems.

    Regards

  2. Every GUI client I can think of have similar client-side functionalities for exports (to CSV and other formats).

  3. The latest version of dbForge Studio for MySQL Express (which is free) supports command-line exports to CSV. You can string together several lines in a batch file to export multiple tables. Thank you dbForge!

Comments are closed.

Whitepaper

Social

Topics

Products