Example of PHP reading CSV large files into the database

This article introduces in detail the example of a csv file quickly imported into a mysql database in php, although there are some flaws in the process from the simplest hundreds of MB to the last use of plug-ins to implement several GB data imports, the results are still good. for CSV files with millions of data records ,... this article introduces in detail the example of a csv file quickly imported into a mysql database in php, although there are some flaws in the process from the simplest hundreds of MB to the last use of plug-ins to implement several GB data imports, the results are still good.

For CSV files with millions of data records, the file size may reach several hundred MB. if you simply read the file, it is likely that the file times out or gets stuck.

In order to successfully import the data in the CSV file into the database, batch processing is necessary. the following function reads the specified rows of data in the CSV file. the code is as follows:

The function uses the line locating method to locate the file pointer by skipping the starting number of lines. This article will not detail how to import data into the database.

The above functions have been tested for files within MB and run smoothly. it is a little slow for 1 GB files, so I will try again.

There are still some problems about how to quickly and completely operate large files.

1. how to quickly obtain the total number of rows of a large CSV file?

Method 1: directly obtain the file content and use a linefeed to split the total number of rows. this method is feasible for small files and cannot be used to process large files;

Method 2: Use fgets to traverse a row to obtain the total number of rows. this method is better than the method, but large files may still time out;

Method 3: Use the SplFileObject class to directly locate the pointer to the end of the file and use the SplFileObject: key method to obtain the total number of rows. this method is feasible and efficient.

The code is as follows:

$csv_file = 'path/bigfile.csv'; $spl_object = new SplFileObject($csv_file, 'rb'); $spl_object->seek(filesize($csv_file)); echo $spl_object->key();

2. how to quickly obtain the data of a large CSV file?

The SplFileObject class of PHP is still used for fast locating through the seek method. the code is as follows:

$ Csv_file = 'path/bigfile.csv '; $ start = 100000; // reads $ num = 100,000th from www.phprm.com starting from row 100; // reads 100 rows $ data = array (); $ spl_object = new SplFileObject ($ csv_file, 'RB'); $ spl_object-> seek ($ start); while ($ num --&&! $ Spl_object-> eof () {$ data [] = $ spl_object-> fgetcsv (); $ spl_object-> next ();}

Print_r ($ data );

Based on the above two points, the class for reading csv files is compiled. the code is as follows:

 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;    }}?>

The call 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(10); echo $line_number, chr(10); print_r($data);

In fact, the above CsvReader class is not only for large CSV files, but also for large files or large files of other text types, provided that the fgetcsv method in the class can be slightly changed to current.

Tutorial link:

Reprint at will ~ However, please keep the tutorial address★