通过 PHP 导出为 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4249432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export to CSV via PHP
提问by test
Let's say I have a database.... is there a way I can export what I have from the database to a CSV file (and text file [if possible]) via PHP?
假设我有一个数据库……有没有办法通过 PHP 将数据库中的内容导出到 CSV 文件（和文本文件 [如果可能]）？
回答by Alain Tiemblo
I personally use this function to create CSV content from any array.
我个人使用此函数从任何数组创建 CSV 内容。
function array2csv(array &$array)
{
if (count($array) == 0) {
return null;
}
ob_start();
$df = fopen("php://output", 'w');
fputcsv($df, array_keys(reset($array)));
foreach ($array as $row) {
fputcsv($df, $row);
}
fclose($df);
return ob_get_clean();
}
Then you can make your user download that file using something like:
然后您可以让您的用户使用以下内容下载该文件：
function download_send_headers($filename) {
// disable caching
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
// force download
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
// disposition / encoding on response body
header("Content-Disposition: attachment;filename={$filename}");
header("Content-Transfer-Encoding: binary");
}
Usage example:
用法示例：
download_send_headers("data_export_" . date("Y-m-d") . ".csv");
echo array2csv($array);
die();
回答by ynh
You can export the date using this command.
您可以使用此命令导出日期。
<?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);
?>
First you must load the data from the mysql server in to a array
首先，您必须将数据从 mysql 服务器加载到数组中
回答by Axel A. García
Just for the record, concatenation is waaaaaay faster (I mean it) than fputcsvor even implode; And the file size is smaller:
只是为了记录，连接比fputcsv甚至更快（我的意思是）implode；并且文件大小更小：
// The data from Eternal Oblivion is an object, always
$values = (array) fetchDataFromEternalOblivion($userId, $limit = 1000);
// ----- fputcsv (slow)
// The code of @Alain Tiemblo is the best implementation
ob_start();
$csv = fopen("php://output", 'w');
fputcsv($csv, array_keys(reset($values)));
foreach ($values as $row) {
fputcsv($csv, $row);
}
fclose($csv);
return ob_get_clean();
// ----- implode (slow, but file size is smaller)
$csv = implode(",", array_keys(reset($values))) . PHP_EOL;
foreach ($values as $row) {
$csv .= '"' . implode('","', $row) . '"' . PHP_EOL;
}
return $csv;
// ----- concatenation (fast, file size is smaller)
// We can use one implode for the headers =D
$csv = implode(",", array_keys(reset($values))) . PHP_EOL;
$i = 1;
// This is less flexible, but we have more control over the formatting
foreach ($values as $row) {
$csv .= '"' . $row['id'] . '",';
$csv .= '"' . $row['name'] . '",';
$csv .= '"' . date('d-m-Y', strtotime($row['date'])) . '",';
$csv .= '"' . ($row['pet_name'] ?: '-' ) . '",';
$csv .= PHP_EOL;
}
return $csv;
This is the conclusion of the optimization of several reports, from ten to thousands rows. The three examples worked fine under 1000 rows, but fails when the data was bigger.
这是几行报表优化的结论，从十行到千行。这三个示例在 1000 行下运行良好，但在数据较大时失败。
回答by StigM
I recommend parsecsv-for-phpto get around a number any issues with nested newlines and quotes.
我推荐parsecsv-for-php来解决嵌套换行符和引号的任何问题。
回答by Bernhard Leichtle
Works with over 100 lines, if you specify the size of the file in the headers simple call the get() method in your own class
处理超过 100 行，如果您在标头中指定文件的大小，则只需在您自己的类中调用 get() 方法
function setHeader($filename, $filesize)
{
// disable caching
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 01 Jan 2001 00:00:01 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
// force download
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Type: text/x-csv');
// disposition / encoding on response body
if (isset($filename) && strlen($filename) > 0)
header("Content-Disposition: attachment;filename={$filename}");
if (isset($filesize))
header("Content-Length: ".$filesize);
header("Content-Transfer-Encoding: binary");
header("Connection: close");
}
function getSql()
{
// return you own sql
$sql = "SELECT id, date, params, value FROM sometable ORDER BY date;";
return $sql;
}
function getExportData()
{
$values = array();
$sql = $this->getSql();
if (strlen($sql) > 0)
{
$result = dbquery($sql); // opens the database and executes the sql ... make your own ;-)
$fromDb = mysql_fetch_assoc($result);
if ($fromDb !== false)
{
while ($fromDb)
{
$values[] = $fromDb;
$fromDb = mysql_fetch_assoc($result);
}
}
}
return $values;
}
function get()
{
$values = $this->getExportData(); // values as array
$csv = tmpfile();
$bFirstRowHeader = true;
foreach ($values as $row)
{
if ($bFirstRowHeader)
{
fputcsv($csv, array_keys($row));
$bFirstRowHeader = false;
}
fputcsv($csv, array_values($row));
}
rewind($csv);
$filename = "export_".date("Y-m-d").".csv";
$fstat = fstat($csv);
$this->setHeader($filename, $fstat['size']);
fpassthru($csv);
fclose($csv);
}
回答by roots
Just like @Dampes8N said:
就像@Dampes8N 说的：
$result = mysql_query($sql,$conecction);
$fp = fopen('file.csv', 'w');
while($row = mysql_fetch_assoc($result)){
fputcsv($fp, $row);
}
fclose($fp);
Hope this helps.
希望这可以帮助。
回答by Umair Idrees
pre-made code attached here. you can use it by just copying and pasting in your code:
预制代码附在此处。您只需复制和粘贴代码即可使用它：
https://gist.github.com/umairidrees/8952054#file-php-save-db-table-as-csv
https://gist.github.com/umairidrees/8952054#file-php-save-db-table-as-csv
