I started to write a utility to upload order header/details, products, customers and general Drupal nodes to Excel/Csv format. It is basic shell, but functional. It can used some more features like security, upload file management and ability for user to specify parameters for extraction. Maybe someone would be interested in working with it.
<?php
/*
* module: export_to_csv.module
* URL: export-to-csv
* custom module to export Drupal file to CSV
*/
/***************************************************************
* hook menu
*/
function export_to_csv_menu(){
$items = array();
$items['export-to-csv'] = array(
'title' => t('Export to CSV'), // Page title
'page callback' => 'fn_export_to_csv', // function to call when this page is called
'access arguments' => array('access content'), // An array of arguments to pass to the access callback function.
'description' => t('Export to CSV'),
'type' => MENU_CALLBACK,
);
return $items;
}
/***************************************************************
*
* implementation of hook block - (Declare a block or set of blocks.)
*/
function export_to_csv_block($op = 'list', $delta = 0, $edit = array())
{
switch($op)
{
case 'list':
$blocks[0] = array(
'info' => t('Export to CSV'),
'region' => 'left',
'weight' => 0,
'visibility' => 1,
'status' => TRUE
);
return $blocks;
break;
// case configure
case 'configure':
// not used
break;
}
}
/***************************************************************
* function
*/
function fn_export_to_csv() {
return drupal_get_form('fn_export_to_csv_my_form');
}
/***************************************************************
* function
*/
function fn_export_to_csv_my_form($form_state){
//echo "-" . $form_state['post']['op'] ."-";
//die();
global $base_root;
$form = array();
//some radio buttons
$form ['query_type'] =array (
'#type' => 'radios',
'#title' => t('Select from available Queries'),
'#options' => array(
"e_commerce_orders" => t("All e-commerce headers"),
"new_orders" => t("New Orders"),
"cancelled_orders" => t("Cancelled Orders")),
'#required' => TRUE,
);
// only show link when submitted. Note: this does not work
//if($form_state['post']['op'] == 'Submit')
{
$form['sec_wrapper'] = array(
'#prefix' => '',
'#value' => l("Click to View CSV Extract File", $base_root . "/myfile.csv"),
'#suffix' => '',
);
}
// add submit button
$form['submit'] = array(
'#type' => 'submit',
'#value' => t('Submit'));
return $form;
}
/***************************************************************
* function - fn_export_to_csv_my_form_submit
* Decide which SQL to run
*/
function fn_export_to_csv_my_form_submit($form, &$form_state) {
drupal_set_message(t('The form has been submitted.'));
$my_selection = $form['#post']['query_type'];
if ($my_selection)
{
switch ($my_selection) {
case 'e_commerce_orders':
$result = fn_run_sql_all_orders();
export_to_excel($result);
break;
case 'new_orders':
$result = fn_run_sql_new();
break;
case 'cancelled_orders':
$result = fn_run_sql_cancelled();
break;
}
}
}
function fn_run_sql_all_orders()
{
//$sql = "SELECT o.order_id, o.uid, o.billing_first_name, o.billing_last_name,
// o.billing_company, o.primary_email, o.order_total, o.order_status, o.created, oh.shipping_method, os.title
// FROM {uc_orders} o
// LEFT JOIN {uc_shipments} oh ON o.order_id = oh.order_id
// LEFT JOIN {uc_order_statuses} os ON o.order_status = os.order_status_id";
$sql = "SELECT * FROM {uc_orders} o
LEFT JOIN {uc_shipments} oh ON o.order_id = oh.order_id
LEFT JOIN {uc_order_statuses} os ON o.order_status = os.order_status_id";
$result = db_query($sql);
if ($result) {
return $result;
}
else {
}
}
function fn_run_sql_new()
{
// some code here
}
function fn_run_sql_cancelled()
{
// some code here
}
/*************************************
* function: export_to_excel
* input receives SQL $result
* output: csv file
*/
function export_to_excel($result, $filename = 'myfile.csv'){
$fh=fopen($filename,"w+");
// iterate through the rows
while($row=db_fetch_array($result))
{
$line = "";
$comma = "";
foreach($row as $value) {
$line .= $comma . '"' . str_replace('"', '""', $value) . '"';
$comma = ",";
}
$line .= "\n";
fputs($fh, $line);
}
fwrite($fh,$string);
fclose($fh);
}