WLScripting.com » Advanced CSV Export

Advanced CSV Export

In one of my last tutorials I explained how you can export data into a CSV. You can see that tutorial at: Export data to CSV (Comma Separated Value). In this tutorial you will learn more about getting items from a database instead of hand coding the CSV contents.

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

function parseCSVComments($comments) {
  $comments = str_replace('"', '""', $comments); // First off escape all " and make them ""
  if(eregi(",", $comments) or eregi("\n", $comments)) { // Check if I have any commas or new lines
    return '"'.$comments.'"'; // If I have new lines or commas escape them
  } else {
    return $comments; // 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++) {
		$head[] = mysql_field_name($sql, $i); // Create the headers for each column, this is the field name in the database
	}
	$headers = join(',', $head)."\n"; // Make our first row in the CSV

	while($info = mysql_fetch_object($sql)) {
		foreach($head as $fieldName) { // Loop through the array of headers as we fetch the data
			$row[] = parseCSVComments($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.';
}
?>

As you will see in this tutorial I have continued to use the parseCSVComments() function from the previous tutorial. This ensures that your data is going to come out how you want it to appear in the CSV.

The best part about this script is that any table you put in the query on line 14 will be the headers and data in the CSV. No need to hand type out all the table headers unless you want to comment that section out and put in your own. So if you have a table full of users that includes: name, email, website, phone you could create a query like:

SELECT name, email, website, phone FROM users

This would pull only those fields to the CSV if you had other data like passwords, nicknames or instant messenger addresses in the same table.

Enjoy!

(4 votes, average: 4.25 out of 5)

Loading ...

12 Responses to “Advanced CSV Export”

Kelly Says:
February 5th, 2007 at 6:55 pm
COOL
Been looking for this type of php Advanced CSV Export, but I need it to export to a table in a html page every time someone adds to database.
If you have a sample of that it would be great.
Derek Buntin Says:
February 16th, 2007 at 3:48 pm
I would just like to say thanks for this, we used an INNER JOIN on two tables and it exported perfectly
problem Says:
February 28th, 2007 at 2:43 am
i am using it it display data on browser not in neither create csv nor put data even i put log.csv in the same location.still not working can u explain me.
Clive Says:
March 23rd, 2007 at 6:43 am
Fantastic! Just what I was looking for. I’ve simply added in my own database specific info in the first couple of lines and it outputs everything exactly as I wanted. Many thanks!
nichole Says:
April 3rd, 2007 at 6:25 am
thanks this is what i was looking for. but i have one problem. i want to display the header in first two row in log.csv file. i can display the header in single cell but not in merge cell. if you have some idea then it would be great help.
Lou Says:
February 22nd, 2008 at 2:15 pm
Very easy to use, thank you! Two questions. #1. How can I combine two db fields into one csv cell? ex. First_Name and Last_name in the same cell. #2. Is there a way to export by date range instead of the entire db? That way I can select only the past few days I haven’t downloaded instead of the entire db.

Thanks again!
Catherine Says:
May 29th, 2008 at 8:20 am
That works perfectly. Thank you!
demo Says:
July 23rd, 2008 at 6:33 pm
This functionality dosen’t supported by Safari.
nish Says:
September 3rd, 2008 at 8:07 am
Excellent. You don’t know how you have helped me. you have pulled out me from great trouble.

Thanks
CheetahZ Says:
September 3rd, 2008 at 7:38 pm
will this work for PHP 4?
Daniel Says:
September 23rd, 2008 at 9:15 pm
Lou, this is a bit of a late reply but here goes. You will want to look up concat_ws for combining multiple fields. The second thing is that this script allows you to modify the query and export whatever you want with that one SQL query. So depending how your database is structured will depend on what all you query to create your CSV.

demo, I do not have Safari so I am not sure if this works or not. If you are a Safari user and confirm working or not will you post a comment. If it is not and you make some changes to the code please post them and I will modify the code above.

nish, glad to hear. That is what this site is all about.

Cheetahz, This should work with PHP 4.
Scott Says:
October 27th, 2008 at 11:14 am
Daniel,

Thanks for sharing this script!

12 Responses to “Advanced CSV Export”

Leave a Reply