Monday, February 04, 2013

Creating Downloadable CSV Files Using PHP


CSV (comma-separated values) is the most widely supported format for transferring tabular data between applications. The ability to export data in CSV format is a useful feature for many programs, and is becoming increasingly common in web applications. This page explains how to use PHP to create CSV files, and how to ensure that your visitor’s browser offers to download the file instead of displaying it.

The code

The following code assumes that the data to be exported are stored in a MySQL database, but it can easily be modified to work with other data sources, and hence serves as a general template:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
// output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('Column 1', 'Column 2', 'Column 3'));

// fetch the data
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');
$rows = mysql_query('SELECT field1,field2,field3 FROM table');

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);
The string ‘data.csv’ on line 3 is the name the browser will suggest for the downloaded file. The array literal on line 9 contains the column headings; this line should be omitted altogether if you do not wish to include column headings. The strings on lines 12 to 14 should be changed to the appropriate connection parameters and query string for your database. In a real application the database connection parameters should be defined as constants in a separate configuration file.

How it works

The code needs to tell the browser that it is producing a CSV file and that the file should be offered for download, rather than displayed in the browser. Line 2 produces an HTTP header telling the browser that the MIME type of the content is text/csv, the official MIME type for CSV files.
Line 3 produces a ‘Content-Disposition’ header. The Content-Disposition header was defined in RFC 2183 and was intended to be used in e-mails, but was also adopted by web browsers. In web browsers the value ‘attachment’ instructs the browser to offer the file for download with the suggested name — the file might otherwise be displayed in the browser window as if it were normal text.
PHP 5.1 introduced the fputcsv function, which outputs an array of data to a file pointer as comma-separated values. Because fputcsv can only write to file pointers, line 6 opens the output stream as if it were a file. Line 9 then outputs column headers, while line 17 loops over the rows of data and outputs them.

No comments: