PHP import millions of data to csv file

Keywords: PHP Excel

I saw a lot of codes that import millions of data into CSV files on the Internet. I tried them on my own. M ost of them will cause memory overflow due to too large data. I saw the use of PHP generator by chance and finally found a solution (the memory required is very small, about tens of meters is enough, depending on the range of data you get each time). I took THINKPHP5.1 as an example to test. Try as follows:

My environment is LNMP, PHP version is PHP7.2

  1. Write a Csv.php class and put it into the extend directory, as follows:
namespace csv;

use think\Db;

class Csv
     * Export data to CSV file
     * @param $fileName  Generated csv filename
     * @param array $title csv The first line of the file displays the title
     * @param array $showKeys csv Field name in the table displayed corresponding to the file
     * @param string $table Data table for query
     * @param string $pk Auto primary key of data table, this field must exist
     * @param array $where query criteria
    public function putCsv($fileName, $title = [], $showKeys = [], $table = '', $pk = 'id', $where = []) {
        header('Content-Type: application/');//Set content type to Excel
        header('Content-Disposition: attachment;filename='.$fileName );//Download File
        header('Cache-Control: max-age=0');//Indicates that you will not visit the server again within 0 seconds after visiting this web page
        //Open a file or URL. PHP: / / output is a write only data stream. It allows you to write to the output buffer in the same way as echo and print. A: open in write mode, and point the file pointer to the end of the file. If the file does not exist, try to create it.
        $file = fopen('php://output',"a");

        $limit =1000;
        $calc = 0;

        foreach ($title as $v){
            $tit[] = iconv('UTF-8', 'GB2312//IGNORE',$v); / / transcode
        //Format the line as CSV and write it to an open file. (title)

        //Number of cycles per time
        $step = 10000;
        $j = $step;

        //Calculate total quantity
        if (!empty($where)) {
            $lastIdValue = Db::name($table)->where($where)->order($pk . ' DESC')->limit(1)->value($pk);
        } else {
            $lastIdValue = Db::name($table)->order($pk . ' DESC')->limit(1)->value($pk);

        for ($i = 1;$i < $lastIdValue;$i = $i + $step) {

            //Get the generator object returned by the generator here
            if (!empty($where)) {
                $cursor = Db::name($table)->where($pk, 'between', [$i, $j])->where($where)->cursor();
            } else {
                $cursor = Db::name($table)->where($pk, 'between', [$i, $j])->cursor();

            foreach ($cursor as $v){
                //-------Core!!! Clear cache, write data on cache to file--------
                if($limit == $calc){
                    ob_flush();//Take out the existing contents of the output cache, and the contents of the buffer will be discarded after the call of ob_flush().
                    flush();   //The content to be output is sent immediately.
                    $calc = 0;
                $arr = [];
                foreach ($showKeys as $key) {
                    if (isset($v[$key])) {
                        $arr[] = $v[$key];
                foreach($arr as $t){
                    $tarr[] = iconv('UTF-8', 'GB2312//IGNORE',$t);
                fputcsv($file,$tarr);//Format the line as CSV and write it to an open file. (content)
                unset($tarr);//Destroy the specified variable

            $j = $j + $step;

        //unset($list); / / destroy the specified variable
        fclose($file);//Close open file

2, the call class is exported to the CSV file in the controller.

public function testPutCsv() {
        $fileName = 'test.csv';
        $title = ['url address', 'Adding time', 'state'];
        $showKeys = ['url', 'add_time', 'status'];
        $where = [
            'status' => 0
        $csv = new Csv();
        $csv->putCsv($fileName, $title, $showKeys, 'use_qrcode', 'id', $where);



Posted by SpasePeepole on Sat, 09 Nov 2019 06:34:18 -0800