Carriage returns in multi-line text field to csv to excel

PHP
1

Hi guys,
I’ve got an html form, processed by php that writes to a csv text file that I would like opened in excel. Problem is that I have a multi-line text field that users are submitting hard returns in that excel looks at as new records. How can I have the php ignore the hard returns within the abstract multi-line text field?

Here is my code…I know NOTHING about php for the record! It’s the $abstract value that is causing the issue…

<?php
if(isset($_POST['Submit'])){
   $oral = $_POST['oral'];
   $poster = $_POST['poster'];
   $either = $_POST['either'];
   $name = $_POST['name'];
    $affiliation = $_POST['affiliation'];
	 $telephone = $_POST['telephone'];
		   $email = $_POST['email'];
	    $delivery = $_POST['delivery'];
		 $eval = $_POST['eval'];
		  $perceptions = $_POST['perceptions'];
		  $decision = $_POST['decision'];
		   $access = $_POST['access'];
		    $other = $_POST['other'];
			 $knowledge = $_POST['knowledge'];
			  $social = $_POST['social']; 	
			   $economics = $_POST['economics']; 	
			    $other2 = $_POST['other2'];
				$faculty =$_POST['faculty'];
				$student =$_POST['student'];
				$supervisor =$_POST['supervisor'];
				$other3 =$_POST['other3'];
				$othercategory =$_POST['othercategory'];
				$coauthor =$_POST['coauthor'];
					$coauthor1 =$_POST['coauthor1'];
					$coauthor2 =$_POST['coauthor2'];
					$coauthor3 =$_POST['coauthor3'];
					$coauthor4 =$_POST['coauthor4'];
						$coauthor5 =$_POST['coauthor5'];
					$coauthor6 =$_POST['coauthor6'];
					$coauthor7 =$_POST['coauthor7'];
					$coauthor8 =$_POST['coauthor8'];
						$coauthor9 =$_POST['coauthor9'];
					$coauthor10 =$_POST['coauthor10'];
					$coauthor11 =$_POST['coauthor11'];
					$coauthor12 =$_POST['coauthor12'];
						$coauthor13 =$_POST['coauthor13'];
					$coauthor14 =$_POST['coauthor14'];
					$coauthor15 =$_POST['coauthor15'];
					$title =$_POST['title'];
				 $abstract = $_POST['abstract'];
				 $implications = $_POST['implications'];
			 	
   $err = '';
   if(trim($name)==''){
      $err .= '-Please enter your name.<br>';
   }
   if(trim($telephone)==''){
      $err .= '-Please enter a contact telephone number.<br>';
   }
   if(trim($email)==''){
      $err .= '-Please enter an email address.<br>';
   }
   if(trim($title)==''){
      $err .= '-Please enter a title.<br>';
   }
     if(trim($abstract)==''){
      $err .= '-Please enter an abstract.<br>';
   }
    if(empty($implications)){
   	$err .= '-All abstracts must contain a Policy Implications section.';
	}
           if($err!=''){
      echo $err;
   }
   else{
      $filename = 'abstract.txt';
      $somecontent = $oral . ',' . $poster . ',' . $either . ',' . $name . ',' . $affiliation . ',' . $telephone . ',' . $email . ',' . $delivery . ',' . $eval . ',' . $perceptions . ',' . $decision . ',' . $access . ',' . $other . ',' . $knowledge . ',' . $social . ',' . $economics . ',' . $other2 . ',' . $faculty . ',' . $student . ',' . $supervisor . ',' . $other3 . ',' . $othercategory . ',' . $coauthor . ',' . $coauthor1 . ',' . $coauthor2 . ',' . $coauthor3 . ',' . $coauthor4 . ',' . $coauthor5 . ',' . $coauthor6 . ',' . $coauthor7 . ',' . $coauthor8 . ',' . $coauthor9 . ',' . $coauthor10 . ',' . $coauthor11 . ',' . $coauthor12 . ',' . $coauthor13 . ',' . $coauthor14 . ',' . $coauthor15 . ',' . $title . ',' . $abstract . ',' . $implications . ',' ."\
";

      // Let's make sure the file exists and is writable first.
      if (is_writable($filename)) {

         // In our example we're opening $filename in append mode.
         // The file pointer is at the bottom of the file hence
         // that's where $somecontent will go when we fwrite() it.
         if (!$handle = fopen($filename, 'a')) {
             echo "Cannot open file ($filename)";
             exit;
         }

         // Write $somecontent to our opened file.
         if (fwrite($handle, $somecontent) === FALSE) {
            echo "Cannot write to file ($filename)";
            exit;
         }

		 echo '<img src="http://xxxxx/images/logosmall.jpg"> <br><br>';
         echo "Thank you -- your information has been received and the database will soon been updated.  Please do not resubmit. <br><br>";
		 echo '<a href="http://xxxxx">Go Back</a>';
		

         fclose($handle);

      } else {
         echo "The file $filename is not writable";
      }
   }
}
?>
2

