PHP read CSV Large file Import Database example

For millions of data volumes of CSV file, the file size may reach hundreds of m, if simply read, it is likely to have a time-out or a card-dead phenomenon.

Batch processing is necessary in order to successfully import data from a CSV file into a database.

The following function reads a few rows of data specified in a CSV file:

/** * Csv_get_lines read a few rows of data in the CSV file * @param $csvfile CSV file path * @param $lines number of rows read * @param $offset the starting line * @return Array * */functionCsv_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;} Call Method:$data= Csv_get_lines (' path/bigfile.csv ', 10, 2000000);Print_r($data);

The function mainly adopts the idea of line positioning, and the file pointer positioning is achieved by skipping the number of start lines.

As for how data is put into storage, this article is no longer detailed.

The above function to 500M within the file has been tested, run smoothly, for 1GB files found a bit slow, and then went on to find ways.

How to operate a large file quickly and completely there are still some problems.

1, how to quickly get the total number of CSV large file?

Method One: Directly obtain the contents of the file, using newline characters to split the total number of rows, this method is feasible for small files, processing large files is not feasible;

Method Two: Use Fgets row by line traversal, arrive the total number of rows, this method is better than the method, but the large file still has the possibility of timeout;

Method Three: With the help of Splfileobject class, direct pointer to the end of the file, through the Splfileobject::key method to obtain the total number of rows, this approach is feasible and efficient.

Specific implementation methods:

$csv _file = ' path/bigfile.csv '; $SPL _object New Splfileobject ($csv _file, ' RB '); $spl _object->seek (filesize($csv _file)); Echo $spl _objectkey();

2, how to quickly get the data of large CSV file?

Still using PHP's Splfileobject class, the Seek method enables quick positioning.

$csv _file= ' Path/bigfile.csv ';$start= 100000;//start www.111cn.net read from line 100,000th$num= 100;//Read 100 rows$data=Array();$SPL _object=NewSplfileobject ($csv _file, ' RB ');$SPL _object->seek ($start); while($num--&&!$SPL _object-EOF ()) { $data[] =$SPL _object-Fgetcsv(); $SPL _object-Next();}Print_r($data);

Combine the above two points to sort the read class into a CSV file:

classCsvreader {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 functionSet_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 functionGet_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 =NewSplfileobject ($this->csv_file, ' RB '); }  return true; }  Public functionGet_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 functionGet_lines () {if(!$this-_open_file ()) {   return false; }  $this->spl_object->seek (filesize($this-csv_file)); return $this->spl_object->Key(); }  Public functionGet_error () {return $this-error;}}

The calling method is as follows:

include (' CsvReader.class.php '); $csv _file = ' path/bigfile.csv '; $csvreader New Csvreader ($csv _file); $line _number $csvreader-Get_lines (); $data $csvreader->get_data (_number,echo$linechr(ten); Print_r ($data);

In fact, the above Csvreader class is not only for CSV large files, for other text types of large files or oversized files are also available, provided that the class Fgetcsv method is slightly changed to current.

PHP read CSV Large file Import Database example