3

A site we have over 8000+ users with Views, Ctools and VBO installed. Is it possible to export all users data into 1 CSV file? We tired Views Data Export with no luck(memory limit), even we increase the memory to 128MB. Please help.

  • As I mentioned on my question. The Views Data Export does not help on my situation. – CocoSkin Jun 6 '14 at 14:21
  • 3
    @H-L "You have the option to turn on batch export, for large datasets" Source: drupal.org/node/1820452 – Alexar Jun 6 '14 at 14:22
  • Thank You Alexar! the Batch export does solve the problem. – CocoSkin Jun 6 '14 at 14:31
4

Stick the following in a file named expusers.module

<?php
/**
 *  Implementation of hook_menu()
 */
function expusers_menu() {
  $items['expusers'] = array(
    'page callback' => '_expusers_select',
    'access arguments' => array('administer expusers'),
  );
  return $items;
}

/**
 * Callback
 */
function _expusers_select() {
  // 
  $csvfile_name = 'expusers.csv'; // the file to save to
  $users = array();
  $result = db_query('SELECT * FROM {users}');
  foreach ($result as $record) {
    unset($userarray);
    foreach ($record as $key => $value) {
      $userarray[] = (empty($value)) ? '' : '"' . $value . '"';
    }
    $user = implode(',',$userarray);
    $users[] = $user . PHP_EOL; // Remove this if file_save_data don't work.
    // Use PHP statement below to append to file here if file_save_data don't work.
    // file_put_contents(..path to file.., $user, FILE_APPEND); 
  }
  $file = file_save_data($users,'public://' .$csvfile_name); // Remove this if file_save_data don't work.
  return '<p>CSV dump of {users} table written to file "' . $file->uri . '".</p>';
}

Stick the following in a file named expusers.info;

name = Export users
description = "Exports the {users} table to a CSV file in the public file area."
core = 7.x

Create a directory named expusers in your in your modules directory, and place both files in it.

Enable the custom module "Export users" on the site's module page.

Navigate to the path expusers (e.g. http://example.com/expusers).

The message:

CSV dump of {users} table written to file "public://expusers.csv".

Should appear.

Go to your public file area and pick up your file.

The above tiny custom module should be far less resource hungry than Views data export, so, hopefully, you will get your CSV without hitting a memory limit .

However, it first collects all the user data into an array in memory, before dumping into the file.

If you still hit a memory limit, you can't use Drupal's very convenient file_save_data to save to a file. Instead, you must save the data one line at a time. There is no built-in Drupal function to do this, but you should be able to use PHPs file_put_contents instead.

I've used comment to indicate how to change the code to replace Drupal's file_save_data with PHP's file_put_contents.

  • Ty @Free Radical. Its a small and great module. I have to use this if the site getting bigger. Many Thanks. – CocoSkin Jun 6 '14 at 15:24
0

Another option to consider for addressing these requirements is the Forena module.

You would use it's report writing features (such as its WYSIWYG report writer and/or it's query builder) to first create a report that fits your needs. The result of it could be a tabular output and/or graph. After the report content matches the specifications, you could save it as (e.g.) a CSV file.

Here is a quote from one of the forena community docu pages, i.e. Reasons for considering Forena:

Reports created by Forena (combined with SVGGraph and mPDF) can be saved (exported) as PDF documents with embedded graphs. Other supported formats for saving a report are CSV, XLS, DOC, HTML, etc.

The Forena Video demos and tutorials contain quite some links to live demos.

0

Here is simple function (D7):

/**
  * Export all active users into CSV.
  */
function foo_export_users_csv($filename = 'users-' . REQUEST_TIME) {
  drupal_add_http_header('Content-Type', 'text/csv');
  drupal_add_http_header('Content-Disposition', 'attachment; filename=' . $filename . '.csv');
  $results = db_query("SELECT * FROM {users} WHERE status = 1", array());
  $csvdata = 'Uid,Username,Email,Created,Access,Login' . PHP_EOL;
  foreach ($results as $record) {
    $row = array();
    $row[] = $record->uid; 
    $row[] = $record->name; 
    $row[] = $record->mail; 
    $row[] = $record->created; 
    $row[] = $record->access; 
    $row[] = $record->login; 
    $csvdata .= implode(',', $row) . PHP_EOL;
  }
  print $csvdata;
  drupal_exit();
} 

Then call this function from hook_menu, some submit function or somewhere else, like:

/**
 * Some callback function.
 */
function foo_export_users_submit(&$form, &$form_state) {
  ini_set('max_execution_time', 0); // Set unlimited time for large sizes.
  $values = $form_state['values'];
  $op = $values['operation'];
  switch ($op) { 
    case 'export':
      foo_export_users_csv();
      break;
  } 
}

Not the answer you're looking for? Browse other questions tagged or ask your own question.