CODEWITHSUNDEEP
phpsqlcsvdrupal
raeq
raeq

Reputation: 971

How to remove commas in a SQL output to print in a CSV?

So I have a multidimensional array - $result - that kind of looks like this:

[3] => stdClass Object
    (
        [ProfileID] => 7656
        [Practice_Name] => Some Name, MD
        [some_id] => 
        [Telephone] => (816)531-0930
        [Email] => [email protected]
        [Contact_Name] => Some Name
        [Address] => Some Rd, Ste 40
        [City] => Some City
        [State] => MO
        [ZipCode] => 
        [Last_Accessed] => 123479
        [Some_statis] => Active
    )

I am printing all the arrays into a CSV in drupal using this code:

header("Cache-Control: public");
header("Content-Type: application/octet-stream");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=\"contacts.csv\";" );
header("Content-Transfer-Encoding: binary");        

foreach($result as $row) {
    $line = implode(",", (array) $row) . PHP_EOL;
    print $line;
}
drupal_exit();

Right now it splits all the contents of the multidimensional array into columns. But the problem is if a result has a comma in it then an extra column gets added. So "Some Name, MD" results in MD being pushed to a different column.

Any idea how to avoid this? I am okay with removing the comma in there but have had no luck with doing a preg_replace.

Upvotes: 0

Views: 309

Answers (2)

Phil
Phil

Reputation: 449

A csv file has a double quote qualifier in it to cover the strings that contain commas like your line "Some Name, MD". If you are importing this into anything that reads a standard format with the double quote qualifier you could just make sure your output always has the double quotes around it.

Upvotes: 2

Orangepill
Orangepill

Reputation: 24665

You can use fputcsv to get properly escaped csv.

 $fp = fopen("php://output", "w");
 foreach($result as $row) {
      fputcsv($fp,  (array) $row));
 }
 fclose($fp);
 drupal_exit();

Upvotes: 1

Related Questions