Creating a CSV file with PHP

While working on several coding projects I find myself needing to export data in CSV format. All this basically means is a text file with many lines of text separated by commas(,) which can then be opened up in a spreadsheet application or imported into another program. So for an example you have the following saved in a file called export.csv:

first value, second value, third value
fourth value, fifth value, sixth value

Every value before a comma is a new cell in the spreadsheet and everything after each new line is the start of a new row. Which means that the above values would produce something like the following in a spreadsheet application:

first value second value third value
fourth value fifth value sixth value

To accomplish this in PHP you can use arrays, joins and a few header functions to download the file. You could also save this output to a file instead of forcing the browser to download the content.

<?php
$row = array();
$row[] = 'first value';
$row[] = 'second value';
$row[] = 'third value';
$data .= join(',', $row)."\n"; // Join all values without any trailing commas and add a new line

$row = array(); // We must clear the previous values
$row[] = 'fourth value';
$row[] = 'fifth value';
$row[] = 'sixth value';
$data .= join(',', $row)."\n";

// Output the headers to download the file
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=log.csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $data;
?>

Notice the join function adding the commas between the values and then finally adding the new line with the \n before finally outputting the data as a downloadable file.

Now you may be noticing one problem with the above code. Sure the code works but what happens if there is a comma, quote or new line in one of the $row variables? If you tried to put a comma between the first and value in our first value like:

first, value, second value, third value
fourth value, fifth value, sixth value

You would get:

first value second value third value
fourth value fifth value sixth value

Obviously not the output we were looking for. To fix this you have to escape the commas and new lines with quotes like:

"first, value", second value, third value
fourth value, fifth value, sixth value

Now if you are wanting to use quotes in your CSV file you have to escape those with double quotes such as:

"""first"" value", second value, third value
fourth value, fifth value, sixth value

Now that you know the above information about escaping values you can use the below function to escape all of the values before joining them with commas.

<?php
/*
Created by: Daniel Kassner
Website: http://www.danielkassner.com
*/
function escape_csv_value($value) {
	$value = str_replace('"', '""', $value); // First off escape all " and make them ""
	if(preg_match('/,/', $value) or preg_match("/\n/", $value) or preg_match('/"/', $value)) { // Check if I have any commas or new lines
		return '"'.$value.'"'; // If I have new lines or commas escape them
	} else {
		return $value; // If no new lines or commas just return the value
	}
}
?>

Usage:

<?php
$SafeValue = escape_csv_value('your value here');
?>

Now you may also be wondering about an easy way to export data straight from your MySQL database into a CSV file for download. Using some basic PHP functions for working with databases we can easily create a new export based on the query you run. To change the export just change the query that you run.

<?php
$db = mysql_connect('localhost', 'user', 'password'); // Connect to the database
$link = mysql_select_db('database name', $db); // Select the database name

/*
Created by: Daniel Kassner
Website: http://www.danielkassner.com
*/
function escape_csv_value($value) {
	$value = str_replace('"', '""', $value); // First off escape all " and make them ""
	if(preg_match('/,/', $value) or preg_match("/\n/", $value) or preg_match('/"/', $value)) { // Check if I have any commas or new lines
		return '"'.$value.'"'; // If I have new lines or commas escape them
	} else {
		return $value; // If no new lines or commas just return the value
	}
}

$sql = mysql_query("SELECT * FROM tablename"); // Start our query of the database
$numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching

if($numberFields) { // Check if we need to output anything
	for($i=0; $i<$numberFields; $i++) {
		$keys[] = mysql_field_name($sql, $i); // Create array of the names for the loop of data below
		$head[] = escape_csv_value(mysql_field_name($sql, $i)); // Create and escape the headers for each column, this is the field name in the database
	}
	$headers = join(',', $head)."\n"; // Make our first row in the CSV
	
	$data = '';
	while($info = mysql_fetch_object($sql)) {
		foreach($keys as $fieldName) { // Loop through the array of headers as we fetch the data
			$row[] = escape_csv_value($info->$fieldName);
		} // End loop
		$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row
		$row = ''; // Clear the contents of the $row variable to start a new row
	}
	// Start our output of the CSV
	header("Content-type: application/x-msdownload");
	header("Content-Disposition: attachment; filename=log.csv");
	header("Pragma: no-cache");
	header("Expires: 0");
	echo $headers.$data;
} else {
	// Nothing needed to be output. Put an error message here or something.
	echo 'No data available for this CSV.';
}
?>

Please note that the above code also puts in a header row of what each of the columns is based on the database table fields. You can change what the headings are in your MySQL query by using the SQL as syntax. Examples:

SELECT fname, minitial, lname FROM users

Could be changed to:

SELECT fname as 'First Name', minitial as 'Middle Initial', lname as 'Last Name' FROM users

This would produce the desired output quickly and easily with a single MySQL query.

Enjoy working with CSV files that are properly escaped.

  • Facebook
  • Digg
  • del.icio.us
  • Google Bookmarks
  • BlinkList
  • FriendFeed
  • LinkedIn
  • MySpace
  • Slashdot
  • StumbleUpon
  • Twitter
  • Yahoo! Bookmarks
  • Add to favorites
  • email

5 Responses to “Creating a CSV file with PHP”

  1. Barbara S. Martinez Says:

    i like your blog and article.thanks and bookmark it


  2. Patrick Ng Says:

    Hi ya, Great post!
    Just having problem to integrate more than one sql query into the script to be exported

    Any ideas?


  3. Meenakshi Says:

    Thanks,
    It helped me a lot


  4. Michael Says:

    Many thanks Daniel, this is a brilliant script!!!


  5. kurtis holsapple Says:

    this works awesome!

    thanks yo!


Switch to our mobile site