通过PHP导出CSV

Question

通过PHP导出CSV

phpcsv

185

假设我有一个数据库....是否可以通过PHP将我拥有的内容导出到CSV文件（如果可能的话，还要导出为文本文件）？

- test

2

http://code.stephenmorley.org/php/creating-downloadable-csv-files/ - Sajeev C

10个回答

37

您可以使用此命令导出日期。

<?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);
?>

你首先需要将数据从mysql服务器加载到一个数组中。

- ynh

11

或者，你可以在一个标准的fetch assoc循环中执行fputcsv()并直接将它插入到返回结果中。 - DampeS8N

12

谢谢你的夸奖。我会尽力将“plop it down straight out”翻译为中文，并保持原意不变。 - AnchovyLegend

2

以下是有关编程的内容，需要从英语翻译成中文。请仅返回已翻译的文本：此内容是从PHP手册fputcsv中未经归属地复制的。 - BenK

15

仅供参考，连接字符串（concatenation）比 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;

这是对几个报告进行了优化的结论，从十行到数千行。三个示例在1000行以下运行良好，但在数据更大时失败了。

- Axel A. García

11

如果在请求头中指定文件大小，它可以与超过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);
}

- Bernhard Leichtle

9

我建议使用parseCSV-for-php来解决在嵌套的换行符和引号方面可能出现的一些问题。

- StigM

6

以下是预制的代码。您只需将其复制并粘贴到您的代码中即可：

https://gist.github.com/umairidrees/8952054#file-php-save-db-table-as-csv

- Umair Idrees

6

就像 @Dampes8N 所说的一样：

$result = mysql_query($sql,$conecction);
$fp = fopen('file.csv', 'w');
while($row = mysql_fetch_assoc($result)){
    fputcsv($fp, $row);
}
fclose($fp);

希望这能帮到你。

- roots

mysql_fetch_assoc()自'5.5'版本起已被弃用。 - undefined

5

<?php 
      
          // Database Connection
          include "includes/db/db.php";
           
          
              $query = mysqli_query($connection,"SELECT * FROM team_attendance JOIN team_login ON   
   team_attendance.attendance_user_id=team_login.user_id where   
   attendance_activity_name='Checked-In' order by   
   team_attendance.attendance_id ASC"); // Get data from Database from  
   demo table
           
           
              $delimiter = ",";
              $filename = "attendance" . date('Ymd') . ".csv"; // Create file name
               
              //create a file pointer
              $f = fopen('php://memory', 'w'); 
               
              //set column headers
              $fields = array('ID', 'Employee Name', 'Check In Time', 'Check Out Time', 'Date');
              fputcsv($f, $fields, $delimiter);
               
              //output each row of the data, format line as csv and write to file pointer
              while($row = $query->fetch_assoc()){
                   $date=date('d-m-Y',$row['attendance_date']);
                  $lineData = array($row['attendance_id'], $row['user_name'], $row['attendance_time'],   
   $row['check_out_time'],$date);
                  fputcsv($f, $lineData, $delimiter);
              }
               
              //move back to beginning of file
              fseek($f, 0);
               
              //set headers to download file rather than displayed
              header('Content-Type: text/csv');
              header('Content-Disposition: attachment; filename="' . $filename . '";');
               
              //output all remaining data on a file pointer
              fpassthru($f);
              ?>

- Anupam Verma

1

这对我所需的工作非常好。谢谢。 - JukEboX

0

        $data .= "Your Data";

        if ($data == ""):
            $data = "\nNo Records Found!\n";
            $file="call_sign_records.txt";

        header("Content-type: application/octet-stream"); 
        header("Content-Disposition: attachment; filename=$file"); 
        header("Pragma: no-cache"); 
        header("Expires: 0"); 
        print "$data";
endif;

- Snuriya

欢迎来到SO！请不要发布仅包含代码的答案，而是添加一些文字说明，解释您的方法如何以及为什么有效，以及与其他给出的答案有何不同之处。您可以在我们的“如何编写好的答案”页面找到更多信息。 - ahuemmer

0

你可以使用本地的PHP函数"fputcsv"。使用CSV很容易。

<?php

// Connect to the database
$conn = new PDO('mysql:host=localhost;dbname=mydatabase', $username, $password);

// Query the database to get the data
$result = $conn->query('SELECT * FROM table');

// Open a file for writing
$fp = fopen('table.csv', 'w');

// Loop through the result set
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    // Write the data to the file
    fputcsv($fp, $row);
}

// Close the file
fclose($fp);

使用txt格式会更加复杂，因为你没有说明想要以什么方式查看它。因此，你需要介绍你的转换器。

这里提供一个简单的例子。它会将行写入文件中。

<?php

// Connect to the database
$conn = new PDO('mysql:host=localhost;dbname=mydatabase', $username, $password);

// Query the database to get the data
$result = $conn->query('SELECT * FROM table');

// Open a file for writing
$fp = fopen('table.txt', 'w');

// Loop through the result set
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    // Transform it here as you want
    // Write the data to the file
    fwrite($fp, implode(',', $row) . "\n");
}

// Close the file
fclose($fp);

- madlopt

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alain Tiemblo · Accepted Answer

我个人使用此函数从任何数组创建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();
}

然后您可以使用类似以下方式让用户下载该文件：

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");
}

使用示例：

download_send_headers("data_export_" . date("Y-m-d") . ".csv");
echo array2csv($array);
die();