PHP读取CSV大文件导入数据库的示例

/**
 * csv_get_lines 读取CSV文件中的某几行数据
 * @param $csvfile csv文件路径
 * @param $lines 读取行数
 * @param $offset 起始行数
 * @return array
 * */
function csv_get_lines($csvfile, $lines, $offset = 0) {
    if(!$fp = fopen($csvfile, 'r')) {
     return false;
    }
    $i = $j = 0;
 while (false !== ($line = fgets($fp))) {
  if($i++ < $offset) {
   continue;
  }
  break;
 }
 $data = array();
 while(($j++ < $lines) && !feof($fp)) {
  $data[] = fgetcsv($fp);
 }
 fclose($fp);
    return $data;
}
调用方法：

$data = csv_get_lines('path/bigfile.csv', 10, 2000000);

print_r($data);

class CsvReader {
 private $csv_file;
 private $spl_object = null;
 private $error;
 
 public function __construct($csv_file = '') {
  if($csv_file && file_exists($csv_file)) {
   $this->csv_file = $csv_file;
  }
 }
 
 public function set_csv_file($csv_file) {
  if(!$csv_file || !file_exists($csv_file)) {
   $this->error = 'File invalid';
   return false;
  }
  $this->csv_file = $csv_file;
  $this->spl_object = null;
 }
 
 public function get_csv_file() {
  return $this->csv_file;
 }
 
 private function _file_valid($file = '') {
  $file = $file ? $file : $this->csv_file;
  if(!$file || !file_exists($file)) {
   return false;
  }
  if(!is_readable($file)) {
   return false;
  }
  return true;
 }
 
 private function _open_file() {
  if(!$this->_file_valid()) {
   $this->error = 'File invalid';
   return false;
  }
  if($this->spl_object == null) {
   $this->spl_object = new SplFileObject($this->csv_file, 'rb');
  }
  return true;
 }

 public function get_data($length = 0, $start = 0) {
  if(!$this->_open_file()) {
   return false;
  }
  $length = $length ? $length : $this->get_lines();
  $start = $start - 1;
  $start = ($start < 0) ? 0 : $start;
  $data = array();
  $this->spl_object->seek($start);
  while ($length-- && !$this->spl_object->eof()) {
   $data[] = $this->spl_object->fgetcsv();
   $this->spl_object->next();
  }
  return $data;
 }
 
 public function get_lines() {
  if(!$this->_open_file()) {
   return false;
  }
  $this->spl_object->seek(filesize($this->csv_file));
  return $this->spl_object->key();
 }
 
 public function get_error() {
  return $this->error;
 }
}

include('CsvReader.class.php');

$csv_file = 'path/bigfile.csv';

$csvreader = new CsvReader($csv_file);

$line_number = $csvreader->get_lines();

$data = $csvreader->get_data(10);

echo $line_number, chr(10);

print_r($data);

Array ( [0] => id [Field] => id [1] => char(2) [Type] => char(2) [2] => utf8_general_ci [Collation] => utf8_general_ci [3] => NO [Null] => NO [4] => PRI [Key] => PRI [5] => [Default] => [6] => [Extra] => [7] => select,insert,update,references [Privileges] => select,insert,update,references [8] => [Comment] => )

Array ( [0] => title [Field] => title [1] => char(50) [Type] => char(50) [2] => utf8_general_ci [Collation] => utf8_general_ci [3] => YES [Null] => YES [4] => [Key] => [5] => [Default] => [6] => [Extra] => [7] => select,insert,update,references [Privileges] => select,insert,update,references [8] => 建议存储：标题、姓名等信息 [Comment] => 建议存储：标题、姓名等信息 )

Array ( [0] => des [Field] => des [1] => varchar(255) [Type] => varchar(255) [2] => utf8_general_ci [Collation] => utf8_general_ci [3] => YES [Null] => YES [4] => [Key] => [5] => [Default] => [6] => [Extra] => [7] => select,insert,update,references [Privileges] => select,insert,update,references [8] => [Comment] => )

…………

<?php

 header("Content-type:application/vnd.ms-excel");

 header("Content-Disposition:filename=xls_region.xls");

 $cfg_dbhost = 'localhost';

 $cfg_dbname = 'testdb';

 $cfg_dbuser = 'root';

 $cfg_dbpwd = 'root';

 $cfg_db_language = 'utf8';

 // END 配置

 //链接数据库

 $link = mysql_connect($cfg_dbhost,$cfg_dbuser,$cfg_dbpwd);

 mysql_select_db($cfg_dbname);

 //选择编码 www.111Cn.net

 mysql_query("set names ".$cfg_db_language);

 //users表

 $sql = "desc users";

 $res = mysql_query($sql);

 echo "<table><tr>";

 //导出表头（也就是表中拥有的字段）

 while($row = mysql_fetch_array($res)){

  $t_field[] = $row['Field']; //Field中的F要大写，否则没有结果

  echo "<th>".$row['Field']."</th>";

 }

 echo "</tr>";

 //导出100条数据

 $sql = "select * from users limit 100";

 $res = mysql_query($sql);

 while($row = mysql_fetch_array($res)){

  echo "<tr>";

  foreach($t_field as $f_key){

   echo "<td>".$row[$f_key]."</td>";

  }

  echo "</tr>";

 }

 echo "</table>";

?>

#!/usr/bin/php www.111cn.net
<?php
header('Content-Type:text/html;charset=utf-8');

define('BACK_PATH', dirname(__FILE__));

/**
为空数组就备份所有数据库
*/
$back_databases = array('learning','wp');

$mysql_host = 'localhost';

$mysql_user = 'root';

$mysql_passwd = 'root';

if($conn = mysql_connect($mysql_host, $mysql_user, $mysql_passwd)){
 
 $databases = array();
 
 mysql_select_db('mysql', $conn);
 
 $query  =mysql_query('show databases',$conn);
 
 while($row = mysql_fetch_array($query))
 {
  if(empty($row)) continue;
  
  $database = $row[0];
  
  if(!empty($back_databases) && !in_array($database, $back_databases)) continue;
    
  $sql_dir = BACK_PATH . "/{$database}.sql";
   
  $execs = "mysqldump --user={$mysql_user} --password={$mysql_passwd} {$database} > " . $sql_dir;
  
  system($execs);
  
  if(file_exists($sql_dir . '.gz')) {
     unlink($sql_dir . '.gz');
  }
    
  system('gzip ' . $sql_dir);
 }
}

<?PHP
include_once('lib/pclzip.lib.php');
$src="##源路径##";
$dest="##目标zip路径##";
$archive = new PclZip($dest);
$v_list = $archive->create($src);
if ($v_list == 0) {
   die("Error : ".$archive->errorInfo(true));
}else{
     print("Success");
}
?>

;mssql.charset = "ISO-8859-1"
mssql.charset = "GBK"

iconv('GB2312','UTF-8',$data)