导出csv时如何从列内容中转义定界符 [英] how to escape the delimiter from the column content when export csv

问题描述

我要导出CSV文件，定界符是，"逗号，但是如果列内容中有逗号，则存在问题，当查看libre office calc时，它将破坏csv输出.

Im up to export the CSV file, delimiter is a "," comma but im there is a problem if column content had comma , it will break the csv out put when looking on libre office calc.

如果有人能帮助我解决这个问题

if any body can help me how to solve this

这里是目前为止

//$filename = $row['fileName'].'-ConsignmentInfo.csv';
$filename="test.csv";
$delim = ',';
$columns = array('0Product_Number', 'Product_Name', 'Alternative_Title');
echo implode($delim,$columns )."\n";           

$ptr1 = DD_Db::fetch("SELECT p.pNum,p.pName,a.varcharValue FROM ds_products p   left join productAttribute a on a.id=p.pID where a.attributeId= (select id FROM attribute where attributeName='alternateTitle') ");

 foreach ($ptr1 as $row) {
      $line = array("\"{$row['pNum']}\"","\"{$row['pName']}\"","\"{$row['varcharValue']}\"");
      echo implode($delim,$line)."\n";
 }

header('Content-Type: text/csv');
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header('Content-Disposition: attachment; filename='.$filename);
header('Pragma: cache');
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); // Date in the past
exit;

推荐答案

只需使用 fputcsv 可以转义并生成正确的csv数据.

Just use fputcsv it takes care of escaping and producing correct csv data.

<?php

$list = array (
    array('aaa', 'bbb', 'ccc', 'dddd'),
    array('123', '456', '789'),
    array('"aaa"', '"bbb"')
);

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

foreach ($list as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);
?>

输出:

aaa,bbb,ccc,dddd
123,456,789
"""aaa""","""bbb"""

编辑

您始终可以结合使用 tmpfile 来打开文件它将在请求结束时自动删除，写入请求，然后在创建报告后使用 tempnam + file_get_contents ，但在这种情况下，您必须打开文件，并在您自己阅读后进行清理.

Edit

You can always use a combination of tmpfile to open a file that will be automatically deleted at the end of the request, write to it and then after the report is created output its content with fread. You have to use fread since tmpfile return a resource, otherwise you can use tempnam + file_get_contents but in that case you have to open the file and to cleanup after read it by yourself.

这篇关于导出csv时如何从列内容中转义定界符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！