Skip to content
Search

Last updated

Read a large CSV file in PHP without running out of memory

Let's say we got an export of orders: one CSV file, a million rows, 62Mb. We need the revenue per country, and our PHP process runs with the default memory_limit of 128Mb.

Doesn't look like a complicated task, right?

The file

First we need a file to work with, so let's generate one. Set $rows to 1_000_000 to get the 62Mb version.

<?php

declare(strict_types=1);

$rows = 1_000;
$file = fopen(__DIR__ . '/orders.csv', 'w');

fputcsv($file, ['id', 'email', 'country', 'amount', 'created_at'], escape: '');

for ($id = 1; $id <= $rows; $id++) {
    fputcsv($file, [
        $id,
        "customer{$id}@example.com",
        ['PL', 'US', 'DE', 'FR', 'GB'][$id % 5],
        number_format(($id % 1000) / 10, 2, '.', ''),
        gmdate('Y-m-d H:i:s', 1_700_000_000 + $id * 60),
    ], escape: '');
}

fclose($file);

echo "orders.csv: {$rows} rows\n";
orders.csv: 1000 rows

Why file() ends with "Allowed memory size exhausted"

The first thing that comes to mind is file(). It reads the whole file into an array of lines, and then we loop over it.

<?php

declare(strict_types=1);

$revenue = [];

foreach (file(__DIR__ . '/orders.csv') as $number => $line) {
    if ($number === 0) {
        continue;
    }

    $row = str_getcsv($line, escape: '');
    $revenue[$row[2]] = ($revenue[$row[2]] ?? 0.0) + (float) $row[3];
}

arsort($revenue);

foreach ($revenue as $country => $total) {
    printf("%s %.2f\n", $country, $total);
}
GB 10030.00
FR 10010.00
DE 9990.00
US 9970.00
PL 9950.00

With a thousand rows it works. With a million rows it ends like this:

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

file() keeps every line in memory at the same time. For our file that is 172Mb, and we have 128Mb. We could raise memory_limit, but that only moves the problem to the next, bigger file.

Read the CSV file line by line with fgetcsv

fgetcsv reads one row at a time, so it doesn't matter how big the file is.

<?php

declare(strict_types=1);

$file = fopen(__DIR__ . '/orders.csv', 'r');
$header = fgetcsv($file, escape: '');
$revenue = [];

while (($row = fgetcsv($file, escape: '')) !== false) {
    $order = array_combine($header, $row);
    $revenue[$order['country']] = ($revenue[$order['country']] ?? 0.0) + (float) $order['amount'];
}

fclose($file);
arsort($revenue);

foreach ($revenue as $country => $total) {
    printf("%s %.2f\n", $country, $total);
}
GB 10030.00
FR 10010.00
DE 9990.00
US 9970.00
PL 9950.00

That's 2Mb of memory for a million rows. Reading a big file was never the hard part.

Now sort it

OK, but the next request comes the same day: the same file, sorted by amount, the biggest orders first.

We can't sort a file one row at a time. To sort the rows we need all of them, so we are back to keeping the whole file in memory.

<?php

declare(strict_types=1);

$file = fopen(__DIR__ . '/orders.csv', 'r');
$header = fgetcsv($file, escape: '');
$orders = [];

while (($row = fgetcsv($file, escape: '')) !== false) {
    $orders[] = array_combine($header, $row);
}

fclose($file);

usort($orders, static fn (array $left, array $right): int => (float) $right['amount'] <=> (float) $left['amount']);

$sorted = fopen(__DIR__ . '/orders-sorted.csv', 'w');
fputcsv($sorted, $header, escape: '');

foreach ($orders as $order) {
    fputcsv($sorted, $order, escape: '');
}

fclose($sorted);

foreach (array_slice($orders, 0, 3) as $order) {
    printf("%s %s %s\n", $order['id'], $order['email'], $order['amount']);
}
999 customer999@example.com 99.90
998 customer998@example.com 99.80
997 customer997@example.com 99.70

With a thousand rows it works. With a million rows we are back where we started:

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

This time fgetcsv doesn't help. The script needs 624Mb for a 62Mb file, ten times its size, and it grows with every row. To do it right we would have to cut the file into sorted chunks on disk and merge them back. That's a lot of code for "sort a file".

The same thing with Flow

Flow needs PHP 8.3 or newer.

composer require flow-php/etl flow-php/etl-adapter-csv
<?php

declare(strict_types=1);

use function Flow\ETL\Adapter\CSV\{from_csv, to_csv};
use function Flow\ETL\DSL\{data_frame, overwrite, ref, to_output};

require __DIR__ . '/vendor/autoload.php';

data_frame()
    ->read(from_csv(__DIR__ . '/orders.csv'))
    ->sortBy(ref('amount')->desc())
    ->write(to_csv(__DIR__ . '/orders-sorted.csv')->saveMode(overwrite()))
    ->run();

data_frame()
    ->read(from_csv(__DIR__ . '/orders-sorted.csv'))
    ->select('id', 'email', 'amount')
    ->limit(3)
    ->write(to_output(truncate: false))
    ->run();
+-----+-------------------------+-----------+
|  id |                   email |    amount |
+-----+-------------------------+-----------+
| 999 | customer999@example.com | 99.900000 |
| 998 | customer998@example.com | 99.800000 |
| 997 | customer997@example.com | 99.700000 |
+-----+-------------------------+-----------+
3 rows

from_csv reads the file row by row, detects the separator and the type of every column, so amount is already a float. sortBy keeps as many rows in memory as the limit allows and moves the rest to disk, so the script never needs more than it has. The second pipeline is there only to show us the first three rows of the result.

The revenue report from the beginning is the same shape:

<?php

declare(strict_types=1);

use function Flow\ETL\Adapter\CSV\from_csv;
use function Flow\ETL\DSL\{data_frame, ref, sum, to_output};

require __DIR__ . '/vendor/autoload.php';

data_frame()
    ->read(from_csv(__DIR__ . '/orders.csv'))
    ->groupBy([ref('country')])
    ->aggregate(sum(ref('amount')))
    ->sortBy(ref('amount_sum')->desc())
    ->write(to_output(truncate: false))
    ->run();
+---------+--------------+
| country |   amount_sum |
+---------+--------------+
|      GB | 10030.000000 |
|      FR | 10010.000000 |
|      DE |  9990.000000 |
|      US |  9970.000000 |
|      PL |  9950.000000 |
+---------+--------------+
5 rows

How much memory does a large CSV file take in PHP

PHP 8.3, memory_limit=128M, a million rows, 62Mb:

Task Code Peak memory Time
Revenue per country file() fatal error, needs 172Mb -
Revenue per country fgetcsv loop 2Mb 1.6s
Revenue per country Flow 44Mb 4.9s
Revenue per country Flow with the flow_php extension 16Mb 0.6s
Sort by amount fgetcsv and usort fatal error, needs 624Mb -
Sort by amount Flow 98Mb 7.4s
Sort by amount Flow with the flow_php extension 18Mb 1.0s

The numbers that matter are the ones that don't change. With three million rows and a 189Mb file the plain sort needs 1942Mb, and Flow still takes 97Mb, or 20Mb with the extension.

flow_php is an optional PHP extension. The scripts stay the same, without a single change.

To check it on your machine, add this line at the end of any of the scripts above:

printf("peak memory: %.1f MB\n", memory_get_peak_usage(true) / 1024 / 1024);

Found a typo or an outdated section? Edit this guide on GitHub

scroll back to top