Export MySQL Query Result to CSV file with headers

Introduction

CSV file format is widely used for reading data from a file and loading it into the system. It is a simple text format, comma-separated text file, lightweight, and can be opened in tools like Microsoft Excel for reading. So, when we generate output from the database using SQL query, we often need to store the output results in a CSV file. The SQL query could be on a single table or multiple tables using joins. Here, we will discuss exporting data in a CSV file with headers using MySQL phpMyAdmin.

phpMyAdmin Export utility and MySQL Queries

phpMyAdmin gives many utilities to perform various database operations. It is the GUI for a MySQL database. The Export utility in phpMyAdmin is one feature that provides exporting data into different file formats. You can use SQL, XML, CSV, PDF, etc.; phpMyAdmin also has an SQL editor window, where you can write SQL queries and execute them. It gives the query results and you can store them in CSV format.

In the above picture, on the left side, we have selected a database called "demo" and a table called "items" under this database. On the right side, you can see the SQL window and the Export option.

We will see below two ways to export data in CSV format from MySQL database:

1. Export table data using phpMyAdmin Export utility.
2. Export the output of a MySQL query into a CSV file by using one or more tables with joins.

1. Export table data using phpMyAdmin Export utility.

Step 1: Open phpMyAdmin by entering localhost/phpmyadmin in your browser. Select a database and a table you want to export in CSV. For example, I have selected the "demo" database and the table I want to export is "category". See below:

The table has four rows.

Step 2: Click on Export and select Format as CSV from the dropdown as shown below:

Now scroll down and click on Export.

It will create a CSV file in the downloads folder or ask for the path to save the file. Give a path and save the file. You can double-click the CSV file and open it in Excel.

If you open it in Notepad, you will see the text data written in the CSV file using comma-separated format.

2. Export the output of a MySQL query into a CSV file by using one or more tables with joins

We will create two tables, the "category" table and the "items" table. below are the structures of these two tables

Table: category

Table: items

Below are the table creation scripts with data to be inserted into the tables:

CREATE TABLE `category` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'Furniture'),
(2, 'Electronics'),
(3, 'Dress Material'),
(4, 'Food');

CREATE TABLE `items` (
  `item_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `item_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `items` (`item_id`, `category_id`, `item_name`) VALUES
(1, 1, 'Table'),
(2, 1, 'Chair'),
(3, 1, 'Shelf'),
(4, 1, 'Sofa'),
(5, 1, 'Bed'),
(6, 2, 'TV'),
(7, 2, 'Mobile'),
(8, 2, 'Laptop'),
(9, 2, 'Digital, Clock'),
(10, 2, 'Microwave'),
(11, 3, 'Shirts'),
(12, 3, 'Tee'),
(13, 3, 'Trousers'),
(14, 3, 'Shorts'),
(15, 3, 'Jeans'),
(16, 4, 'Noodles'),
(17, 4, 'Chocolate'),
(18, 4, 'Ice Cream'),
(19, 4, 'Tea'),
(20, 4, 'Coffee');

ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`);

ALTER TABLE `category`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `items`
  ADD PRIMARY KEY (`item_id`);

ALTER TABLE `items`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

We can join these two tables on "category_id" column. We will select all categories and all the items under each category. See the below SQL queries and the output:

SELECT a.category_name, b.item_name
from category a, items b
where a.category_id=b.category_id
order by category_name,item_name;

Modify this query to export to CSV file

We need to give a CSV file name with a path. Make sure you have the write privilege in the specified folder and check that the same file does not already exist. In the following case, I have used a path d:/sql folder and the file name is "items.csv". Note that the query has an "INTO OUTFILE" clause with the path.

  SELECT a.category_name, b.item_name
  INTO OUTFILE 'd:/sql/items.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  from category a, items b
  where a.category_id=b.category_id
  order by category_name,item_name;

It will write the output in the file with each field separated by a comma (FIELDS TERMINATED BY clause), if the content itself has a comma in it, then it will enclose the data with a double-quote (OPTIONALLY ENCLOSED BY clause) and there will be a line separator for each line (LINES TERMINATED BY clause)

If you run this, it will generate a CSV file in the above folder. Contents of the file are given below:

You can see that the output is stored in a CSV file and we can view it in Excel. Again, if you open it in Notepad, you will see comma-separated text data written in the file.

How to get the column headers in the CSV file

You might have already noticed that it does not write the column headers in the file. To show the column headers, modify the query to use UNION. Select the column headers first and then the content using UNION ALL in one subquery, use that as a table alias T1 and then select columns from T1.

  (SELECT 'category_name', 'item_name'
  UNION ALL
  SELECT a.category_name, b.item_name
  FROM category a, items b
  WHERE a.category_id=b.category_id
  )
  INTO OUTFILE 'd:/sql/items.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

After running this query, we get the result in a CSV file and the output is given below, you can see the column headers now:

But the problem is that ORDER BY will not work here. You might use the Excel sort function to sort the data. It depends on your requirements. However, complex queries could be written or even a small PHP program can write sorted data in a CSV file with headers. You might check if it is worth spending time on that according to your requirements.

Another point to remember is that UNION ALL can highly degrade performance if the data volume is high in the table.

For a single table, it is easier to use the direct Export option as discussed earlier. Note that the Export utility always gives column headers in the CSV file. But data might not be sorted as per your need.

Write a Simple PHP program to create a CSV file

Just another option, sometimes it could even be easier to write a simple PHP program to select the data using query and then write in a file.

1. Connect to a MySQL database
2. Open a file with extension .csv for writing using PHP function fopen()
3. Write the column headers in the file using fputcsv() function
4. Write the SELECT query using single or multiple tables with joins and ORDER BY, whatever way you want the output.
5. Execute the query
6. Using a loop, fetch each row and create an array of all selected column values.
7. Write the array to the file using fputcsv() function
8. After the loop ends close the file
9. Your file is ready with headers and sorted as expected

Here is the PHP code:

  $conn = mysqli_connect("localhost","root", '', 'demo');
  if (!$conn) 
      die("Connection Error: " . mysqli_connect_error());
  // open file for writing
  $fp = fopen('employees.csv', 'w') or die("Unable to open file!");

  $col_header = array("Category Name","Item Name");
  fputcsv($fp,$col_header); // write column headers

  $sql = "SELECT a.category_name, b.item_name from category a, items b where a.category_id=b.category_id order by category_name,item_name";
  $result = mysqli_query($conn,$sql);  // execute the query

  if (mysqli_num_rows($result) > 0){
     foreach ($result as $row){
        $line = array($row['category_name'],$row['item_name']); // array of values
        fputcsv($fp,$line); // write to csv file
      }
    }
  fclose($fp);

Conclusion

SQL query output in a CSV file is frequently used in the project; Because it is easier to view the output in Excel where we can also use various Excel functions on the data. We have discussed how to get the SQL query output in a CSV file using phpMyAdmin in MySQL. I have used the query joining two tables to show that you can get the data in a CSV file from multiple tables. If you have only one table, your SQL query will change. I hope you will find the topic useful.