PHP to Excel help

[coupe-r]

Hi All,

 

I am trying to export a table into excel and it is working, kind of..

 

I'm trying to import data that has commas in it.  Unfortunately, it looks like this "PHP to CVS" process is comma delimited.

 

How can I get around that, while having the data in the correct columns?

 

Here is my code:

$file = 'Notes_Export';
$csv_output = '';


$sql = "SHOW COLUMNS FROM prop_notes WHERE Field IN ('note', 'created_by', 'created_on', 'updated_by', 'updated_on')";
	$result = mysqli_query($connect, $sql);

$i = 0;
		if (mysqli_num_rows($result) > 0)
		{
				while ($row = mysqli_fetch_assoc($result))
				{
					$csv_output .= $row['Field'].", ";
					$i++;
				}
		}

$csv_output .= "\n";

$sql = "SELECT note, created_by, created_on, updated_by, updated_on FROM prop_notes";
	$values = mysqli_query($connect, $sql);

		while($rowr = mysqli_fetch_row($values))
		{
				for ($j=0;$j<$i;$j++)
				{
					$csv_output .= $rowr[$j].", ";
				}
			$csv_output .= "\n";
		}


$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
// header( "Content-disposition: filename=".$filename.".csv");
header("Content-disposition: attachment; filename=".$filename.".csv");
print $csv_output;
exit;

[kenrbnsn]

If you have commas in the data, you can do 1 of two things:

• Put double quotes around the data
  
• Use a different separator character and then specify that character when importing the data into Excel
  

 

Ken

 

[coupe-r]

Hi and thanks Ken.

 

Could you give me an example of how to specify a new delimited character?

[kenrbnsn]

Here's one way of surrounding the data with double quotes:

<?php
$file = 'Notes_Export';
$csv_output = array();


$sql = "SHOW COLUMNS FROM prop_notes WHERE Field IN ('note', 'created_by', 'created_on', 'updated_by', 'updated_on')";
	$result = mysqli_query($connect, $sql);

$i = 0;
		if (mysqli_num_rows($result) > 0)
		{
			  tmp = array();
				while ($row = mysqli_fetch_assoc($result))
				{
					$tmp[] = $row['Field'];
					$i++;
				}
		}

$csv_output[] = '"' . implode('","', $tmp) . '"';

$sql = "SELECT note, created_by, created_on, updated_by, updated_on FROM prop_notes";
	$values = mysqli_query($connect, $sql);

		while($rowr = mysqli_fetch_row($values))
		{
				$tmp = array();
				for ($j=0;$j<$i;$j++)
				{
					$tmp[] = $rowr[$j];
				}
			$csv_output[] = '"' . implode('","', $tmp) . '"';
		}


$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
// header( "Content-disposition: filename=".$filename.".csv");
header("Content-disposition: attachment; filename=".$filename.".csv");
print implode("\n",$csv_output) . "\n";
exit;
?>

 

And here's a way to use a different separator character (you need to pick one that doesn't appear in your data, such as a ~, `, or |)

<?php
$file = 'Notes_Export';
$csv_output = array();


$sql = "SHOW COLUMNS FROM prop_notes WHERE Field IN ('note', 'created_by', 'created_on', 'updated_by', 'updated_on')";
	$result = mysqli_query($connect, $sql);

$i = 0;
		if (mysqli_num_rows($result) > 0)
		{
			  tmp = array();
				while ($row = mysqli_fetch_assoc($result))
				{
					$tmp[] = $row['Field'];
					$i++;
				}
		}

$csv_output[] = implode('|', $tmp);

$sql = "SELECT note, created_by, created_on, updated_by, updated_on FROM prop_notes";
	$values = mysqli_query($connect, $sql);

		while($rowr = mysqli_fetch_row($values))
		{
				$tmp = array();
				for ($j=0;$j<$i;$j++)
				{
					$tmp[] = $rowr[$j];
				}
			$csv_output[] = implode('|', $tmp);
		}


$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
// header( "Content-disposition: filename=".$filename.".csv");
header("Content-disposition: attachment; filename=".$filename.".csv");
print implode("\n",$csv_output) . "\n";
exit;
?>

 

Ken

[coupe-r]

Perfect.  That first code works great!!!!

 

Thank you for taking your time and helping me, Ken.

[kenrbnsn]

No problem. Just make sure you understand how & why the code works.

 

Ken