Write large amount of data to CSV file using PHP

Question

I need to write almost 109,392 rows to CSV file and currently I am doing it like this

    //open raw memory as file, no need for temp files
    $tempMemory = fopen('php://temp', 'w');

    //default php csv handler
    foreach ($inputArray as $line) {
        fputcsv($tempMemory, $line, $delimiter);
    }

    //rewrind the "file" with the csv lines
    fseek($tempMemory, 0);

    //modify header to be downloadable csv file
    header('Content-Type: application/csv');
    header('Content-Disposition: attachement; filename="' . $outputFileName . '";');

    //send file to browser for download
    fpassthru($tempMemory);

The problem here is that it is very slow and I am not getting all the rows inserted. The array size of $inputArray is 109,392 but only 50K rows are there in the CSV file produced. What is the best way of doing this ?

The rows might be inserted to csv, but the number of rows to display in csv is limited to particular number I think. Can you read the exported csv and print the last row and crosscheck — Sanjay Kumar N S
– Sanjay Kumar N S, Commented Dec 4, 2015 at 7:41
Also it might make sense to explicitly flush(), not sure if ??rewind()` takes care of that. — arkascha
– arkascha, Commented Dec 4, 2015 at 7:49
@SanjayKumarNS What do you mean by "the number of rows to display in csv"? That is a text file, there is no logic that could limit here. Maybe some application you use to read such a file is limited, sure, for example spreadsheets are all very limited in that. But the file format itself is not limited. So using standard file tools or a sane text editor should certainly allow to access all content. — arkascha
– arkascha, Commented Dec 4, 2015 at 7:50
Yes. If opened in text mode, all contents should appear. I thought you opened in any csv mode — Sanjay Kumar N S
– Sanjay Kumar N S, Commented Dec 4, 2015 at 10:24

Danny_ds · Accepted Answer · 2016-02-08 20:59:00Z

As it is now, you are generating (or trying to) the whole file in memory first, and only then start sending the data to the browser.

Since the resulting file (in memory?) could be large, you should at least check the return value of fputcsv(). Maybe it starts being FALSE which could be the reason you don't get all the rows.

Anyway, this is not the right way to do this (just imagine 10 users sending the same request to your server at the same time).

I don't know about php, but the ideal way to generate (on demand) and send such a file would be:

first send the http headers, including a header for chunked transfer mode. That way you don't need to know the size of the file up front.
while there is data in $inputArray, fill a buffer with rows (say max. 64kB) and send that buffer to the browser (also in chunked mode). You could eventually compress the data first.

That way the browser will start receiving the data immediately, and you won't need much more than 64Kb of memory on the server to generate the data.

Armen · Accepted Answer · 2015-12-04 07:56:08Z

1

My suggest to not write directly to php memory but instead write to file located in some public directory in your root of project then after filling all rows to it redirect request to that url domain.com/download_files/generated.csv then issue with missing line should be solved

answered Dec 4, 2015 at 7:56

Armen

4,2123 gold badges27 silver badges43 bronze badges

Comments

H.Dave · Accepted Answer · 2015-12-04 08:18:07Z

1

Try to get a CSV file directly from mysql and store it in your system and then allow user to download it for e.g

SELECT order_id,product_name,qty FROM ordersINTO OUTFILE "/tmp/orders.csv"FIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n';

answered Dec 4, 2015 at 8:18

H.Dave

211 bronze badge

1 Comment

Happy Coder Over a year ago

It is not possible as I need to fetch certain fields from one table and then pass it through some other functions for calculations.

Danack · Accepted Answer · 2015-12-04 08:29:46Z

1

You can write directly to STDOUT, rather than writing to a 'temp' memory file.

//modify header to be downloadable csv file
header('Content-Type: application/csv');
header('Content-Disposition: attachement; filename="' . $outputFileName . '";');

foreach ($inputArray as $line) {
    fputcsv(STDOUT, $line, $delimiter);
}

This should be a lot quicker.....however 109,392 rows is still a lot of rows. Generating this much data would be much better done as a background task that generates the whole file, and then serve the generated file from disk.

answered Dec 4, 2015 at 8:29

Danack

25.8k16 gold badges93 silver badges123 bronze badges

Collectives™ on Stack Overflow

Write large amount of data to CSV file using PHP

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related