What is the best approach to export large CSV data using PHP/MySQL?

Question

I'm working on a project that I need to pull out data from database which contains almost 10k rows then export it to CSV. I tried the normal method to download CSV but I'm always getting memory limit issue even if we already sets the memory_limit to 256MB.

If any of you have experienced the same problem, please share your ideas on what is the best solutions or approach.

Really appreciate your thoughts guys.

Here is my actual code:

$filename = date('Ymd_His').'-export.csv';

//output the headers for the CSV file
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$filename}");
header("Expires: 0");
header("Pragma: public");

//open the file stream
$fh = @fopen( 'php://output', 'w' );

$headerDisplayed = false;

foreach ( $formatted_arr_data_from_query as $data ) {
    // Add a header row if it hasn't been added yet -- using custom field keys from first array
    if ( !$headerDisplayed ) {
        fputcsv($fh, array_keys($ccsve_generate_value_arr));
        $headerDisplayed = true;
    }

    // Put the data from the new multi-dimensional array into the stream
    fputcsv($fh, $data);
}

// Close the file stream
fclose($fh);

Tizzy

do you use a unbufferd query? Do you write your rows with fputcsv()? – Celebration

Read each data row individually from the query resultset and write directly to php://output, then read the next row, etc; rather than building any large array or building the csv in memory – Glooming

1. Are you using echo to send data to the browser? 2. Are you asking the browser to download the file through an HTTP header 3. How are you fetching the data from your DB? – Reginareginald

Have you considered using the mysqldump system command. This had solved all of my issues in the past. – Deathlike

system("mysqldump -h $dbhost -u $dbuser -p $dbpass $dbname > $backupfile"); – Deathlike

dev.mysql.com/doc/refman/5.1/en/mysqldump.html – Deathlike

@rosscowar mysqdump is not applicable on my part since I need to format the data before adding to CSV. – Tizzy

Answer 1

If you really must do processing in PHP you'll need to use MYSQL's limit command to grab a subset of your data. Grab only a certain number of rows per time, write those out to the file and then grab the next set.

You may need to run unset() on a few of the variables inside your querying loop. The key is to not have too many huge arrarys in memory at once.

If you're grabbing entire merged tables, sort them by insert date ascending such that the second grab will get any newer items.

Northing

Answer 2

SHORT DESCRIPTION: Export packs of several hundreds of lines to CSV reusing variables, so the memory pressure will remain low. You cannot throw an entire mysql table in an array (and then to CSV file), that is the main problem

LONG DESCRIPTION: Try this to export a large table with column names (I used it, worked well, it can also be improved and compressed and optimised but .. later):

1. Open the CSV file (headers, fopen, etc)
2. Define an array with the column names and: fputcsv($f, $line, $delimiter);
3. Get a list of ids that you want (not entire rows, only ids): SELECT id FROM table WHERE condition ORDER BY your_desired_field ASC -> here you have $ids
4. $perpage = 200; // how many lines you export to csv in a pack;

5. for ($z=0; $z < count($ids); $z += $perpage)
   {
       $q = "SELECT * FROM table WHERE same_condition ORDER BY your_desired_field ASC LIMIT " . $perpage . " OFFSET " . $z 
       // important: use the same query as for retrieving ids, only add limit/offset. Advice: use ORDER BY, don't ignore it, even if you do not really need it;
       $x = [execute query q]
       for ($k=0; $k < count($x); $k++)
       {
           $line = array($x[$k]->id, $x[$k]->field1, $x[$k]->field2 ..);
           fputcsv($f, $line, $delimiter);
       }
   } // end for $z
   

6. close the CSV

So, you will loop through entire results table, get 200 rows and write them to CSV which will wait open until you write all the rows. All the memory you need is for the 200 rows because you will re-write the variable. I am sure it can be done in a better way but took several hours for me and didn't find a solution; also, it is slightly influenced by my architecture and demands of the app, this is why I chose this solution.

Petigny

Answer 3

As explained in this comment: https://mcmap.net/q/209527/-dump-all-tables-in-csv-format-using-39-mysqldump-39 using mysqldump is probably the best option. If needed you could even execute this via php with the exec() command as explained here: php exec() - mysqldump creates an empty file

Northing

mysqldump is not applicable on my issue since my mysql queries involves table relationship plus I need to format the data before printing it to CSV. – Tizzy

Answer 4

• Read each data row individually from the query result set
• write directly to php://output
• then read the next row, etc;

rather than building any large array or building the csv in memory

Ecclesia

Please format your answer properly. Add code example if possible. – Berkley

uh dats a comment – Jolinejoliotcurie