CSV格式数据处理

基础知识

  1. php函数:fgetcsv,fputcsv

    类似于fgets,fputs的用法,只不过是针对csv文件.

  2. msyql语句:LOAD DATA INFILE

    LOAD DATA INFILE语句用于高速地从一个文本文件中读取并导入数据.

    LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE `table` CHARACTER SET GBK FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';
  3. \t

    tab键,制表符 插入\t避免长数值显示为"科学计数"

导入

流程逻辑

  1. 读取文件

  2. 处理数据

  3. 写入新的数据文件

  4. 导入数据文件到数据库

先生成新的数据文件,然后使用mysql的LOAD DATA INFILE语句,直接导入文件,这样对比一遍读取文件,一边插入sql.效率高很多,特别是针对于几万几十万的大数据处理,对比尤其强烈

详细代码

require 'config.php';
/*
读取文件
*/
// 读取的文件
$fp = fopen('E:/a.csv', 'r');
// 写入的新文件
$new_fp = fopen('E:/data.csv', 'a');
// 循环读取数据
$data = array();
// 表头标记
$ishead = true;
// 开始循环
while($da = fgetcsv($fp, 1000)) {
// 跳过表头
if ($ishead) {
$ishead = false;
continue;
}
// 处理数据并写入新文件
foreach ($da as &$d) {
$d = trim($d);
}
fputcsv($new_fp, $da);
}
// 关闭文件
fclose($fp);
fclose($new_fp);
// mysql导入csv文件到数据库
$conn = @mysql_connect($config['db_host'],$config['db_user'],$config['db_pwd']) or die("Connection occur error");
mysql_select_db($config['db_name'],$conn) or die("Connection occur error");
mysql_query("LOAD DATA LOCAL INFILE 'E:/data.csv' INTO TABLE `test2` CHARACTER SET GBK FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\n';") or die ('Error: '.mysql_error());
// 删除文件
// unlink('E:/a.csv');
unlink('E:/data.csv');

导出

流程逻辑

  1. 设置表头

  2. 循环分步处理大数据

  3. 处理数据

  4. 输出数据

详细代码

require 'config.php';
// 计时开始
$start = microtime(true);
$filename = '用户信息' . date('YmdHis');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $filename . '.csv"');
header('Cache-Control: max-age=0');
// 处理字符以适应CSV格式
function dostr($str) {
return iconv('utf-8', 'gbk', $str) . "\t";
}
// 数据库连接
$pdo = new PDO($config['db_type'] . ':host=' . $config['db_host'] . ';dbname=' . $config['db_name'] . '', $config['db_user'], $config['db_pwd']);
$pdo->query('set names ' . $config['db_charset'] . ';');
$fp = fopen('php://output', 'a');
// 输出列名信息
$head = array('姓名', '简码', '登录名', '密码', '部门编号', '公司码', '电话号码', '备注');
foreach ($head as $i => &$v) {
$v = dostr($v);
}
fputcsv($fp, $head);
$where = 'WHERE `id` < 1000';
// 计算总数,分步循环
$qu = $pdo->prepare('SELECT `id` FROM `test` ' . $where);
$qu->execute();
$total = $qu->rowCount();
// 开始循环,执行导出数据
for ($i=0; $i < $total; $i+=$config['db_limit']) {
// 组成SQL语句查询数据
$limit = 'LIMIT ' . $i . ', ' . $config['db_limit'];
$sql = 'SELECT `name`, `jm`, `login_name`, `password`, `depart_id`, `company_code`, `phone`, `remark` FROM `test` ' . $where . ' ' . $limit . ';';
$data = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
// 处理并输出数据
foreach ($data as $da) {
foreach ($da as &$d) {
$d = dostr($d);
}
fputcsv($fp, $da);
}
// 刷新缓存数据,避免内存过大
unset($data, $row);
ob_flush();
flush();
}
// 计时结束
fputcsv($fp, array('use:' . round(microtime(true)-$start,3) . 'S'));
// 关闭文件
fclose($fp);

导出性能分析

性能分析图

服务器环境:windows10+IIS10+mysql5.6

注意要点

  1. 主键(id)的处理.

  2. 在循环里面,不要(或者尽可能少的)使用sql语句.

  3. 读取文件时,表头的处理.

  4. 字符编码处理.

  5. 导出数据时,表头必须和数据内容对应,且顺序一致.

  6. 导出数据不能用异步,只能打开连接.导入可以用异步.