搜索
简帛阁>技术文章>php 结合缓冲区读取excel数据并批量导入mysql

php 结合缓冲区读取excel数据并批量导入mysql

在工作中,很多时候需要把excel中的数据读取出来,导入到mysql中,以前的代码,都是通过 Spreadsheet_Excel_Reader插件直接取出来,进行for循环,组合成一个长的sql语句,这样进行插入,但是这样的导入有不足之处就是如果导入的数据量庞大,那么会造成长语句超出sql最大限制。并且执行时间超长,会超出php脚本执行时间下面贴一段原先的代码。

require_once 'Excel/reader.php';//调用Reader,引用地址可以任意,正确即可
$data = new Spreadsheet_Excel_Reader(); //创建 Reader
$data->setOutputEncoding('utf-8');//设置文本输出编码
$data->read($uploadfile);//读取Excel文件
$insert = '';
for ($i = 0; $i < $data->sheets[0]['numRows']; $i++) {<!-- -->
$insert .= "('".$data->sheets[0]['cells'][$i][1]."','".$data->sheets[0]['cells'][$i][3]."-".$data->sheets[0]['cells'][$i][4]."','".$data->sheets[0]['cells'][$i][7]."--".$data->sheets[0]['cells'][$i][6]."',1,'".$data->sheets[0]['cells'][$i][2]."','".$datetime."','".$data->sheets[0]['cells'][$i][8]."','".$data->sheets[0]['cells'][$i][5]."','".$data->sheets[0]['cells'][$i][8]."'),";
}

用过这款插件的同学应该知道,$data->sheets[0]['numRows']即php读取excel中的记录数,而$data->sheets[0]['cells']为php读取出来的数据,这样调取其实是可以的,但是前提是数据量不大。

数据量稍微大一点就提示以下错误,这是因为发送的SQL语句太长,以致超过了max_allowed_packet的大小,这种情况,你只要修改my.cnf,加大max_allowed_packet的值即可。但是这种虽然可以解决大sql插入问题,但是程序运行的时间也响应的增加了,比如:读取一个5000记录数的excel并插入,居然使用了超过20秒时间。这意味着如果采用此种方法,还需要设置set_time_limit(0);


但是大多数的情况下,不可能频繁的使用小excel文件进行导入,也太繁琐,通过php的前期处理,将大型的sql语句拆分为n多符合条件的sql语句,配合缓冲区,这样做的好处就是:不用考虑因为sql语句太长而造成执行时间超出php限定时间,以及语句太长造成mysql报错。


$len = $data->sheets[0]['numRows'];
		$datacells = $data->sheets[0]['cells'];
		unset($data);
		//以2000条为分界
		$lun = $len / 2000;
		// echo $len.'<br />';
		// echo $lun.'<br />';
		$num = intval($len % 2000);
		if($num == 0){
			$lun = $lun;
		}else{
			if($lun>0){
				$lun = floor($lun);
			}else{
				$lun = 0;
			}
		}
		// echo $lun.'<br />';
		// echo $num;exit;
		set_time_limit(0);
		//插入一条excel记录
		mysql_query("INSERT INTO `excel` (`id`, `oldname`, `newname`, `size`, `time`) VALUES (NULL, '$file', '$uploadfile', '$len','$exceltime');");  
		$insert_id=mysql_insert_id();
		if($lun >= 1){ 
			for($j = 0;$j < $lun;$j++){
				ob_end_clean();
				$insert = '';
				for($i = 0;$i <= 2000;$i++){
					$key=$i+$j*2000;
					if (strlen($datacells[$key][5])>0){
						$datetime=$datacells[$key][5];
					}else{
						$datetime=date('Y-m-d H:i:s');
					}
					// if($datacells[$key][3]){
						$insert .= "('".$datacells[$key][1]."','".$datacells[$key][3]."-".$datacells[$key][4]."','".$datacells[$key][7]."--".$datacells[$key][6]."',1,'".$datacells[$key][2]."','".$datetime."','".$datacells[$key][8]."','".$datacells[$key][5]."','".$datacells[$key][8]."','".$insert_id."'),"; 
					// }
				}
				$insert=trim($insert,',');
				$sql = "INSERT INTO message_bak (tel, addr, y_title, bs, wz, timeadd,ip,uid,telzt,excel_id) VALUES".$insert;
                               //echo $sql;
                                $res != mysql_query($sql);
				if (!$res){
					$msg="SQL语句执行错误".$sql;
				}	
				flush();
			}
		}
                if($lun > 0 && $num > 0){
                    unset($sql);
                    insert = '';
                    for($i = 0;$i <= $num;$i++){
                       $key=($lun-1)*2000+$i;
                       if (strlen($datacells[$key][5])>0){
                          $datetime=$datacells[$key][5];
                       }else{
                          $datetime=date('Y-m-d H:i:s');
                       }
                       $insert .= "('".$datacells[$key][1]."','".$datacells[$key][3]."-".$datacells[$key][4]."','".$datacells[$key][7]."--".$datacells[$key][6]."',1,'".$datacells[$key][2]."','".$datetime."','".$datacells[$key][8]."','".$datacells[$key][5]."','".$datacells[$key][8]."','".$insert_id."'),"; 
                    }
                    $insert=trim($insert,',');
                    $sql = "INSERT INTO message_bak (tel, addr, y_title, bs, wz, timeadd,ip,uid,telzt,excel_id) VALUES".$insert;
                    $res = mysql_query($sql);

                }elseif($num > 0){
                    unset($sql);
                    insert = '';
                    for($i = 0;$i <= $num;$i++){
                       $key=$i;
                       if (strlen($datacells[$key][5])>0){
                          $datetime=$datacells[$key][5];
                       }else{
                          $datetime=date('Y-m-d H:i:s');
                       }
                       $insert .= "('".$datacells[$key][1]."','".$datacells[$key][3]."-".$datacells[$key][4]."','".$datacells[$key][7]."--".$datacells[$key][6]."',1,'".$datacells[$key][2]."','".$datetime."','".$datacells[$key][8]."','".$datacells[$key][5]."','".$datacells[$key][8]."','".$insert_id."'),"; 
                    }
                    $insert=trim($insert,',');
                    $sql = "INSERT INTO message_bak (tel, addr, y_title, bs, wz, timeadd,ip,uid,telzt,excel_id) VALUES".$insert;
                    $res = mysql_query($sql);
                }
