Fatal Error (running out of memory) on writing more than 4000 rows approx

[shreygokani]

Hello.

I am trying to solve this since 2 days. Also read many threads on stackoverflow. I am trying to write an Excel/CSV file.

My code works fine except when rows exceed somewhere around 4000, with 15 columns, it throws a fatal error saying the memory limit has exhausted. I increased my memory limit in php.ini at 512MB, but no help! Whatever amount of memory I give, it gets exhausted after this limit. I am reading data from text file of around 700 kb. If anyone faced similar thing, please help!

[MarkBaker]

So what cell caching methods have you tried using?

[shreygokani]

by default, it throws this fatal error.

tried sqlite and phpTemp. page becomes unresponsive in both caching method.

[MarkBaker]

Do you mean unresponsive or simply slower? PHPExcel cannot exceed PHP's memory limits, so cell caching is method to reduce memory usage, but at cost in execution speed

[shreygokani]

upon using these 2 caching methods, browser says "site is unresponsive"

[abstract-base-method]

Are you inserting data through a loop or PHPExcel's fromArray?

[shreygokani]

@lemoney I am inserting through loop. because I have to insert data vertically.

[shreygokani]

sorry for late response. I was unavailable.

[abstract-base-method]

@shreygokani the fromArray method allows you to easily put data into a sheet at volume. Like this example:

// example PDO Object
$PDO = new PDO('Connection String');
// Query database for 4000 rows of data
$PDO->query('SELECT * FROM example_table LIMIT 4000');
// Fetch data into an array NOTE: I use FETCH_ASSOC so only one copy returns 
$Data = $PDO->fetchAll(PDO::FETCH_ASSOC);
// Start PHPExcel Object
$wb = new PHPExcel();
// ... setting up wb ...
$sheet = $wb->getSheet(0);
/* Take $Data and just dump the array into the table. This would output a
the result in each column by row. EX:
column 1  | column 2  | column 3
-----------------------------------
Row 1       | Row 1      | Row 1
-----------------------------------
Row 2       | Row 2      | Row 2

etc....
*/
$sheet->fromArray($Data, '', 'A2');

Does this solution fix the issue?

[shreygokani]

@lemoney fromArray method inputs data row wise. I wish to add data column wise. do you get that? My program is not fetching row from a table. it is doing a file conversion. logic is complex. If I can add my array into column directly, I would not have used loop.

[abstract-base-method]

Ah, Okay. I wasn't understanding correctly. My apologies. Okay first question is how many columns are you generating? the full 4000+?

[shreygokani]

@lemoney Max columns are 20.
its rows, sometimes go more than 4000.

[abstract-base-method]

Person I am so sorry for not responding sooner! Friday's are crazy. Are you watching resources while running the script (*nix systems [h]top) or task manager in windows?

[shreygokani]

@lemoney sorry for late reply. i was experimenting with fromArray. but that too fails!
how do i watch resources in php? i m on shared hosting

[abstract-base-method]

@shreygokani I am so sorry for the last response. If you are on a shared host try

// default
memory_get_usage();
// all memory
memory_get_usage();

I would do them before trying the insert, then use

try {
} catch ( /* Enter the exception type */ $e {
memory_get_usage();
print_r($e);
}
memory_get_usage();

To see what the differences are. You will need to echo out or insert into a DB. You could be maxing out your VPS. Have you tried

ini_set()

to increase PHP available memory?

[shreygokani]

@lemoney i am allocating 256mb. do i need more than that? I guess not.