Importing a 1,000,000-row XLSX without a 2 GB memory_limit: streaming reads in PHP

aVadim • 03.09.2026 15:22 •

If you have ever imported a large Excel file in PHP, you have almost certainly seen this message:

Fatal error: Allowed memory size of 134217728 bytes exhausted

The classic scenario: a client sends an export of 300,000–500,000 rows, you open it with PhpSpreadsheet — and the script dies. You raise memory_limit to 512M — it still dies. To 2G — it works, but slowly, and setting such a limit in production is frightening. Sound familiar?

In this article we look at why that happens and how to read files of practically any size with flat memory use, using avadim/fast-excel-reader.

Everything below was verified against version 4.4.3.

Why Excel files eat memory

An XLSX file is a ZIP archive with XML files inside. A 10 MB file on disk can unpack into 100+ MB of XML. What happens next depends entirely on how the library reads that XML.

General-purpose libraries like PhpSpreadsheet build a full object model of the workbook in memory: every cell is an object with a value, a style, coordinates, and references to neighboring structures. That is necessary when you want to edit a file. But for importing data it is pure overhead: even a small file of a few hundred kilobytes can take up to 200 MB in memory, and a million-row file will not fit into any reasonable limit.

Hence the usual workarounds people recommend to each other on Stack Overflow:

  • setReadDataOnly(true) — helps partly, the object model is still built;
  • split the file into pieces before importing — it works, but that is a separate process and one more point of failure;
  • ini_set('memory_limit', '-1') — good until the first genuinely large file.

All of them fight the symptom. The cause is the “load everything, then process” architecture.

A different approach: a stream instead of an object model

FastExcelReader solves the problem differently. It does not build a workbook model at all:

  • the sheet XML is read node by node through XMLReader — at any moment memory holds only the current row;
  • iteration over rows is implemented with PHP generators — the next row is parsed only when you ask for it;
  • the library only reads data and drags along no infrastructure for writing and editing.

Installation:

bash
composer require avadim/fast-excel-reader

A minimal streaming example:

php
use avadim\FastExcelReader\Excel;

$excel = Excel::open('huge-report.xlsx');
$sheet = $excel->sheet();

foreach ($sheet->nextRow() as $rowNum => $rowData) {
    // $rowData is an array like ['A' => ..., 'B' => ..., ...]
    // only THIS row is in memory right now
    processRow($rowData);
}

nextRow() returns a generator. However many rows the file holds — a thousand or a million — peak memory stays practically constant: it is defined by the size of one row and the shared structures (shared strings, styles), not by the size of the file.

Measuring: how much memory is actually needed

It is easy to check — a simple script with memory_get_peak_usage():

php
use avadim\FastExcelReader\Excel;

$start = microtime(true);

$excel = Excel::open('huge-report.xlsx'); // a file of 1,000,000 rows
$count = 0;

foreach ($excel->sheet()->nextRow() as $rowData) {
    $count++;
}

printf(
    "Rows: %d\nTime: %.1f sec\nPeak memory: %.1f MB\n",
    $count,
    microtime(true) - $start,
    memory_get_peak_usage(true) / 1024 / 1024
);

Our measurement on PHP 8.4: a file of 1,000,000 rows by four columns, 20 MB on disk — reading took 491 seconds, with a peak memory use of 2 MB. Not hundreds, not tens — two. The exact numbers depend on the file (the count of unique strings in shared strings matters most), but the main point is that memory use does not grow with the number of rows. A file of two million rows needs memory of the same order as a file of a hundred thousand.

For comparison, run the same file through “load everything into an array”:

php
$rows = $excel->readRows(); // DO NOT do this with large files

readRows() is convenient for small files, but it collects the whole result into an array — on a million rows you hit the memory wall again, now on the result side rather than the parser side.

Practical techniques for large files

1. Process rows one at a time and do not hoard them

Bad:

php
$all = [];
foreach ($sheet->nextRow() as $rowData) {
    $all[] = transform($rowData); // memory grows with every row
}
save($all);

Good:

php
$batch = [];
foreach ($sheet->nextRow() as $rowData) {
    $batch[] = transform($rowData);
    if (count($batch) >= 1000) {
        save($batch);   // saved a portion
        $batch = [];    // and freed the memory
    }
}
if ($batch) {
    save($batch);
}

Memory is bounded by the batch size, not by the file size.

2. Skip empty rows and junk at the reader level

By default the library already skips completely empty rows. You can tighten the rules — for instance, treat cells holding empty strings and whitespace as empty:

php
foreach ($sheet->nextRow([], Excel::TRIM_STRINGS | Excel::TREAT_EMPTY_STRING_AS_EMPTY_CELL) as $rowData) {
    if (!$rowData) {
        continue;   // a row of nothing but spaces arrives as an empty array
    }
    // from here on — real data only
}

An important detail: the flags act at the level of cells, not rows. Completely empty rows are skipped by the reader even without the flags, whereas a row holding spaces and empty strings arrives as an empty array with these flags on — which is why the loop carries if (!$rowData) continue;. Still cheaper than cleaning values by hand after reading.

3. Read only the area you need

If the data occupies a specific range, there is no point in scanning the whole sheet:

php
$sheet->setReadArea('B4:F100000');
foreach ($sheet->nextRow() as $rowData) {
    // only columns B..F, starting from row 4
}

4. Stop reading when the data ends

readCallback() gives you full control over the process — return true and reading stops:

php
$excel->readCallback(function ($row, $col, $val) {
    // process the cell
    if ($row > 500000) {
        return true; // stop — read no further
    }
    return false;
});

5. Step-by-step reading instead of foreach

If the rows must be read outside a single loop (interleaved with other logic, say), there is the pair reset() + readNextRow():

php
$sheet->reset();
$header = $sheet->readNextRow();  // the first row

while ($rowData = $sheet->readNextRow()) {
    // the following rows — on demand
}

What about CSV and legacy XLS?

The same streaming approach works for every supported format:

php
// CSV — row-by-row reading through a generator
$csv = Excel::openCsv('huge.csv');
foreach ($csv->nextRow() as $rowData) { /* ... */ }

// Legacy XLS (Excel 97-2003) opens with the same Excel::open() —
// the format is detected from the file signature, the API is identical
$excel = Excel::open('legacy.xls');

One API, three formats, and flat memory use everywhere.

When FastExcelReader is not the right fit

In fairness: the library only reads files. If you need to open an XLSX, change a couple of cells, and save — that is a job for FastExcelWriter or PhpSpreadsheet. If you need formulas evaluated — also not here: the reader returns the formula text and the last calculated value, but does not recompute it.

But if your task is “read data out of Excel and do something with it” — and that is 90% of real import work — a streaming reader does it an order of magnitude faster and far more frugally with memory.

Summary

  • An XLSX of hundreds of thousands or millions of rows is no reason to raise memory_limit and build file splitters.
  • The key to flat memory is streaming: XMLReader plus generators instead of an object model.
  • nextRow() is your main tool; readRows() is for small files only.
  • Batches, read areas, and early exit let you squeeze out speed as well.
bash
composer require avadim/fast-excel-reader

Documentation and examples are in the GitHub repository.

#fast-excel-reader #php #excel

Comments (0)