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:

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.

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:

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:

Related Posts

This entry was posted
on Thursday, December 31st, 2009 at 8:00 pm and is filed under PHP Coding, Technology, Web Development.
You can follow any responses to this entry through the RSS 2.0 feed.
Both comments and pings are currently closed.