how to remove quotes from csv in php

Question

I have a array that i am getting from DB. In this project, im converting my array to csv file. But every time i open the file i get double quoetes. I have tried with str_replace and preg_place with no succes. How can i remove quotes

this is my csv code

$query = "SELECT t.transactiontime, t.restaurant_id, t.transactionid, t.cardid, emd.m_field_id_2, t.pricebefordiscount, t.menucard_cut
from transactions as t
left join exp_member_data AS emd ON (t.cardid-10000000 = emd.member_id) order by t.transactiontime desc limit 50";

$transactions_query = ee()->db->query($query);
$transactions_result = $transactions_query->result_array();

$transaction_array = array();
foreach ($transactions_result as $key) 
{
  $date = new DateTime($key['transactiontime']);
  $newdate = $date->format('d.m.Y');


 $transaction_array[] = array(
    'transactiontime' => $newdate,
    'restaurant_id' =>  $key['restaurant_id'], 
    'member' => $key['transactionid'] . " " . $key['m_field_id_2'],
    'pricebefordiscount' => $key['pricebefordiscount']/100,
    'menucard_cut' => $key['menucard_cut']
    ); 


}


function outputCSV($data) 
    {

$outstream = fopen("php://output", 'w');



function __outputCSV(&$vals, $key, $filehandler) 
{
    fputcsv($filehandler, $vals, ';');
}

array_walk($data, '__outputCSV', $outstream);

fclose($outstream);
}

outputCSV($transaction_array);

my output

19.08.2013;47657;"12459 Abdullahi";60;
19.08.2013;47658;"12455 atima";30;

Quotes around fields in CSV files are perfectly valid, though, and anything that reads the file again should handle them correctly. — andrewsi, Aug 19 '13 at 14:16
Of course it's possible, but why do you want do this is the question. Maybe there's a better solution. — Jurik, Aug 19 '13 at 14:19
I assume that they're being added by fputcsv; in which case, you'd need to write your own functions to output the CSV. — andrewsi, Aug 19 '13 at 14:20
^ to complete this comment, look here how to write into your own file. It's pretty easy: http://www.php.net/manual/en/function.fwrite.php — Jurik, Aug 19 '13 at 14:22

Elias Van Ootegem · Accepted Answer · 2013-08-19T14:38:27.963

There really is nothing wrong with the quotes. They avoid any confusion that might occur when some CSV's use whitespace as delimiter:

data    "some more"    another thing
//is not the same as:
data    some more    another thing

However, if you want to remove them, apply this regex to each line:

$line = preg_replace('/(^|;)"([^"]+)";/','$1$2;',$line);

And you should be all right.
How does it work:

(^|;) matches (and captures) either the beginning of a line, or a semi-colon
" matches a literal " (doesn't capture)
([^" ]+): matches and captures at least one char that is not "
";: matches (no capture) a literal " and ;
$1$2;: the $1 is a back-reference to the first matched group ((^|;))
The $2 references ([^";]+), the ; is just a literal

Suppose $line is '19.08.2013;47657;"12459 Abdullahi";60;', the result (after the preg_replace call) would be: '19.08.2013;47657;12459 Abdullahi;60;'. The quotes are gone.

However, if some field were to contain a " char, it'll probably get escaped (\"), so to prevent the regex from failing to spot that, here's one that uses a lookahead assertion:

$line = preg_replace('/(?<=^|;)"(.+)"(?=;)/','$1',$line);

The difference:

(?<=^|;) a non-capturing positive lookbehind. The next thing in the pattern will only match if it's preceded either by the beginning of the string (^) or a semi-colon
(.+) is now the second group. It matches everything, including " BUT:
"(?=;) this matches a " only if it's followed by a ;.

When presented with a line like '19.08.2013;47657;"12459 \"Abdullahi\"";60;', the latter expression will return 19.08.2013;47657;12459 \"Abdullahi\";60; <-- it only removed the quotes that weren't escaped

where do you put your code $line = preg_replace('/(^|;)"([^"]+)";/','$1$2;',$line); ? — Zaz, Aug 19 '13 at 14:29
@Zaz: nowhere in your code, just noticed you're using `fputcsv`. That function is probably adding the quotes. so either use `fwrite($handle, implode(';',$array));` or write your own csv-making function. If the quotes are still there, apply this `preg_replace` to each line when you call `fwrite` — Elias Van Ootegem, Aug 19 '13 at 14:33

score 1 · Answer 2 · answered Jul 06 '17 at 08:00

Re write the file and try to parse csv file:

$file_path = "Book1.csv";
$string = file_get_contents($file_path, FILE_USE_INCLUDE_PATH);
echo $string;
echo "<br><br><br>";
$string2 = str_replace('"', " ", $string);
echo $string2;
file_put_contents($file_path, $string2); 
exit;

dearsina · Answer 3 · 2019-11-20T21:08:35.700

There is usually a very good reason why cell values are enclosed in quotation marks in a CSV. Generally it's because there is a danger/fear that the cell value contains the column value separation marker. Removing them may cause havoc when parsing the CSV.

In a well formatted CSV file, if cells are enclosed with quotation marks, quotation marks that form part of the cell value need to be escaped. Escaping is very important, or else the parser reading the CSV will not understand where a cell value starts and ends.

Unfortunately, mistakes are made. And you're probably here because you have to parse a CSV where the creator did not properly escape their CSV. Thus, below is the definitive way to strip away enclosure quotation marks. The following RegEx will remove quotation marks at the beginning and end of cell values, but not inside them.

$delimiter = ',';
$enclosure = '"';
$row = preg_replace("/(?:(?<=^|{$delimiter}){$enclosure})|(?:{$enclosure}(?=$|{$delimiter}))/",'',$row);

If your delimiter character is pipe, make sure you prefix it with 2 backslash characeters (\\|).

A demo can be found here.

score -1 · Answer 4 · edited May 23 '17 at 12:14

-1

try this

$array = array('19.08.2013',47657,'"12459 Abdullahi"');
$array = str_replace('"', '', $array);
outputCSV($array);

So it might be like this in your code

$transaction_array = str_replace('"', '', $transaction_array);

or check this thread

Avoid default quotes from csv file when using fputcsv

edited May 23 '17 at 12:14

Community

1
1

answered Aug 19 '13 at 14:24

usman allam

274
1
5

how to remove quotes from csv in php

4 Answers4