用php导入10W条+ 级别的csv大文件数据到mysql。导出10W+级别数据到csv文件
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了用php导入10W条+ 级别的csv大文件数据到mysql。导出10W+级别数据到csv文件,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9111字,纯文字阅读大概需要14分钟。
内容图文
![用php导入10W条+ 级别的csv大文件数据到mysql。导出10W+级别数据到csv文件](/upload/InfoBanner/zyjiaocheng/476/1254da7a7fd64af8861691427b3ee05d.jpg)
- $handle=fopen("1.csv","r");
- while(!feof($handle)){
- $buffer=fgetss($handle,2048);
- $row = mb_convert_encoding(trim($buffer), ‘utf-8‘, ‘gbk‘); //很重要。转换成UTF8格式,不然容易产生乱码
- $data=explode(",",$row); //转换成数组
- $insertRows[] = $data;
- } //这样所有的csv文件就生成一个二维数组$insertRows;
2. 函数 fgetcsv($handel,2048,‘,‘),返回数组,它就是explode(",",fget($handel))的组合。这种方法使用于小的csv 文件。而且不适合有汉字的csv 文件。 [php] view plain copy
- $handle=fopen("1.csv","r");
- while($data=fgetcsv($handle,1000,",")){
- $insertRows[] = $data;
- } //这样所有的csv文件就生成一个二维数组$insertRows;
- $handle=fopen("1.csv","r");
- //将文件一次性全部读出来
- $excelData = array();
- $content = trim(file_get_contents($fileName));
- $excelData = explode("\n",$content);
我们先将所有的文件一次性读出来。放到一个数组$excelData 中。这个时候,我们就可以不用管这个csv 文件了,纯粹了php 操作数组了。所以。不会崩溃异常: [php] view plain copy
- $chunkData = array_chunk($excelData , 5000); // 将这个10W+ 的数组分割成5000一个的小数组。这样就一次批量插入5000条数据。mysql 是支持的。
- $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‘, ‘gbk‘);//转码
- $v = explode(‘,‘, trim($string));
- $row = array();
- $row[‘cdate‘] = empty($v[0]) ? date(‘Y-m-d‘) : date(‘Y-m-d‘,strtotime($v[0]));
- $row[‘business‘] = $v[1];
- $row[‘project‘] = $v[2];
- $row[‘shopname‘] = $v[3];
- $row[‘shopid‘] = $v[4];
- $row[‘fanli‘] = formatNumber($v[5]);
- $row[‘fb‘] = $v[6] * 100;
- $row[‘jifen‘] = $v[7];
- $sqlString = ‘(‘."‘".implode( "‘,‘", $row ) . "‘".‘)‘; //批量
- $insertRows[] = $sqlString;
- }
- $result = $model->addDetail($insertRows); //批量将sql插入数据库。
- }
插入数据库当然是批量插入了: [php] view plain copy
- public function addDetail($rows){
- if(empty($rows)){
- return false;
- }
- //数据量较大,采取批量插入
- $data = implode(‘,‘, $rows);
- $sql = "INSERT IGNORE INTO tb_account_detail(cdate,business,project,shopname,shopid,fanli,fb,jifen)
- VALUES {$data}";
- $result = $this->query($sql);
- return true;
- }
ok ! 亲测试。10W 数据。6个字段。插入需要10秒。 -----------------2013-12-18 日更新---------------------
3. 导出10W+的数据到csv
放弃之前写的一篇博客中用到的方法:http://blog.csdn.net/think2me/article/details/8596833 。原因是:当超过50W+ 以上的数据时,有可能浏览器崩溃,内存超。 这个方法是写文件的方式。然后再把文件弹出下载。 [php] view plain copy- public function dump2Excel() {
- set_time_limit(0);
- ini_set(‘memory_limit‘, ‘640M‘);
- //获取列表
- $name = $this->getActionName();
- $model = D(GROUP_NAME . ‘.‘ . $name);
- $map = $this->_search();
- //文件名
- if (isset($_GET[‘error‘]) && $_GET[‘error‘] > 0) {
- $filename = C(‘IMG_PATH‘).‘account_data_error_‘ . $map[‘action_id‘] . ‘_‘ . date(‘Y-m-d‘, mktime()) . ‘.csv‘;
- }else{
- $filename = C(‘IMG_PATH‘).‘account_data_all_‘ . $map[‘action_id‘] . ‘_‘ . date(‘Y-m-d‘, mktime()) . ‘.csv‘;
- }
- //用户信息,商家ID,联盟,商家订单号,商品分类,确认类别,下单时间,完成时间,
- //实际支付金额,佣金,佣金补贴,返利,F币,论坛积分,备注,强制入库
- // ‘user_info‘, ‘shopid‘, ‘league‘, ‘order_id‘, ‘classify‘, ‘confirm_type‘,
- //‘buydate‘, ‘paydate‘, ‘real_pay‘, ‘commission‘, ‘commission_plus‘,
- // ‘fanli‘, ‘jifen‘, ‘bbs‘, ‘remarks‘, ‘persist_execute‘, ‘unique_sign‘,‘error_code‘
- $header[] = iconv("utf-8", "gb2312", "用户信息");
- $header[] = iconv("utf-8", "gb2312", "商家ID");
- $header[] = iconv("utf-8", "gb2312", "联盟");
- $header[] = iconv("utf-8", "gb2312", "商家订单号");
- $header[] = iconv("utf-8", "gb2312", "商品分类");
- $header[] = iconv("utf-8", "gb2312", "确认类别");
- $header[] = iconv("utf-8", "gb2312", "下单时间");
- $header[] = iconv("utf-8", "gb2312", "完成时间");
- $header[] = iconv("utf-8", "gb2312", "实际支付金额");
- $header[] = iconv("utf-8", "gb2312", "佣金");
- $header[] = iconv("utf-8", "gb2312", "佣金补贴");
- $header[] = iconv("utf-8", "gb2312", "返利");
- $header[] = iconv("utf-8", "gb2312", "F币");
- $header[] = iconv("utf-8", "gb2312", "论坛积分");
- $header[] = iconv("utf-8", "gb2312", "备注");
- $header[] = iconv("utf-8", "gb2312", "强制入库");
- $header[] = iconv("utf-8", "gb2312", "唯一标识");
- $header[] = iconv("utf-8", "gb2312", "错误信息");
- $headerFile = implode(‘,‘, $header);
- //写入标题
- @unlink($filename);
- file_put_contents($filename, $headerFile."\n");
- //获取所有error_code
- $list = D(‘Fanli‘)->table(‘tb_account_action_data_error_code‘)->field(‘id,err_msg‘)->findAll();
- $error_msg = array();
- foreach ($list as $value) {
- $error_msg[$value[‘id‘]] = $value[‘err_msg‘];
- }
- //导入错误的数据
- if (isset($_GET[‘error‘]) && $_GET[‘error‘] > 0) {
- $map[‘error_code‘] = array(‘gt‘, 0);
- }
- if (!empty($map[‘action_id‘])) {
- $allCount = $model->where($map)->field(‘count(1) as count‘)->select();
- $pageLimit = ceil($allCount[0][‘count‘]/self::PAGE_COUNT);
- $voList = array();
- //打开文件
- if (!$handle = fopen($filename, ‘a‘)) {
- echo "不能打开文件 $filename";
- exit;
- }
- //分页获取
- for($i=0;$i<$pageLimit;$i++){
- $count = self::PAGE_COUNT;
- $start = $count * $i;
- $limit = "$start,$count";
- $voList = $model->where($map)->limit($limit)->order(‘id desc‘)->findAll();
- //写入文件
- $excelString = array();
- foreach ($voList as $v) {
- $dumpExcel = array();
- $dumpExcel[] = mb_convert_encoding($v[‘user_info‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘shopid‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘league‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘order_id‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘classify‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘confirm_type‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = "‘".mb_convert_encoding($v[‘buydate‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = "‘".mb_convert_encoding($v[‘paydate‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘real_pay‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘commission‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘commission_plus‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘fanli‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘jifen‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘bbs‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($v[‘remarks‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = intval($v[‘persist_execute‘]);
- $dumpExcel[] = mb_convert_encoding($v[‘unique_sign‘], ‘GBK‘, ‘UTF-8‘);
- $dumpExcel[] = mb_convert_encoding($error_msg[$v[‘error_code‘]], ‘GBK‘, ‘UTF-8‘);
- $excelString[] = implode(‘,‘,$dumpExcel);
- }
- //只能一行行些。不然容易漏
- foreach($excelString as $content){
- fwrite($handle, $content . "\n");
- }
- unset($excelString);
- }
- fclose($handle);
- }
- //导出下载
- header("Content-type: application/octet-stream");
- header(‘Content-Disposition: attachment; filename="‘ . basename($filename) . ‘"‘);
- header("Content-Length: ". filesize($filename));
- readfile($filename);
- }
用php导入10W条+ 级别的csv大文件数据到mysql。导出10W+级别数据到csv文件
标签:base code bsp attach link 打开文件 rem 数组分割 nes
本文系统来源:http://www.cnblogs.com/houdj/p/6492004.html
内容总结
以上是互联网集市为您收集整理的用php导入10W条+ 级别的csv大文件数据到mysql。导出10W+级别数据到csv文件全部内容,希望文章能够帮你解决用php导入10W条+ 级别的csv大文件数据到mysql。导出10W+级别数据到csv文件所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。