Php Export large amount of data to CSV or EXCEL from custom tables in drupal 8

Question

We have 2 millions encrypted records in a table to export. We are using Drupal 8 but we cannot export data it through custom views or using webform export due to encryption of sensitive data. So we have to write a custom function to export data in CSV or Excel. But it throw "Allowed Memory Exhausted" error due large amount of data whenever we tried to export it.

It seems the best option is loading data in smaller chunks and appending to the same sheet. How can we achieve this approach? Or any idea to do it in PHP or Drupal 8.

Answer 1

Exporting to CSV is by far the simpler operation. There are a couple of ways to do this.

1. You could always use mysqldump with text delimiters to avoid PHP memory constraints:

mysqldump -u YOUR_USERNAME -p -t DATABASE_NAME TABLE_NAME 
    --fields-terminated-by="," 
    --fields-optionally-enclosed-by="\"" 
    --fields-escaped-by="\""
    --lines-terminated-by="\r\n" 
    --tab /PATH/TO/TARGET_DIR

Line breaks added for readability. By default, mysqldump also generates a .sql file with DROP/CREATE TABLE statements. The -t option skips that.

2. You can make a MySQL query and define INTO OUTFILE with the appropriate delimiters to format your data as CSV and save it into a file:

SELECT * FROM `db_name`.`table_name` 
    INTO OUTFILE 'path_to_folder/table_dump.csv' 
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"' 
    ESCAPED BY '"'
    LINES TERMINATED BY '\r\n';"

If you run this on the command line, you can probably get away with a single call without the need to batch it (subject to your server specs and MySQL memory config).

If you do need to batch, then add something like LIMIT 0, 100000 where 100000 is whatever is a good result set size, and adapt your filename to match: table_dump_100000.csv etc. Merging the resulting CSV dumps into one file should be a simple operation.

3. If you do want to run this over PHP, then you most likely have to batch it. Basic steps:

• A loop with for($i = 0; $i <= $max_rows; $i += $incr) where $incr is the batch size. In the loop:

• Make MySQL query with variables used in the LIMIT clause; as in LIMIT $i, $incr.

• Write the rows with fputcsv into your target file. Define your handle before the loop.

The above is more of a homework assignment than an attempt to provide ready code. Get started and ask again (with code shown). Whatever you do, make sure the data variables used for each batch iteration are reused or cleared to prevent massive memory usage buildup.

You can up your script's memory limit with ini_set('memory_limit', '2048M'); (or whatever your server can handle). If you run into max execution time, set_time_limit(600) (10 min; or whatever seems enough) at the start of your script.

Answer 2

Never tried with 2 million records. But it works with a few hundred thousand, using drush and a script similar to:

<?php

// php -d memory_limit=-1 vendor/bin/drush php:script export_nodes.php

// options
$type = 'the_type';
$csv_file_name = '/path/to/csv_file.csv';
$delimiter = '"';
$separator = ',';

// fields
$fields = [
        'nid',
        'title',
        'field_one',
        'field_two',
        'field_three',
    ];

// header
$header = '';

foreach ($fields as $field) {

    $header = $header . $delimiter . $field . $delimiter . $separator;

}

$header = $header . PHP_EOL;

file_put_contents ($csv_file_name, $header, FILE_APPEND);
unset ($header);

// get nodes
$nodes = \Drupal::entityTypeManager()
            ->getStorage('node')
            ->loadByProperties([
                    'type' => $type,
                ]);

// loop nodes
foreach ($nodes as $node) {

    $line = '';

    // loop fields
    foreach ($fields as $field) {

        $field_value_array = $node->get($field)->getValue();

        if (empty ($field_value_array[0]['value'])) {

            $field_value = '';

        }
        else {

            $field_value = $field_value_array[0]['value'];

        }

        $line = $line . $delimiter . $field_value . $delimiter . $separator;

    }

    unset ($field_value_array);
    unset ($field_value);

    // break line
    $line = $line . PHP_EOL;

    // write line 
    file_put_contents ($csv_file_name, $line, FILE_APPEND);
    unset ($line);

}

unset ($nodes);