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

// 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:

// 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.

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.

// 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);

Related posts:

  1. How to create a zip file using PHP
  2. How to sort a multi-dimension array by value in PHP
  3. How to hide Nginx version number in headers and errors pages
  4. How to get size of BLOB in MySQL

14 thoughts on “How to create CSV file using PHP”

  1. Great script, thanks. The only issue I get is that fputcsv add double quote to some data and I need to get rid of those chars. Any hint?

  2. How can I just create a row of data from a form and add that row to a fixed file on the server. Each time a form is filled another row of data goes to the server – no headers.

    Thanks

  3. Mate, I really owe you one! None the less I still have a question. Could you probably show me how one would initialize the download? Meaning, how would I program the button for the User to use and how would I link it with the action you provided?

Leave a Reply