一次性导入20万+的excel数据（转载记录）

 

<?php
class Add
{
    public function data()
    {
        ini_set('memory_limit', '-1');  //PHP内存设置
        $handle=fopen("201501.csv","r");
        $fileName="201501.csv";
        //将文件一次性全部读出来
        $excelData = array();
        $content = trim(file_get_contents($fileName));
        $excelData = explode("\n",$content);

        $chunkData = array_chunk($excelData , 500); //批量插入500条数据
        $count = count($chunkData);
        for ($i = 0; $i < $count; $i++) {
            $insertRows = array();
            foreach($chunkData[$i] as $value){
                $string = mb_convert_encoding(trim(strip_tags($value)), 'utf-8', 'utf-8');//转码
                $v = explode(',', trim($string));
                $v = str_replace("'", " ", $v);
                $v = str_replace(" ", "", $v);
                $sqlString       = '('."'".implode( "','", $v  ) . "'".')'; //批量
                $insertRows[]    = $sqlString;
            }
             //var_dump($insertRows);
            // $sql=$insertRows[0];
            // echo $sql;
            $result = $this->add($insertRows); //批量将sql插入数据库。
        }
    }

    public function add($insertRows)
    {
        set_time_limit(0);
        $pdo= new PDO("mysql:host=xxxxxx;dbname=xxx",'root','xxx');

        // //采取批量插入
        $data = implode(',', $insertRows);
        $sql = "INSERT into `bsy_201501`(id,sid,oid,nickname,tel,phone,contacts,province,city,area,address,link1,link2,omoney,gname,gsign,gnum,onum,gpaymoney,gtotalmoney,gsinglemoney)
                    VALUES {$data}";
        // var_dump($sql);die;
        $result = $pdo->query($sql);
    }

}
$model = new Add();
$model->data();