How to create CSV file using PHP

CSV (comma-separated values) is one of the most popular methods for transferring tabular data between applications. Lot of applications want to export data in a CSV file. In this article we will see how we can create CSV file using PHP. We will also see how to automatically download the file instead of just showing it in the browser or giving the user a link to download it.

Creating a CSV file using static data

If you want to store the data into a csv file, then you can use the code similar to the following

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

// open the file "demosaved.csv" for writing $file = fopen('demosaved.csv', 'w');   // save the column headers fputcsv($file, array('Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5'));   // Sample data. This can be fetched from mysql too $data = array( array('Data 11', 'Data 12', 'Data 13', 'Data 14', 'Data 15'), array('Data 21', 'Data 22', 'Data 23', 'Data 24', 'Data 25'), array('Data 31', 'Data 32', 'Data 33', 'Data 34', 'Data 35'), array('Data 41', 'Data 42', 'Data 43', 'Data 44', 'Data 45'), array('Data 51', 'Data 52', 'Data 53', 'Data 54', 'Data 55') );   // save each row of the data foreach ($data as $row) { fputcsv($file, $row); }   // Close the file fclose($file);

Explanation of code

• Line 2 opens a file with filename as “demosaved.csv” for writing. Make sure the location in which you are saving this file is writeable by your server
• Line 5 is to add the column headers. You can skip this if you don’t want any column headers
• Lines 8-14 are the data that is to be stored in the file. If you want to fetch the data from mysql, you will have to replace these lines with code to connect to the database and get the data (see below)
• Lines 16-20 goes through each row of data and outputs them
• Line 23 closes the file after we are done writing all the data to the file

Creating and automatically downloading a CSV file using static data

If instead of saving the file you want to directly download it in the browser, then use the code similar to the following:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

// output headers so that the file is downloaded rather than displayed header('Content-type: text/csv'); header('Content-Disposition: attachment; filename="demo.csv"');   // do not cache the file header('Pragma: no-cache'); header('Expires: 0');   // create a file pointer connected to the output stream $file = fopen('php://output', 'w');   // send the column headers fputcsv($file, array('Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5'));   // Sample data. This can be fetched from mysql too $data = array( array('Data 11', 'Data 12', 'Data 13', 'Data 14', 'Data 15'), array('Data 21', 'Data 22', 'Data 23', 'Data 24', 'Data 25'), array('Data 31', 'Data 32', 'Data 33', 'Data 34', 'Data 35'), array('Data 41', 'Data 42', 'Data 43', 'Data 44', 'Data 45'), array('Data 51', 'Data 52', 'Data 53', 'Data 54', 'Data 55') );   // output each row of the data foreach ($data as $row) { fputcsv($file, $row); }   exit();

Explanation of code

Since we want to download the file we will have to send some headers (lines 2 & 3) to tell the browser to download the file.

• Line 2 tells the browser the MIME type of the content. This is text/csv since we are going to send a csv file
• Line 3 sends a “Content-Disposition” header with value as “attachment” and filename as “demo.csv”. This will tell the browser to download the file with the name as “demo.csv” instead of displaying it
• Line 6 & 7 tells the browser not to cache the file. This is useful if we are sending multiple files of the same name, else this is not required
• Line 10 is to open the file pointer to send the data
• Rest of the code is similar to above

Downloading an existing file

If you have already saved the csv file on server and just want to automatically download it, then use the following code instead of lines 12-28 of example 2, where $filename will the be the name of the file that you want to output.

1	readfile($filename);

Getting the data from mysql

If you want to use mysql to get the data then use the code similar to following instead of lines 8-20 and 16-30 in the 1st and 2nd example respectively. Make sure to change the connection parameters and the query for your database. Also, the following database related code is only an example. In a real application it might in a different file and there should also be error handling.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

// Open the connection $link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');   //query the database $query = 'SELECT field1, field2, field3, field4, field5 FROM table';   if ($rows = mysqli_query($link, $query)) { // loop over the rows, outputting them while ($row = mysqli_fetch_assoc($rows)) { fputcsv($file, $row); } // free result set mysqli_free_result($result); } // close the connection mysqli_close($link);