Export table to CSV in MySQL
Exporting data from MySQL tables to CSV (Comma Separated Values) files is a common requirement when sharing or analyzing data in other applications or systems. MySQL provides efficient ways to export tables to CSV. Let's see various methods of how we can export data from MySQL data to CSV e.g.
Using the
SELECT…INTO OUTFILEstatementUsing Microsoft SQL Server Management Studio (SSMS)
Using the
mysqldumpcommandUsing a Stored Procedure
Identify the table to export
First of all, let's create a table from which we want to export data in CSV format.
Example
Assume we have a table called employees in the company database that we want to export to CSV. We will create the table and insert some data into the table to use it further for testing purposes.
CREATE TABLE employees (id INT,name VARCHAR(255),salary DECIMAL(10,2));INSERT INTO employees (id, name, salary) VALUES(1, 'John Doe', 5000.00),(2, 'Jane Smith', 6000.50),(3, 'Michael Johnson', 4500.75),(4, 'Emily Davis', 5500.25),(5, 'Robert Wilson', 7000.00),(6, 'Sophia Brown', 4800.50),(7, 'Daniel Taylor', 5200.75),(8, 'Olivia Clark', 6100.25),(9, 'James Martinez', 4800.00),(10, 'Emma Anderson', 5300.50),(11, 'William Moore', 6900.75),(12, 'Ava Thomas', 4800.25),(13, 'Joseph Lee', 5700.00),(14, 'Mia Rodriguez', 4900.50),(15, 'David Harris', 5500.75),(16, 'Abigail Jackson', 5900.25),(17, 'Andrew White', 5400.00),(18, 'Charlotte Miller', 6200.50),(19, 'Daniel Harris', 4900.75),(20, 'Ella Taylor', 5800.25),(21, 'Matthew Wilson', 4700.00),(22, 'Avery Brown', 5400.50),(23, 'Sofia Johnson', 6700.75),(24, 'Jack Davis', 4600.25),(25, 'Harper Smith', 5100.00),(26, 'Logan Clark', 6300.50),(27, 'Grace Martinez', 4700.75),(28, 'Jackson Anderson', 5200.25),(29, 'Madison Moore', 6300.00),(30, 'Lucas Thomas', 6000.50);
Run the SELECT query
To export the table data to CSV, you can execute a SELECT query to retrieve the desired data. This query will generate the dataset that will be exported to the CSV file.
Example
SELECT * FROM employees;
Exporting data
Let's now move on to the methods of exporting data from MySQL tables to CSV. We will explore four methods one by one.
Using the SELECT…INTO OUTFILE statement
The SELECT...INTO OUTFILE statement allows us to write the results of a query directly into an output file. Here’s how you can use it to export a table to CSV:
SELECT *INTO OUTFILE '/path/to/outputfile.csv'FIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n'FROM employees;
Explanation
The
INTO OUTFILEclause specifies the path and filename of the output CSV file.The
FIELDS TERMINATED BY ','indicates that commas will separate the fields in the CSV.The
ENCLOSED BY '"'ensures that any field containing special characters (e.g., commas or line breaks) will be enclosed within double quotes.The
LINES TERMINATED BY '\n'sets the line terminator to a newline character.
Using Microsoft SQL Server Management Studio (SSMS)
MySQL Workbench provides a graphical interface to export tables to CSV format. Here’s how to do it:
Open MySQL Workbench and connect to your database server.
In the Navigator pane, click on the Schema you’re working with to expand its tables.
Right-click on the table you want to export and select “Select top 1000 rows”.
If your table has more than 1000 rows, execute the query given below:
SELECT * FROM table_name;
In our case, it will be
SELECT * FROM employees;
This will give us all rows of our table.
Now, right-click anywhere on the output given below, and select "Select All". This will select our complete table.
Right-click anywhere on the selected data in the "Result Grid" and select "Save Results AS.."
In the "Save Results" dialog box that pops up, you can choose the export format. Select "CSV Files (*.csv)" from the "Save as type" drop-down menu.
Navigate to the folder where you want to save the exported CSV file and provide a file name for the CSV file in the "File name" field. Click the "Save" button to begin the export process.
Navigate to the folder where you saved the CSV file and open it using a text editor or spreadsheet software like Microsoft Excel or Google Sheets.
Using the mysqldump command
The mysqldump command-line utility can also be used for exporting specific tables to CSV format.
mysqldump -u your_username -p your_database_name your_table_name --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' > outputfile.csv
Explanation
The
-uspecifies the MySQL username.The
-pprompts for the MySQL password.The
your_database_nameis the name of the database containing the table.The
your_table_nameis the name of the table you want to export.The
--fields-terminated-by=','sets the field separator to a comma.The
--fields-enclosed-by='"'encloses fields containing special characters within double quotes.The
--lines-terminated-by='\n'sets the line terminator to a newline character.The
> outputfile.csvredirects the output to the specified CSV file.
Using a stored procedure
If you find yourself exporting tables to CSV frequently, you can create a stored procedure to streamline the process:
DELIMITER //CREATE PROCEDURE ExportTableToCSV()BEGINSET @output_file = '/path/to/outputfile.csv';SET @query = CONCAT("SELECT * INTO OUTFILE '", @output_file, "' FIELDS TERMINATED BY ',' ENCLOSED BY '\\"' LINES TERMINATED BY '\\\n' FROM your_table_name;");PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END //DELIMITER ;
Explanation
We define a stored procedure named
ExportTableToCSV.The
@output_filevariable holds the path and filename for the CSV file.The
@queryvariable is used to build the dynamic SQL query.The
PREPARE,EXECUTE, andDEALLOCATE PREPAREstatements help execute the dynamic query.
Conclusion
Exporting tables to CSV in MySQL is important for data manipulation and sharing. In this Answer, we covered four methods to accomplish this task. These include using SQL statements, Microsoft SQL Server Management Studio (SSMS), command-line tools, and creating a stored procedure for convenience.
