Export CSV question

[nepzap2]

Hello everyone,

 

I have an application that exports from MySQL to CSV. When I open the CSV file in Excel some of my "text fields" from the MySQL database have a lot of text but some of that text is flowing into other columns. Can it be that upon export the commas within the text fields are being treated as deli meters?

 

here is the function that queries the database.

 

<?php
function getSelectedApplicantsCSV($year){

$where_clause = "WHERE reu_year";// = $year AND select_order > 0 ";	
$applicants = getApplicantsByWhereClause($where_clause);

$ret = "id, creation_date, reu_year, last_name, first_name, school_name, school_size, email_address, email_address_alt, phone, enrollmentStatus, high_school_student_year, undergraduate_student_year, highSchoolGPA, major, minor, collegeGPA, graduation_date, reuParticipation, mailingAddress, city, state, zipCode, home_phone, emergency_contact_name, emergency_contact_phone, emergency_contact_relation, birthDate, firstGeneration, gender, ethnicity, programSource, interest1, interest2, interest3, laboratoryExperience, researchExperience, personalStatement, resume\n";
while($row = mysql_fetch_array($applicants)){
	//$interests = getInterestsString($row);

	$ret .= $row['id'] . ", " .
			$row['creation_date'] . ", " .
			$row['reu_year'] . ", " .
	        $row['last_name'] . ", " . 
	        $row['first_name'] . ", " . 
			$row['school_name'] . ", " .
	        $row['school_size'] . ", " . 
	        $row['email_address'] . ", " .
			$row['email_address_alt'] . ", " .
	        $row['phone'] . ", " . 
	        $row['enrollmentStatus'] . ", " .
			$row['high_school_student_year'] . ", " .
	        $row['undergraduate_student_year'] . ", " . 
	        $row['highSchoolGPA'] . ", " . 
			$row['major'] . ", " .
	        $row['minor'] . ", " . 
	        $row['collegeGPA'] . ", " .
			$row['graduation_date'] . ", " .
	        $row['reuParticipation'] . ", " . 
	        $row['mailingAddress'] . ", " .
			$row['city'] . ", " . 
	        $row['state'] . ", " .
			$row['zipCode'] . ", " .
	        $row['home_phone'] . ", " . 
	        $row['emergency_contact_name'] . ", " . 
			$row['emergency_contact_phone'] . ", " .
	        $row['emergency_contact_relation'] . ", " . 
	        $row['birthDate'] . ", " .
			$row['firstGeneration'] . ", " .
	        $row['gender'] . ", " . 
	        $row['ethnicity'] . ", " .
			$row['programSource'] . ", " .
			$row['interest1'] . ", " .
			$row['interest2'] . ", " .
			$row['interest3'] . ", " .
			$row['laboratoryExperience'] . ", " .
			$row['researchExperience'] . ", " .
			$row['personalStatement'] . ", " .
	        //preg_replace("/\<br\>/", "", $interests) . ", " . 
	        $row['resume'] . "\n";
}
return $ret;
}
?>

 

and here is my export controller.

 

<?php
include_once "../db_config.php";
include_once "functions.php";

$pi_id = $_GET['pi_id'];
$pi_id = (null == $pi_id ? -1 : $pi_id);
$year = $_GET['year'];
$year = (null == $year ? -1 : $year);
$applicant_id = $_GET['applicant_id'];
$applicant_id = (null == $applicant_id ? -1 : $applicant_id);	

if($_GET['what'] === "view_selected_csv"){

	$csvContent = getSelectedApplicantsCSV($year);

	@ob_end_clean(); //turn off output buffering to decrease cpu usage

	// required for IE, otherwise Content-Disposition may be ignored
	if(ini_get('zlib.output_compression')) ini_set('zlib.output_compression', 'Off');

	header("Content-Type: text/csv");
	header("Content-Disposition: attachment; filename=\"selected_applicants_$year.csv\"");
	header("Content-Transfer-Encoding: binary");
	header("Accept-Ranges: bytes");

	/// The three lines below basically make the download non-cacheable 
	header("Cache-control: private");
	header("Pragma: private");
	header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
	header("Content-Length: " . strlen($csvContent));

	echo($csvContent);
	flush();
	return;
}
?>

 

does anyone know what I may be missing?

[nepzap2]

How can I get commas in a text field to not be treated as a delimeter upon exporting?

[fenway]

CSV is a very tricky format -- you need to escape any fields that contain the separator.

[fenway]

How can I get commas in a text field to not be treated as a delimeter upon exporting?

Don't double-post next time -- continue in the same thread; consider this a warning.

 

I can't possibly imagine that PHP doesn't have a module for this; but how to escape it depends on what program is being used to read it.  Usually, it's just adding quotes.

[tomdchi]

I was having the same problems so I started using the PHPExcel class to output my csv files to excel format.  it works great.

http://www.codeplex.com/PHPExcel