MySQL Export Table to CSV

Export Table Into CSV Format Using MySQL Workbench

If you don't want to connect to the database server in order to export the CSV file, MySQL offers another option: MySQL Workbench. Workbench is a graphical user interface (GUI) tool for working with MySQL databases without having to use a command-line tool. It allows us to save a statement's result set as a CSV file on our local machine. To do so, we'll need to take the following steps:

Step 1). Navigator -> user -> Tables. Choose the table and then right-click and select Table data export wizard.

Step 2). Now select the columns required to import. 

Step 3). Feed the path where you want your CVS file to the workbench and click next then finish. 

Step 4). After finishing the process you will get the CSV file at the desired location. 
 

Exporting Table Using Command Line Client

It's really simple to export a MySQL table to CSV from the command line. There is no need to install any other program. 

The default variable secure_file_priv stores the location of every exported file in MySQL. To get the default path of an exported file, run the command below.

It will yield the following result after execution, where we can see this path: The default file location is C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/. When running an export command, this path will be used.

We must edit the my.ini configuration file to adjust the default export destination of the CSV file indicated in the secure file priv setting. This file can be found on the Windows platform at the following location: C:\ProgramData\MySQL\MySQL Server X.Y.

After creating the database and creating the table we need to specify the columns that we need to export and the table name. 

In the next line, we need to enter the address where we need the CSV file. 

The code for the following is:

SELECT column_names FROM table_name
INTO OUTFILE 'path_of_the_location'
FIELDS ENCLOSED BY '"'
TERMINATED BY';'
ESCAPED BY '"'
LINES TERMINATED BY ‘\r\n’;;

Hey fellows, In the code stated above the first command states the table name along with the name of the database used. In the second command, the path of the location where the file is to be exported must be stated. After that, we need to state the symbols that are to be used in the file that is exported to our chosen path. 

An example of such a command is given below.

Now you can check the given location in your file manager and you will end up getting the required CSV file.  

Your file will have the data in the first column separated by commas. 

And there are some additional ways to export the CSV file more precisely which are explained further below: 

1. Exporting selected columns of the table
2. Export with Column Headers
3. Exporting tables with a timestamp
4. Dealing with NULL Values

Exporting Selected Columns Of A Table

You may accomplish this by specifying the columns you wish to export using the SELECT query. You can also use the WHERE clause to filter the results by specifying certain constraints.

SELECT column1, column2, column3, column4
FROM tableName
WHERE column2 = 'value';

Substitute the names of the columns you wish to export for column1 (and the rest). Make sure you indicate the table you're exporting from with the FROM command. The WHERE clause is an optional clause that allows you to export just rows with a certain value. 'Value' should be replaced with the value you want to export.

Note: The WHERE clause could be helpful for filtering results.

Export With Column Headers

We sometimes need to export data with column headers to make the file more convenient. If the column titles appear on the first line of the CSV file, the exported file is easier to interpret. Using the UNION ALL command, we can add the column heads as follows:

(SELECT 'columnHeading', ...)
UNION
(SELECT column, ...
FROM tableName
INTO OUTFILE 'path-to-file/outputFile.csv'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY 'n')

Exporting Tables With A Timestamp

If you wish to include a timestamp in your exported file, you'll need to use a MySQL prepared statement.

To export to a CSV file, use the following command and include a timestamp for when the file was created:

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = '/path to file/';
SET @PREFIX = ‘tablename’;
SET @EXT    = '.csv';
SET @CMD = CONCAT("SELECT * FROM tableName INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '"
' TERMINATED BY ',' 
ESCAPED BY '"'",
"LINES TERMINATED BY 'n';");
PREPARE statement FROM @CMD;
EXECUTE statement;

Note: Take note of the SELECT * FROM command within the parenthesis. We've enclosed the command in a function that includes a timestamp.

Dealing With NULL Values

If you have NULL values in your results, they will display in the exported file as 'N' instead of NULL. This may cause confusion, therefore you might wish to replace the 'N' string with a more logical string like NA (not applicable) using the IFNULL function.

To do so, type the following command:

SELECT column1, column2, IFNULL(column3, 'NA')
FROM tableName INTO OUTFILE 'path-to-file/outputFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

Export MySQL To CSV Using Mysqldump

MySQL server provides the mysqldump utility tool, which allows users to export tables, databases, and complete servers. It can also be used for backup and recovery.

Now, we'll look at how to use mysqldump to export a MySQL table to CSV.

mysqldump -u [username] -p -t -T/path/to/directory [database] [tableName] --fields-terminated-by=,

• The above command will make a copy of the table as tableName at the location indicated by the -T option.
• The file's name will be the same as the table's, and it will have an a.txt extension.

Note: One needs to note that access to the directory where the CSV file is kept must be granted to your user account. You can save to a different location, but it must be one to which MySQL has complete access.

Export MySQL To CSV Using CSV Engine

In some cases, you can alter the table using MySQL's CSV engine. The CSV storage engine is always built into the MySQL server and saves data in text files using the comma-separated value format.

It's worth noting that this approach can only be used if the table lacks an index or an AUTO_INCREMENT constraint.

ALTER TABLE tableName ENGINE=CSV;

This command converts the database to CSV format. It may then be readily transferred to another system.

Refer to know about :  Update Query in MySQL

FAQs

1. What is the format of a CSV file?
   A comma-separated values (CSV) file is a text file with values separated by a comma. A data record is represented by each line in the file. Each record has one or more fields, which are separated by commas or any other character. The name for this file format comes from the use of the comma as a field separator.
    
2. What is the meaning of a CSV header?
   The CSV file's header is an array of values assigned to each column. It serves as the data's row header. A header is appended to the data frame once the CSV file is transformed to a data frame. The data frame's contents are written back into the CSV file.
    
3. Which file types can be converted to CSV?
   The software that can open CSV files can convert them to a variety of different file formats. Microsoft Excel, for example, can import data from CSV files and export it as XLS, XLSX, PDF, TXT, XML, and HTML files.
    
4. In MySQL, what is indexing?
   Indexes are used to quickly locate records with certain column values. MySQL must start with the first row and then scan through the entire table to find the relevant rows if there is no index. The larger the table, the higher the price. For these operations, MySQL employs indexes: To quickly identify the rows that match a WHERE clause.
    
5. What is the best way to export data from SQL Server to a CSV query?
   First and foremost, open SQL Server Management Studio and connect to the database. Step 2: In Object Explorer, look for the database you wish to export data from in CSV format. Step 3: Right-click on the database you want to export data from, then select Tasks >> Export Data.

Key Takeaways

There are now four options for exporting your MySQL table to a CSV file. If you have access to MySQL Workbench, it is by far the most user-friendly tool. The SELECT INTO command is the most reliable technique if you're working from a command-line interface. In some cases, the CSV Engine or mysqldump methods can also save a significant amount of time.

Recommended Read: pwd command in linux

After you've learned how to export a MySQL table to a CSV file, the next step is to learn how to import a CSV file into a MySQL database. So, click here to learn how to do that.

Hope you learned something. But the knowledge never stops, so to better understand the Database management system, you can go through many articles on our platform. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Happy Learning Ninja :)