如何从PHP中删除csv中的引号

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;

Answer 1

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