How to parse a csv file that contains 15 million lines of data in php

Admin 1
Viewed 3459

I have a script which parses the CSV file and start verifying the emails. this works fine for 1000 lines. but on 15 million lines it shows memory exhausted error. the file size is 400MB. any suggestions? how to parse and verify them?

Server Specs: Core i7 with 32GB of Ram

function parse_csv($file_name, $delimeter=',') {
  $header = false;
  $row_count = 0;
  $data = [];

  //  clear any previous results
  reset_parse_csv();

  // parse
  $file = fopen($file_name, 'r');
  while (!feof($file)) {
    $row = fgetcsv($file, 0, $delimeter);
    if ($row == [NULL] || $row === FALSE) { continue; }
    if (!$header) {
      $header = $row;
    } else {
      $data[] = array_combine($header, $row);
      $row_count++;
    }
  }
  fclose($file);

  return ['data' => $data, 'row_count' => $row_count];

}

function reset_parse_csv() {
  $header = false;
  $row_count = 0;
  $data = [];    
}
fgetcsv csv php
3 Answers

Iterating over a large dataset (file lines, etc.) and pushing into array it increases memory usage and this is directly proportional to the number of items handling. So the bigger file, the bigger memory usage - in this case. If it's desired a function to formatting the CSV data before processing it, backing it on the of generators sounds like a great idea.

Reading the PHP doc it fits very well for your case (emphasis mine):

A generator allows you to write code that uses foreach to iterate over a set of data without needing to build an array in memory, which may cause you to exceed a memory limit, or require a considerable amount of processing time to generate.

Something like this:



function csv_read($filename, $delimeter=',')
{
    $header = [];
    $row = 0;
    # tip: dont do that every time calling csv_read(), pass handle as param instead ;)
    $handle = fopen($filename, "r"); 

    if ($handle === false) {
        return false;
    }

    while (($data = fgetcsv($handle, 0, $delimeter)) !== false) {

        if (0 == $row) {
            $header = $data;
        } else {
            # on demand usage
            yield array_combine($header, $data);
        }

        $row++;
    }
    fclose($handle);
}

And then:

$generator = csv_read('rdu-weather-history.csv', ';');

foreach ($generator as $item) {
   do_something($item);
}

The major difference here is: you do not get (from memory) and consume all data at once. You get items on demand (like a stream) and process it instead, one item at time. It has huge impact on memory usage.

P.S.: The CSV file above has taken from: https://data.townofcary.org/api/v2/catalog/datasets/rdu-weather-history/exports/csv

user_12628200

It is not necessary to write a generator function. The SplFileObject also works fine.

$fileObj = new SplFileObject($file);

$fileObj->setFlags(SplFileObject::READ_CSV 
  | SplFileObject::SKIP_EMPTY 
  | SplFileObject::READ_AHEAD 
  | SplFileObject::DROP_NEW_LINE
);
$fileObj->setCsvControl(';');

foreach($fileObj as $row){
  //do something 
}

I tried that with the file "rdu-weather-history.csv" (> 500KB). memory_get_peak_usage() returned the value 424k after the foreach loop. The values ​​must be processed line by line. If a 2-dimensional array is created, the storage space required for the example increases to more as 8 Mbytes.

user_72712210

One thing you could possibly attempt, is a Bulk Import to MySQL which may give you a better platform to work from once it's imported.

LOAD DATA INFILE '/home/user/data.csv' INTO TABLE CSVImport; where CSVimport columns match your CSV.

Bit of a left field suggestion, but depending on what your use case is can be a better way to parse massive datasets.

user_424460
Related