How do I export a selected column in MySQL?

For example, you have a users table that has has more than two columns (firstname, lastname, username, password, etc.) and you only want to export the user’s names and usernames.

In short you are exporting only two columns from your users table, and PhpMyAdmin can help us with that.

Here’s how to do that:

1. Find you table and to to structure tab. In this example, we have “coan” database and “users” table.

2. Select two columns you want to export and click “Browse” (see red arrow below). We selected the “firstname” and “username”.

3. Now you can export the selected columns by clicking “Export” (see red arrow) below the page.

4. Now you can “quick export” it. An SQL file will be downloaded, containing your exported column data.

You can play around with “custom” export if you want, there will be options like entering specific number of rows you want to be exported.

This command will show you the directory that has been configured. You can only store your output file in this directory.

Step 2

• Select all the data from the table and specify the location of the output file.

TABLE tableName 
INTO OUTFILE 'path/outputFile.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY 'n';

• Make sure to use the .csv extension for your output file.
• The ORDER clause can be used to arrange the data according to a particular attribute.
• The LIMIT clause is used to restrict the number of rows to be copied into the output file.

a. Exporting Selected Columns of a Table

• To do this you can use the SELECT statement to specify the columns you want to export.
• You may additionally use the WHERE clause to use specific conditions and filter the results.

SELECT columnName, ….
FROM tableName
WHERE columnName = 'value';

b. Exporting Tables with a Timestamp

You may want to add a timestamp to the exported file, to do that you must use a MySQL prepared statement.

Use the following command to export to a CSV file, and add a timestamp for the time the file was created:

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = '/var/lib/sql-files/';
SET @PREFIX = 'employees';
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;

c. Export with Column Headers

It is often convenient to add column headers to the output file to better identify and analyze the data. To do this, you must use the UNION statement.
Use the following command to add column headers:

(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')

d. Handling NULL Values

If your results contain NULL values, they will appear as ‘N’ in the exported file instead of NULL. This may lead to confusion and thus, you may want to replace this ‘N’ string with a string like NA (not applicable) that makes more sense.
Use the following command to do it:

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

2. Using mysqldump

mysqldump is a utility tool provided by MySQL server that enables users to export tables, databases, and entire servers. Moreover, it is also used for backup and recovery.
Here, we will discuss how mysqldump can be used to export a MySQL table to CSV.

• Use the following command in a command prompt/terminal:

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

• The given command will create a copy of the table specified by tableName at the location you define using the -T option.
• The name of the file will be the same as that of the table and will have a .txt extension.

3. Using MySQL Workbench

MySQL Workbench provides an Import/Export Wizard which allows you to export our database/ tables to a specified format using a graphical user interface. The wizard supports JSON and CSV formats.

To download MySQL Workbench, click here. 

You can follow the given steps to export your MySQL table using MySQL Workbench:

Step 1

• Use the left bar “schemas” tab to locate the table you want to export.

In this example, we will be exporting the employee’s table in the classic model’s database.

Step 2

• Right-click using your mouse on the table and select “Table Data Export Wizard” to get the following screen.
• Select the columns you want to export.

Step 3

• Click on Next.
• Browse to the directory where you want to save the output file.
• Choose the CSV format option.

Step 4

• Click on Next.
• Your data will start exporting.
• You can track the process through the logs.

 4. Using phpMyAdmin

phpMyAdmin provides a graphical user interface to export your MySQL table in different formats. Apart from CSV, it supports other formats such as XML, JSON, YAML, and many others.

To download phpMyAdmin, click here.

To use phpMyAdmin to export data, follow these steps:

Step 1

• Log in to phpMyAdmin using a user that has required permissions.
• Navigate to the database which contains the source table as shown.

Step 2

• Choose the table from the database.
• Click on Export in the top bar.

Step 3

• Choose the CSV format from the format dropdown menu.

• Click on Go.
• Select the save file option when prompted.

5. Using CSV Engine

The CSV storage engine stores data in text files using comma-separated values format and is always compiled into the MySQL server.
It is important to note that this method can only be used if the table does not have an index or an AUTO_INCREMENT constraint.

ALTER TABLE tableName ENGINE=CSV;

This command changes the format of the database to CSV. It can then directly be copied to another system easily.

Conclusion

You now have 5 methods to export your MySQL table to CSV in your arsenal. If you are comfortable with writing queries using the command-line or mysqldump utility tool will prove to be the simplest way. However, if you are not confident with your querying skills, MySQL Workbench and phpMyAdmin will be your best bet. Extracting complex data from a diverse set of data sources such as MySQL can be a challenging task and this is where Hevo saves the day!

Hevo offers a faster way to move data from 100+ data sources such as SaaS applications or Databases such as MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Have you used any of these methods? Have any further queries? Reach out to us in the comments section below.

How to export only one column in MySQL?

How to export selected data from MySQL?

How do I export a column from a database?

How do I select a specific column of data in MySQL?