用PHP写入.csv文件(数据错误中的逗号) [英] Write to .csv file with PHP (Commas in Data Error)

问题描述

我正在处理一个PHP语句，该语句运行查询，然后将数据写入.csv文件.我遇到的问题是，我从服务器接收的某些数据的数据中包含逗号，导致.csv文件在错误的位置输入数据.下面有一个代码示例.

I am working on a PHP statement that runs a query and then writes the data to a .csv file. The problem I am having is that some of the data I am receiving from the server has commas in the data which causes for the .csv file to enter data in the wrong place. Below I have an example of the code.

$sql = "Select *
From table;"

$data = mysqli_query($link, $sql);
$row= ("Column One, Column Two, Column Three\n");

while ($result = $data->fetch_assoc()) {
    $row .= ("$result[columnOne], $result[columnTwo], $result[columnThree]\n");
}

$fd = fopen("./filePath.csv", "w") or die ("Error Message");
fwrite($fd, $row);
fclose($fd);

第三列是数据包含逗号的位置，导致其写入.csv文件中的不同单元格.有什么解决方法可以使$ result [columnThree]数据即使在其中包含逗号的情况下仍保留在一个单元格中?

Column three is where the data contains commas which causes for it to write to different cells in the .csv file. Is there any solution to make the $result[columnThree] data stay in one cell even though it contains commas in it?

推荐答案

您可以将值用双引号引起来:

You can wrap the values in double-quotes:

$row .= ('"'.$result['columnOne'].'", "'.$result['columnTwo'].'", "'.$result['columnThree'].'"\n"');

我希望尽可能多地使用数组，而不是连接字符串:

Instead of concatenating a string, I like to use arrays as much as possible:

$rawCsv = array();
while ($result = $data->fetch_assoc()) {
    if (count($rawCsv) === 0)
        $rawCsv[] = '"'.implode('","', array_keys($result )).'"';
    $rawCsv[] = '"'.implode('","', $result ).'"';
}
$csvString = implode("\n", $rawCsv);

这两种方法都很难，因为数据中的字符不同-双引号.考虑到这一点，一个更好的选择是使用fopen和fputcsv创建您的CSV数据，而您不必考虑它.

Both of these approaches will have a hard time with a different character in your data though -- the double quote. With that in mind, an even better alternative would be to use fopen and fputcsv to create your CSV data and you don't have to think about it.

如果您打算立即提供CSV数据以供下载，则根本不需要文件，只需将其转储到输出黄油中即可.

If you plan to immediately offer the CSV data for download, you don't need a file at all, just dump it into the output butter:

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

...如果确实要挂接到文件，则在所需的输出文件上使用fopen并跳过对ob_start

... if you do want to hang on to a file, then use fopen on the desired output file and skip the call to ob_start

接下来，整理您的数据:

Next, assemble your data:

fputcsv($file_handle, array(
    'Your',
    'headings',
    'here'
));

while ($result = $data->fetch_assoc()) {
    fputcsv($file_handle, array(
        $result['Your'],
        $result['data'],
        $result['"here"']
    ));
}
fclose($file_handle);

...如果您使用的是文件，则一切就绪！如果使用输出缓冲区(不使用文件)，则可以获取CSV数据并将其直接发送到浏览器:

... If you're using a file, then you're all set! If you are using the output buffer (no file used), you can grab the CSV data and send it to the browser directly:

$csv = ob_get_clean();
echo $csv; // should send headers first!

请谨慎使用输出缓冲，但是某些框架/应用程序会在内部使用它.如果您遇到问题，请尝试使用文件.如果该文件有效，则说明您的框架可能已经在使用输出缓冲区了.

Be careful with output buffering, though, some frameworks/applications make use of it internally. If you're running in to problems with it, try using a file. If the file works, then your framework is probably already doing something with the output buffer.

文档

• RFC 4180 逗号分隔值(CSV)文件的通用格式和MIME类型- http://php.net/function.implode
• fopen- http://php.net/manual/zh/function. fopen.php
• fclose- http://php.net/manual/zh/function. fclose.php
• fputcsv- http://php.net/manual/en/function. fputcsv.php
• ob_start- http://php.net/manual/zh/function.ob-start.php
• ob_get_clean- http://php.net/manual/zh/function.ob-get-clean.php

• RFC 4180 Common Format and MIME Type for Comma-Separated Values (CSV) Files - https://tools.ietf.org/html/rfc4180
• implode - http://php.net/function.implode
• fopen - http://php.net/manual/en/function.fopen.php
• fclose - http://php.net/manual/en/function.fclose.php
• fputcsv - http://php.net/manual/en/function.fputcsv.php
• ob_start - http://php.net/manual/en/function.ob-start.php
• ob_get_clean - http://php.net/manual/en/function.ob-get-clean.php

这篇关于用PHP写入.csv文件(数据错误中的逗号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！