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.

Code: Select all

str_replace(',', '\,', $str);

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;
  }
:wink:

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.