Page 1 of 1
Exporting to Excell problem
Posted: Mon Jun 18, 2007 5:43 pm
by reyes99
Hi
I am trying to export a table to excel but I have two comment fields and when the users enter a comment they put commas and other punctuation. The problem I am having is that I need to clean up the comment fields so they will export correctly. I tried using mysql_real_escape_string but I dont think it is working.
Here is my code:
<center><h1><img border="0" src="banner.gif" width="805" height="70"></h1>
<h1>Survey Results</h1></center>
Code: Select all
<?php
require 'config.php';
require 'opendb.php';
$fp = fopen('file.csv','wa');
$q = 'select * from survey';
$query = mysql_query($q);
while ($row = mysql_fetch_array($query)) {
$nextline = $row[0] . ',' . $row[1] . ',' . $row[2] . $row[3] . ',' . $row[4] . ',' . $row[5] . ',' . $row[6] . ',' . $row[7] . ',' .
$row[8] . ',' . $row[9] . ',' . $row[10] . ',' . $row[11] . ','. $row[12] .',' .mysql_real_escape_string($row[13]) . ',' .mysql_real_escape_string($row[14]) . "\r\n";
fwrite($fp,$nextline);
}
fclose($fp);
Posted: Mon Jun 18, 2007 5:45 pm
by superdezign
Maybe you could escape the commas? I'm not sure, but I think that's how you'd do it.
Posted: Mon Jun 18, 2007 5:50 pm
by Gente
Also you can try to use TAB as separator.
Posted: Mon Jun 18, 2007 6:04 pm
by reyes99
ok that worked. I was able to remove the commas but now the ? are giving me problems.
Can I remove the commas and the ? using the same command??
Thanks
Posted: Tue Jun 19, 2007 9:56 am
by reyes99
I am new to php and I am not sure if this is correct. I wrote this function to try to clean up my comment fields that has ? and , and it is causing problems when I try to convert the file to csv file.
Please tell me if this is correct or if there is a better way to do this??
Code: Select all
<center><h1><img border="0" src="banner.gif" width="805" height="70"></h1>
<h1>Survey Results</h1></center>
<?php
function cleancomm($comm)
{
$comm1 = str_replace('?', ' ', $comm);
$comm2 = str_replace(',', ' ', $comm1);
$comm2 = $comm;
return $comm;
}
require 'config.php';
require 'opendb.php';
$fp = fopen('file.csv','wa');
$q = 'select * from survey';
$query = mysql_query($q);
while ($row = mysql_fetch_array($query)) {
$nextline = $row[0] . ',' . $row[1] . ',' . $row[2] . $row[3] . ',' . $row[4] . ',' . $row[5] . ',' . $row[6] . ',' . $row[7] . ',' .
$row[8] . ',' . $row[9] . ',' . $row[10] . ',' . $row[11] . ','. $row[12] .',' . cleancomm($row[13]) . ',' . cleancomm($row[14]) . "\r\n";
fwrite($fp,$nextline);
}
fclose($fp);
?>
Posted: Tue Jun 19, 2007 10:05 am
by Gente
Look attentive at your function first
Code: Select all
function cleancomm($comm)
{
$comm1 = str_replace('?', ' ', $comm);
$comm2 = str_replace(',', ' ', $comm1);
$comm2 = $comm;
return $comm;
}
Everything else seems to be ok
Posted: Tue Jun 19, 2007 1:24 pm
by reyes99
I am not sure if it is correct. What I was trying to do is do the first pass and clean it and store it to $comm1 is should remove the ? marks but it's not.
Then I wanted to do a second pass and remove the ,'s from $comm1 that iis already clean of ? marks
then I want to return the cleaned $comm2 to the row.
I know there must be something wrong because it is not cleaning the fields. But I am not receiving any errors????
Thanks
Ralph
Posted: Tue Jun 19, 2007 1:32 pm
by Gente
reyes99 wrote:I know there must be something wrong because it is not cleaning the fields. But I am not receiving any errors????
Oh... Look again. You return the argument of your function without any modification. So your error is logical.
Try this
Code: Select all
function cleancomm($comm)
{
$comm1 = str_replace('?', ' ', $comm);
$comm2 = str_replace(',', ' ', $comm1);
return $comm2;
}
Posted: Tue Jun 19, 2007 3:44 pm
by reyes99
Thank you, it made cense after you explainted it.
One more thing can you tell me what these formatting things are called
\n \r
so I can search for them and read more about them? When I replace the ? or , it is cutting the line off and I want it to continue as part of the same string.
Thanks again everyone for all your help!!
Posted: Wed Jun 20, 2007 2:54 am
by Gente
"\n\r" makes a new line in the text output.
As CSV format doesn't support fields with multiple lines (every new line in the file is a new record) you can not use it in this situation.