• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Strip quotes from fputcsv results.

Community Expert ,
Nov 13, 2015 Nov 13, 2015

Copy link to clipboard

Copied

For now, I'm querying a MySQL database with phpMyAdmin and exporting results to a comma delimited CSV file.

It works fine but I need to automate this if I can.

The php script I created uses fputcsv but by default it adds double quotes around text strings containing spaces. 

And the service I'm working with doesn't allow quotes in the CSV file.

Results with fputcsv:

1234,"prod name with spaces",29.95,/path/filename.zip,1gb,4320

Desired result:

1234,prod name with spaces,29.95,/path/filename.zip,1gb,4320

<?php

mysqli_select_db($con, "myproducts") or die ("no database found");  

$result = mysqli_query($con,"SELECT prodcode, prodname, price, dload_url, size, expiry_time FROM downloads WHERE dload_url LIKE '%value%' ORDER BY prodcode DESC");

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

$fp = fopen('myfile.csv', 'w');

      while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){

           fputcsv($fp, $row);

        }

fclose($fp);

?>

Any ideas?

Nancy O.

Nancy O'Shea— Product User, Community Expert & Moderator

Views

17.9K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Nov 15, 2015 Nov 15, 2015

A much simpler solution is to use fwrite() instead of fputcsv():

$fp = fopen('myfile.csv', 'w');

      while($row = mysqli_fetch_array($result, MYSQLI_NUM)){

           fwrite($fp, implode(',', $row) . "\r\n");

        }

fclose($fp);

The reason fputcsv() insists on an enclosure is to prevent problems if the text contains the same character as the delimiter. If you're certain that the value of prodname will never contain commas, this solution will work. But if a product name ever contains commas, you'r

...

Votes

Translate

Translate
Guru ,
Nov 14, 2015 Nov 14, 2015

Copy link to clipboard

Copied

Nancy, I have not used this function before (I use PHPExcel to create spreadsheets from queries), but I do see that the double quotes are an option (enclosure), so you may be able to set the option to nothing

fputcsv(file,fields,seperator,enclosure)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 14, 2015 Nov 14, 2015

Copy link to clipboard

Copied

Good suggestion, Rob.

I tried it but enclosure must be a single token like a space, quote, pipe or something.

     fputcsv($fp, $row,',','');

Nancy O.

Nancy O'Shea— Product User, Community Expert & Moderator

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Nov 15, 2015 Nov 15, 2015

Copy link to clipboard

Copied

Then would it be possible to run preg_replace or str_replace after the function has run? You could set the enclosure to something unique and strip it out afterwards.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 15, 2015 Nov 15, 2015

Copy link to clipboard

Copied

A much simpler solution is to use fwrite() instead of fputcsv():

$fp = fopen('myfile.csv', 'w');

      while($row = mysqli_fetch_array($result, MYSQLI_NUM)){

           fwrite($fp, implode(',', $row) . "\r\n");

        }

fclose($fp);

The reason fputcsv() insists on an enclosure is to prevent problems if the text contains the same character as the delimiter. If you're certain that the value of prodname will never contain commas, this solution will work. But if a product name ever contains commas, you're screwed.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 15, 2015 Nov 15, 2015

Copy link to clipboard

Copied

Perfect!

The reason fputcsv() insists on an enclosure is to prevent problems if the text contains the same character as the delimiter.

Right. And I can see where that would be useful in some cases.

Our dbase contains no commas. So this works.

Thanks a million, David.

Nancy O'Shea— Product User, Community Expert & Moderator

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 15, 2015 Nov 15, 2015

Copy link to clipboard

Copied

LATEST

Then would it be possible to run preg_replace or str_replace after the function has run?

Sadly, that didn't work either.  Fputcsv is very stubborn.

But thanks for your input.  It's much appreciated.

Nancy O.

Nancy O'Shea— Product User, Community Expert & Moderator

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines