How to work around a memory error when reading a large amount of records from a spreadsheet?

Question

I have not yet found a solution to this. I was using memory_limit to remove the error but it still doesn't insert all records into the database. I have 20K records.

Here is the error I get

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in C:\xampp\htdoc\a\b\excel_reader.php on line 1367

Any ideas on how to fix it?

Here's the code:

ini_set('memory_limit', '512M');

$format_file = $_POST['fileexcel'];
$format_file = $_FILES['fileexcel']['tmp_name'];

if (strlen($format_file)<1){
echo "empty file";
exit();
}
$data = new Spreadsheet_Excel_Reader($format_file);
$result = $data->rowcount($sheet_index=0);
for ($i=2; $i<=$result; $i++){

    $data1 = $data->val($i,1);
    $data2 = $data->val($i,5);
    $data3 = $data->val($i,10);

    $q = mysql_query("INSERT INTO t (k,m,n) VALUES ('$data1','$data2','$data3')") or die(mysql_error());

    if ($result) {

        echo "success";

    }
    else {
        echo "failed";
    }


}

Would it be possible for you to stagger the INSERTS so you're inserting a chunk at a time. Generally this would be better, plus you could feedback progress to the client. — Mark
– Mark, Commented Mar 8, 2015 at 22:55
i just add the code @mark .. there's an error ? i've 20.000 data ... — TARA
– TARA, Commented Mar 8, 2015 at 23:02
The issue here is that you try to load all data into memory at once. Such an approach cannot scale. Instead you should only load a small chunk, maybe even a line, process that and then move on to the next chunk. That way the memory footprint stays constantly small, regardless of how huge the amount of data is you are trying to load. — arkascha
– arkascha, Commented Mar 8, 2015 at 23:03
okey thanks mark , arkascha .. any recomend how many chunk for better process ? — TARA
– TARA, Commented Mar 8, 2015 at 23:18
You can find out a suitable chunk size by experimentation - too small and you'll do too many reads from the spreadsheet, which might cause some slowness, and too large you'll run out memory. I'd start with 100 and see how you get on. You may find that changing it, within the limits imposed by memory, does not change performance too greatly anyway. — halfer
– halfer, Commented Apr 2, 2015 at 17:05

arkascha · Accepted Answer · 2015-03-08 22:40:45Z

1

PHP has a builtin default value for such configuration options.

In case of the memory-limit directive this is 128MB. So you are using it.

answered Mar 8, 2015 at 22:40

arkascha

43.2k8 gold badges61 silver badges97 bronze badges

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

2 Comments

TARA Over a year ago

memory_limit can remove the error BUT data record not full insert into database .. may you have another solution ?

arkascha Over a year ago

Well, I'd say enhance your script. If I got that right you are trying to load data into a database. A script for such purpose should never use such huge amounts of memory. But I cannot say anything more specific without you posting the code...

Collectives™ on Stack Overflow

How to work around a memory error when reading a large amount of records from a spreadsheet?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related