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?

Ross · Accepted Answer · 2011-12-09 06:10:28Z

6

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]} ... )");
}

edited Dec 9, 2011 at 6:10

answered Dec 9, 2011 at 5:33

Ross

47.2k39 gold badges124 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

chromedude Over a year ago

Ok, this stopped the fatal error. The thing now is that it echos this long list of "Array" instead of the actual values.

Ross Over a year ago

$data is an array of fields in one row, which you can check with print_r($data) or var_dump($data).

chromedude Over a year ago

Then how do you $rows so that you get each value so that you can insert it into the database?

Ross Over a year ago

I updated my answer, as xdazz said you don't need to read the whole file into another array, only to loop over it again.

Siddharth Over a year ago

This still gives the Fatal error of exceeding the allowed memory size.

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

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:

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 9, 2011 at 5:42

Will Bickford

5,3862 gold badges35 silver badges46 bronze badges

Comments

xdazz · Accepted Answer · 2014-03-21 00:21:52Z

5

You don't need to read all csv data from the file into memory before processing.

Instead, create a while loop that reads from your file one line at a time. Each time you read from the file, you should insert a row into your database.

Or read several lines and insert several rows at one time.

Example:

   $i = 0; 
   while (($data = fgetcsv($handle, 0, ",") !== FALSE) {
        $rows[] = $data;
        $i++;
        // insert 100 rows at one time.
        if ($i % 100 === 0) {
            //insert these rows to db
            insert_to_db($rows);
            //then reset $rows
            $rows = array();
        }
    }
    insert_to_db($rows);

edited Mar 21, 2014 at 0:21

answered Dec 9, 2011 at 5:35

xdazz

161k38 gold badges255 silver badges278 bronze badges

3 Comments

chromedude Over a year ago

Ok, so how would you suggest I loop through rows to insert them in the db? I want each row to be a row in the db. I tried this to loop through: foreach($rows as $key => $value){echo $value.'</br>';}, but came out with just a list of "Array".

xdazz Over a year ago

@chromedude you are echoing $value which is nothing related with saving the data to db, just find a tutorial that show you how to put the data into the db.

Josh Koberstein Over a year ago

If you follow this approach, do not forget to make one more insert_to_db($rows) call after the loop to insert the remainder.

Eugene Manuilov · Accepted Answer · 2011-12-09 06:20:37Z

Use standard MySQL LOAD DATA INFILE statement to escape reading/parsing/inserting data via PHP:

 function import_csv( $table, $afields, $filename, $delim = ',', $enclosed = '"', $escaped = '\\',  $lineend = '\\r\\n', $hasheader = FALSE) {      
    if ( $hasheader ) $ignore = "IGNORE 1 LINES ";
    else $ignore = "";
    $q_import = 
        "LOAD DATA INFILE '" . $_SERVER['DOCUMENT_ROOT'] . $filename . "' INTO TABLE " . $table . " " .
        "FIELDS TERMINATED BY '" . $delim . "' ENCLOSED BY '" . $enclosed . "' " .
        "    ESCAPED BY '" . $escaped . "' " .
        "LINES TERMINATED BY '" . $lineend . "' " . $ignore . "(" . implode(',', $afields) . ")"
    ;
    return mysql_query($q_import);
}

In this case you don't need to open/read CSV file in PHP, MySQL will handle data import by itself.

Collectives™ on Stack Overflow

Out of memory error when reading a large file

4 Answers 4

5 Comments

Use mysqlimport instead

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Use mysqlimport instead

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related