Home ›
Use Drupal to display a table from an array of data and export to CSVUse Drupal to display a table from an array of data and export to CSV
Submitted by Benjamin Melançon on November 11, 2008 - 8:34am
http://api.drupal.org/api/function/theme_table/5
PHP array to CSV solution from the usenet forums:
<?php
$csvstring = "'" . implode("','", $array) . "'";
?>The below (from idealog) will only work if you know your data is escaped or has no commas.
<?php
// create a comma separated list from an array
$array = array('green','purple','blue','yellow','amber','red');
$comma_separated = implode(",", $array);
echo $comma_separated;
?>The next step will be
Covers how to create a zip file, including offering it for download directly from being made on the fly and writing it to disk to provide a link:
http://www.granthinkson.com/2005/07/01/create-zip-files-dynamically-using-php/
<rant>
RRRRRRrrggggghhhhh! It's the {user} table not "users"— Drupal, why can't thou get your plurals straight? {node}, {users}, {role}, {files}, {menu}, {boxes} (here the word doesn't even make sense; it has to do with blocks, which is mostly in {blocks} by the way), {cache}, {sessions}, {variable} – a modicum of consistency would be hugely appreciated! Singular or plural? Pick a standard, and stick with it. It's a good thing it's 4 a.m. and I have two more projects after this one to get done before the morning, or I'd be submitting a patch to Drupal 7 to rename half the tables....
</rant>
And it's while to go through a result set of objects or arrays fetched from the database, not foreach, doh! No more coding after 4 a.m., no more coding after 4 a.m.
Was going to put all the table data in an array and have it as sort of a standalone thing, and so have a header row:
<?php
$table_data = changents_ek_user_export_array();
$header = array_shift($table_data);
?>Instead, the data array will be just the data, because to deal with the massive numbers of users we have, we're going to have to do some kind of batching. Wish this were Drupal 6, which has batch operations!
<?php
$query = "SELECT uid FROM {users} WHERE status > 0";
$result = db_query($query);
print db_num_rows($result);
?>1961
<?php
print db_num_rows(db_query("SELECT uid FROM {node} WHERE type = 'userinfo'"));
?>1888
That's not so much really. Can PHP really not handle getting this data in an inefficient manner?
Based on putting a drupal_set_message in a quick test on a PHP page to see the uids printed out, it can't:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 40961 bytes) in /var/www/vhosts/dev.changents.com/httpdocs/includes/common.inc(1366) : eval()'d code on line 6
Let's try when our code isn't being eval'd, anyway.
It's working on the development site that has almost as many users as the test and live sites! (Heh– chx had just said in IRC this night, "If eval is the answer, it's wrong.")
Note: If writing twice to the file pointer, then the data will be appended to the end of the file content, meaning that the example below wouldn't work as expected:
<?php
$fp = fopen('data.txt', 'w');
fwrite($fp, '1');
fwrite($fp, '23');
fclose($fp); // the content of 'data.txt' is now 123 and not 23!
?>
Which happens to be just the behavior I want!
The on-the-fly download in the code below does work; the weird thing I was trying to do with creating a file stored on the server with cron isn't working at all.
Here's the whole module file, as is. Nothing to be proud of, but I was very surprised how hard it was to find things about creating CSV files for download on the fly direct to the browser and even making a simple table from an array.
Someday this should all be figured out a little better and made into a proper tutorial, but for now, if you can wade through it, there may be useful stuff here.
<?php
// $Id$
/**
* @file
* Export user information in user object and userinfo node.
* Module by Agaric Design Collective, <a href="http://agaricdesign.com/
" title="http://agaricdesign.com/
">http://agaricdesign.com/
</a> */
define('CHANGENTS_EK_USER_EXPORT_CRON_ROWS', 200);
/**
* Implementation of hook_help().
*/
function changents_ek_user_export_help($section) {
switch ($section) {
case 'admin/user/changents_ek_user_export':
return '<p>'. t('Choose to view users with Earthkeeper mail opt-in information as a table or download a comma separated value (CSV) file suitable for manipulation in spreadsheets.') .'</p>';
}
}
/**
* Implementation of hook_menu().
*/
function changents_ek_user_export_menu($may_cache) {
$items = array();
if ($may_cache) {
$items[] = array(
'path' => 'admin/user/changents_ek_user_export',
'title' => t('Users with Earthkeepers info'),
'description' => t('View or export users with Earthkeepers mailing data.'),
'callback' => 'changents_ek_user_export_page',
'access' => user_access('administer users'),
'type' => MENU_NORMAL_ITEM,
);
$items[] = array(
'path' => 'admin/user/changents_ek_user_export/csv',
'title' => t('CSV export'),
'description' => t('Export users with Earthkeepers mailing data as Comma Separated Value file.'),
'callback' => 'changents_ek_user_export_page',
'access' => user_access('administer users'),
'type' => MENU_DEFAULT_LOCAL_TASK,
);
$items[] = array(
'path' => 'admin/user/changents_ek_user_export/table',
'title' => t('View table'),
'description' => t('Export users with Earthkeepers mailing data as Comma Separated Value file.'),
'callback' => 'changents_ek_user_export_table',
'access' => user_access('administer users'),
'type' => MENU_LOCAL_TASK,
);
$items[] = array(
'path' => 'changents_ek_user_export',
'title' => t('Export direct to CSV'),
'callback' => 'changents_ek_user_export_csv',
'access' => user_access('administer users'),
'type' => MENU_CALLBACK,
);
}
return $items;
}
/**
* Data requested:
* User ID User Name Email Address Reg Date First Name Last Name Changents Email EK member EK Email Opt in
*
1) First name
2) Last name
3) Changents user name
4) Email address
5) Registration (Join) date
6) Earthkeeper on Changents member confirmation
7) EK newsletter opt-in confirmation
*
*/
/**
* Let's make it a ridiculously big multidimensional array first.
*/
function changents_ek_user_export_array($limit = NULL) {
$table = array();
$query = "SELECT uid, name, mail, created, data FROM {users} WHERE status > 0";
if ($limit && is_numeric($limit)) {
$start = $limit - CHANGENTS_EK_USER_EXPORT_CRON_ROWS;
if ($start < 0) $start = 0;
$query .= " LIMIT $start, $limit";
}
$result = db_query($query);
while ($account = db_fetch_object($result)) {
$row = array();
// actually, can skip the user_load and get all data in select * above?
// $account = user_load(array('uid' => $uid));
$uid = $account->uid;
$row['uid'] = $uid;
$row['user_name'] = $account->name;
$row['email_address'] = $account->mail;
// apply date formatting inline
$row['reg_date'] = date("Y M d", $account->created);
$query = "SELECT nid FROM {node} WHERE type = 'userinfo' AND uid = %d";
$userinfo_nid = db_result(db_query($query, $uid));
$userinfo = node_load($userinfo_nid);
$row['first_name'] = $userinfo->field_first_name[0]['value'];
$row['last_name'] = $userinfo->field_last_name[0]['value'];
$row['changents_email'] = $userinfo->field_email_updates[0]['value']; // receive/don't receive
$row['ek_member'] = $userinfo->field_is_earthkeeper[0]['value'];
// field_user_earthkeeper_mailings was corrupted
$account_data = unserialize($account->data);
$row['ek_email_opt_in'] = $account_data['user_earthkeeper_mailings'];
$table[] = $row;
unset($row);
}
return $table;
}
function
_changents_ek_user_export_firstrow() {
// make the first row
$row = array();
$row['uid'] = t('User ID');
$row['user_name'] = t('Username');
$row['email_address'] = t('E-mail address');
$row['reg_date'] = t('Date registered');
$row['first_name'] = t('First name');
$row['last_name'] = t('Last name');
$row['changents_email'] = t('Changents newsletter');
$row['ek_member'] = t('Earthkeeper?');
$row['ek_email_opt_in'] = t('EK mail opt-in');
return $row;
}
function
changents_ek_user_export_page() {
$output = '';
$limit = variable_get('changents_ek_user_export_csv_file', NULL);
$total = variable_get('changents_ek_user_export_row_total', 0);
if ($limit && $total && $limit >= $total) {
// we have a perfectly good cached file
$csvpath = file_check_location('changents_ek_user_export');
$fstat = stat($csvpath . '/changents_ek_user_export.csv');
$last_modified = date('Y F j g:i a', $fstat['mdate']);
$output .= '<p>' . t("This file was last modified on @date.", array('@date' => $last_modified)) . '</p>';
drupal_set_message('csvpath: '. $csvpath);
}
else {
// file doesn't exist or is in process of being made
if ($limit && $total) {
$output .= '<p>';
$percent = ($total - $limit) / $total * 100;
$output .= t("This csv file is @percent percent created. More cron runs needed.", array('@percent' => $percent));
}
else {
$output .= '<p>' . t("If clicking the link below times out your browser, click the form below to use cron to generate a CSV file that will be stored on your browser.") . '</p>';
}
$output .= '<p>' . l(t('Download Changent User data with Earthkeepers info as CSV'), 'changents_ek_user_export') . '</p>';
}
$output .= drupal_get_form('changents_ek_user_export_csv_file_form');
return $output;
}
function
changents_ek_user_export_csv_file_form() {
$form = array();
$form['submit'] = array(
'#type' => 'submit',
'#value' => t('Generate CSV file'),
);
return $form;
}
function
changents_ek_user_export_csv_file_form_submit($form_id, $form_values) {
changents_ek_user_export_csv_file_delete();
}
function
changents_ek_user_export_csv_file_delete() {
file_scan_directory(file_create_path('changents_ek_user_export'), '.*', array('.', '..', 'CVS'), 'file_delete', TRUE);
variable_set('changents_ek_user_export_csv_file', CHANGENTS_EK_USER_EXPORT_CRON_ROWS);
variable_set('changents_ek_user_export_row_total', 0);
drupal_set_message("Process for file generation started. Nothing will happen until next cron run.");
}
/**
* Implementation of hook_cron().
*/
function changents_ek_user_export_cron() {
if ($limit = variable_get('changents_ek_user_export_csv_file', NULL)) {
$total = variable_get('changents_ek_user_export_row_total', 0);
if (!$total || $limit < $total) {
changents_ek_user_export_csv_file($limit);
}
}
}
function
changents_ek_user_export_csv_file($limit) {
$filename = 'changents_ek_user_export.csv';
$header = NULL;
// Create the css/ within the files folder.
$csvpath = file_create_path('changents_ek_user_export');
file_check_directory($csvpath, FILE_CREATE_DIRECTORY);
$file = $csvpath .'/'. $filename;
if (!file_exists($file)) {
if ($limit == CHANGENTS_EK_USER_EXPORT_CRON_ROWS) { // that's what we set it to first, provide header
$header = changents_ek_user_export_au_csv_row($header);
$total = db_result(db_query("SELECT COUNT(uid) AS total FROM {users} WHERE status > 0"));
variable_set('changents_ek_user_export_row_total', $total);
}
// Build CSV file. "a" means we are opening to append, creating if needed.
if (!$fp = fopen($file, 'ab')) {
drupal_set_message(t('The file could not be created.'), 'error');
return FALSE;
}
if ($header) {
$header = changents_ek_user_export_au_csv_row($header);
fwrite($fp, $header);
}
$array_chunk = changents_ek_user_export_array($limit);
// would below be better to write to a string and then fwrite?
foreach ($array_chunk as $row) {
fwrite($fp, $row);
}
fclose($fp);
}
variable_set('changents_ek_user_export_csv_file', $limit + CHANGENTS_EK_USER_EXPORT_CRON_ROWS);
return TRUE;
}
function
changents_ek_user_export_csv() {
$csv = '';
$header = _changents_ek_user_export_firstrow();
$csv .= changents_ek_user_export_au_csv_row($header);
// massively inefficent, get the data
$data = changents_ek_user_export_array();
foreach ($data as $row) {
$csv .= changents_ek_user_export_au_csv_row($row);
}
drupal_set_header("Content-type: application/octet-stream; charset=utf-8");
drupal_set_header("Content-disposition: attachment; filename=changents_ek_user_export.csv");
print $csv;
}
/**
* Take an array of values and output a comma separated value string.
*/
function changents_ek_user_export_au_csv_row($array) {
return "'" . implode("','", $array) . "'\n";
}
function
changents_ek_user_export_table() {
$header = _changents_ek_user_export_firstrow();
// massively inefficent, get the data
$table_data = changents_ek_user_export_array();
return theme('table', $header, $table_data);
}
?>Reference
http://api.drupal.org/api/group/menu/5
http://api.drupal.org/api/function/theme_table/5
http://api.drupal.org/api/function/l/5
http://api.drupal.org/api/function/drupal_set_header/5
And as an example of how to output a 'file' - text string without any theme wrappings or anything else in Drupal, http://api.drupal.org/api/function/aggregator_page_rss/5
On writing files: http://api.drupal.org/api/function/drupal_build_css_cache/5 and http://api.drupal.org/api/function/drupal_clear_css_cache/5
Also http://api.drupal.org/api/function/file_save_data/5 which we can't use directly because it doesn't have an append option.
Resolution
Searched words:
drupal make table from array
drupal theme table
drupal sort table with jquery (no answers here)
drupal export array to csv
create file for download
download link
batch api backport to Drupal 5 (i wish!)
php write to file to download
php Excel
CSV comma separated value
drupal write to file
drupal write text document to download
dynamically create download file text php
create dynamic text file php
Drupal output text for download
Drupal output csv for download
drupal create download file
drupal return file to browser
drupal make file on the fly
drupal create files for download on the fly
drupal write to file
get last written date for a file PHP
drupal file last modified statistics with fstat or stat mtime
Comments
Post new comment