Try adding double quotes to the $abstract varable, like so:

$somecontent = $oral . ‘,’ . $poster . ‘,’ . $either . ‘,’ . $name . ‘,’ . $affiliation . ‘,’ . $telephone . ‘,’ . $email . ‘,’ . $delivery . ‘,’ . $eval . ‘,’ . $perceptions . ‘,’ . $decision . ‘,’ . $access . ‘,’ . $other . ‘,’ . $knowledge . ‘,’ . $social . ‘,’ . $economics . ‘,’ . $other2 . ‘,’ . $faculty . ‘,’ . $student . ‘,’ . $supervisor . ‘,’ . $other3 . ‘,’ . $othercategory . ‘,’ . $coauthor . ‘,’ . $coauthor1 . ‘,’ . $coauthor2 . ‘,’ . $coauthor3 . ‘,’ . $coauthor4 . ‘,’ . $coauthor5 . ‘,’ . $coauthor6 . ‘,’ . $coauthor7 . ‘,’ . $coauthor8 . ‘,’ . $coauthor9 . ‘,’ . $coauthor10 . ‘,’ . $coauthor11 . ‘,’ . $coauthor12 . ‘,’ . $coauthor13 . ‘,’ . $coauthor14 . ‘,’ . $coauthor15 . ‘,’ . $title . ‘,"’ . $abstract . ‘",’ . $implications . ‘,’ ."
";

3

I tried this and I’m going to say it did not work? Each line still shows as a new record…

,TEST double quotes,sdf,sdf,asdf,delivery,perceptions,other2,sdf,asdf,1asdfasdf,2asdfasd,3sdfasdf,4faasdf,5asdfasdf,6,7,8,9,10,11,12,13,14,15,test,“asdfasdfadfasdf
sdf
asdf
asdf
asdf
asd
f
asf
asddf
sa
f
asdfsdf”,sdfasdf
asdf
asdf
asdfsad
fasd,

4

fputcsv()

5

The problem with $abstract is that it contains carriage returns, you can use


$abstract = str_replace(array("\\r\
", "\
", "\\r"), array(" ", " ", " "), $abstract);

Or use nl2br() if you want it to be html.

But the best solution is to use fputcsv as crmalibu mentioned, this way you don’t have to worry about csv format.

6

If this file is for reading using Excel, then why not create a genuine Excel file rather than a CSV file that breaks when Excel tries to open it.
That way you don’t need to worry about new lines in cell values, and can use additional features of Excel such as formatting and formulae

7

Thanks!!! I tried the

$abstract = str_replace(array("\\r\
", "\
", "\\r"), array(" ", " ", " "), $abstract);

and it worked like a charm! It was the only one I could comprehend with my limited knowledge and even then I didn’t so much as comprehend it as paste it in and see that it worked! :smiley: One of these days when I don’t have someone breathing down my neck I’ll figure out what all this stuff means!!!

8