Out of memory error when reading a large file

Question

I have a large csv that I want to parse and insert into my database. I have this PHP:

$target = '../uploads/'.$f;
$handle = fopen($target, "r");
$data = fgetcsv($handle, 0, ",");

$rows = array();

while ($data !== FALSE) {
    $rows[] =  $data;
}

fclose($handle);

if (count($rows)) {
             foreach ($rows as $key => $value) {

                  echo $value;

              }
          }

Every time I try to run my script I get this error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 35 bytes)

Any ideas how to do this?

Answer 1

I think this part is wrong:

$data = fgetcsv($handle, 0, ",");
$rows = array();
while ($data !== FALSE) {
    $rows[] =  $data;
}

One call to fgetcsv reads one line from $handle. You need to put fgetcsv in the loop condition:

$handle = fopen($target, "r");
$data = fgetcsv($handle, 0, ",");
while (($row = fgetcsv($handle, 0, ",")) !== FALSE) {
    // Example insert - obviously use prepared statements/escaping/another DAL
    $db->query("INSERT INTO tbl (columns..) VALUES ({$row[0]}, {$row[1]} ... )");
}

Answer 2

Use mysqlimport instead

While you can certainly parse and build queries with PHP you'll get much better performance by letting MySQL handle it directly. Your database will thank you.

<?php
exec("mysqlimport mysqlimport [options] db_name textfile1");
?>

Sources:

• http://www.php.net/manual/en/function.exec.php
• http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html