这里将$data中的数据提出并且销毁$data了。目的是要代码简便点,不过实际环境中无需如此,目前通过上面的程序,导入一个10000条的excel文件,仅需要

6秒。当然如果取消对变量的操作,再优化优化程序(缓冲区的使用是为了实时显示出来每次执行的sql语句,如果不需要这种做法,当然可以放弃使用缓冲区),与excel类,速度应该还会更快一点,以下为程序执行时间对比:


 5.7213270664215   单独使用excel类读取数据花费的时间

 6.3743650913239   使用excel类读取数据以及插入数据库所使用的时间

    由此可以看出,大部分时间是花费在了读取excel上面,使用缓冲区插入msyql还是比较可取的,有个小提示,每一次使用缓冲区之后一定要进行 刷新操作

以上只是我的测试代码,具体的sql需要根据各位同学们的需求进行改写,而且上面的代码增加了一个关于导入数据的字典表,即实时导入数据的操作记录,不需要的也可以删除


在工作中,很多时候需要把excel中的数据读取出来,导入mysql中,以前的代码,都是通过Spreadsheet_Excel_Reader插件直接取出来,进行for循环,组合成一个长的sql语句,这
基于Eggjs框架的Nodejs实现上传excel/读取excel/批量导入mysql等功能//controller层代码asyncuploadSimCardFile(){const{ctx}thi
PHP批量导入excell表格到mysql数据库,本人通过亲自测试,在这里分享给大家1,下载phpexcell类库网上搜索可以下载,这里不写地址2,建html文件<formmethodpost
phpspreadsheet批量导入使用的进阶版关联文章:thinkphpthinkadmin使用phpspreadsheet批量导入excel数据https://blogcsdnnet/qq_363
1、第一步我们得到了一个excel表,里面有很多需要我们导入数据。2、删除第1行准考证号XXX只保留我们需要的数据部分。3、单击文件另存为,类型选择为CSV(逗号分隔)(*csv),将excel表另
先生成Excel模板,然后导入Excel数据mysql,每条数据对应图片上传到阿里云1<?php2/**3*CreatedbyPhpStorm4*User:Administrator5*Da
本系列课程适用人群:python零基础数据分析的朋友;在校学生;职场中经常要处理各种数据表格,或大量数据(十万级以上)的朋友;喜欢图表可视化的朋友;系列视频目前可在B站观看,会定期更新,欢迎大家吐槽
近在做Excel文件导入数据到数据库。网站如果想支持批量插入数据,可以制作一个上传Excel文件,导入里面的数据内容到MySQL数据库的小程序。要用到的工具:ThinkPHP:轻量级国产PHP开发框
数据导入数据库的工作经常会遇到,这篇博文我们介绍一下linux系统下如何通过命令将excel中的数据批量导入到非关系型数据库mongodb中。步骤一:将bigdataxlsx另存为bigdatacsv
这里介绍一个直接将excel文件导入mysql的例子。我花了一晚上的时间测试,无论导入简繁体都不会出现乱码,非常好用。PHPExcelReader,下载地址:http://sourceforgenet