Yii 1.1: csvexport

Create CSV string or write CSV to file from record set
42 followers

New Rewritten version! Class renamed as well. Download had incorrect folder name and file name, though the class itself is named correctly. Don't code late at night.

  • fixed csql bug (Timbo)
  • updated when exportFull is true and CActiveDataProvider has pagination set. It now properly turns it off.
  • update headers bug/var ref error (lunatic77)
  • fixed row exclude bug
  • updated to fix runtime enclosure/delimiter being set in toCSV()

CSVExport will allow you to create a csv file with optional row callbacks. By default it will return a string of comma delimited items, but can also write a file if you need one.

By passing an Array, CDbCommand, CSqlDataProvider, or CActiveDataProvider instance this extension will create a csv string or file, taking into account also any paging settings.

For extremely large csv files, you should just use mysql's select into file instead, as it will be really fast (you just need correct permissions).

Requirements

Yii 1.1 PHP 5.2+

Usage

Delimiter and Enclosure defaults to php defaults of , and ". You can change this with set functions, or at the call toCSV(). Many set* functions provide a fluent interface as well.

Yii::import('ext.ECSVExport');
 
// for use with array of arrays
$data = array(
    array('key1'=>'value1', 'key2'=>'value2')
    ....
)
 
$csv = new ECSVExport($data);
$output = $csv->toCSV(); // returns string by default
 
echo $output;
 
// gives you something like
key1,key2
value1,value2
 
// OR
$csv = new ECSVExport($provider);
$content = $csv->toCSV();                   
Yii::app()->getRequest()->sendFile($filename, $content, "text/csv", false);
exit();

Writing To File

Yii::import('ext.ECSVExport');
 
// for use with array of arrays
$data = array(
    array('key1'=>'value1', 'key2'=>'value2')
    ....
)
 
$filename = 'somewriteablefile.csv';
$csv = new ECSVExport($data);
$csv->toCSV($filename); // returns string by default
 
echo file_get_contents($filename);
 
// gives you something like
key1,key2
value1,value2

Human Readable headers

$headers = array('header_to_change'=>'new value');
$csv->setHeaders($headers);
 
// or
 
$csv->setHeader($currentHeaderValue, $newHeaderValue);

Per Row Callback

// callback must be is_callable by php or exception is thrown
 
$csv->setCallback(function($row){
    $new = array();
    foreach($row as $k=>$v) {
        $new[$k] = $v * $v;
    }
    return $new;
});

Exclude Columns

$exclude = array('getlost','dontshow'...);
$csv->setExclude($exclude);
 
// or
 
$csv->setExclude($nameofcolumn);

Various Provider types

// CDbCommand
$cmd = Yii::app()->db->createCommand("SELECT * FROM track_test LIMIT 10");
$csv = new ECSVExport($cmd);        
$csv->setOutputFile($outputFile);
$csv->toCSV();
 
// CSqlDataProvider
// Defaults to looping through all pages, use $csv->exportCurrentPageOnly(); to turn that off
$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM track_test WHERE campaign_id=1')->queryScalar();
$sql='SELECT * FROM track_test WHERE campaign_id=1';
$dataProvider=new CSqlDataProvider($sql, array(
    'totalItemCount'=>$count,
    'pagination'=>array(
        'pageSize'=>10,
    ),
));
 
$csv = new ECSVExport($dataProvider);
$csv->exportCurrentPageOnly(); // if not set will loop through all pages!
$csv->setOutputFile($this->outputFile);
$csv->toCSV();
 
// CActiveDataProvider
$dataProvider = new CActiveDataProvider('Track', array(
    'criteria'=>array(
        'condition'=>'campaign_id=1 and type=1'
    )
));
$csv = new ECSVExport($dataProvider);
$csv->setOutputFile($this->outputFile);
$csv->toCSV();
 
// Active Records
$csv = new ECSVExport(Track::model()->findAllByAttributes(array('campaign_id'=>1,'type'=>Track::TYPE_INT_SUCCESS)));
$csv->setOutputFile($this->outputFile);
$csv->toCSV();

When working with CActiveDataProviders, you can often run out of memory when working with large resultsets. $convertActiveDataProvider defaults to true, which creates a command out of provider. While this seems to fix memory problems, you are no longer able to use the with() function and will will to resort to joins. Sorry! Use dontConvertProvider() to turn that off.

Total 20 comments

#18528 report it
Sandeep Chauhan at 2014/11/10 08:51am
Extra Row is adding above header using data array

Hi, I need a help for extra row is being added on above of header. I am using an array to pass in plug in and writing header and data. But extra blank row is adding before header. Can you please help me?

#17944 report it
ilbassa at 2014/08/14 08:42am
Resolved problem on null values in relations

Hi, I have a problem exporting with null values in correct column (check comment #c15025).

I have resolved with

$csv->convertActiveDataProvider=false;

but with attributes of related tables the problem still remains.

So I have modified the source of the extension at line 447, adding a comment before the two if

if(isset($model->$relation->$subvalue) && $model->$relation->$subvalue)

because I think this would skip null values of attributes and will cause shifting of datas.

The snippet of code is:

foreach($this->_modelRelations as $relation=>$value) {
                            if(is_array($value)) {
                                foreach($value as $subvalue) {
                                    //if(isset($model->$relation->$subvalue) && $model->$relation->$subvalue)
                                    $row[$relation.'['.$subvalue.']'] = $model->$relation->$subvalue;
                                }
                            } else {
                                //if(isset($model->$relation->$value) && $model->$relation->$value)
                                    $row[$relation.'['.$value.']'] = $model->$relation->$value;
                            }
                        }
#16943 report it
Amjad Khan at 2014/04/15 07:02am
want to add new lines above headers in excel sheet

If i want to add two or three rows above the headers with some data how can i do that?

#16302 report it
nsbucky at 2014/02/07 04:45pm
support

hey I'm not in a position to support this extension anymore, if someone wants to take it over that would be great. :)

#16301 report it
Say_Ten at 2014/02/07 03:23pm
Make excluded fields available to the callback

Use case: Logic in the callback is dependent upon the value of a field that I don't want in the exported CSV. So I want to exclude the field but still have it for processing in the call back.

I amended my local code thus:

# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: D:\www\vauxhall.partners\protected\extensions\ecsvExport
# This patch can be applied using context Tools: Patch action on respective folder.
# It uses platform neutral UTF-8 encoding and \n newlines.
# Above lines and this line are ignored by the patching process.
Index: ECSVExport.php
--- ECSVExport.php Base (BASE)
+++ ECSVExport.php Locally Modified (Based On LOCAL)
@@ -579,6 +579,17 @@
         if($row instanceof CActiveRecord) {
             $row = $row->getAttributes();
         }
+        
+        if($this->stripNewLines) {            
+            array_walk($row, array('ECSVExport','lambdaFail'));
+        }
+        
+        array_walk($row, array('ECSVExport','stripSlashes'));
+               
+        if(isset($this->_callback) && $this->_callback) {
+            $row = call_user_func($this->_callback, $row);
+        }
+        
         // remove excluded
         if(count($this->_exclude) > 0) {
             foreach($this->_exclude as $e) { 
@@ -588,17 +599,7 @@
             }
         }
 
-        if($this->stripNewLines) {            
-            array_walk($row, array('ECSVExport','lambdaFail'));
-        }
-        
-        array_walk($row, array('ECSVExport','stripSlashes'));
-               
-        if(isset($this->_callback) && $this->_callback) {
-            fputcsv($this->_filePointer, call_user_func($this->_callback, $row), $this->_delimiter, $this->_enclosure);                       
-        } else {
             fputcsv($this->_filePointer, $row, $this->_delimiter, $this->_enclosure);
-        }
         unset($row);
     }
#15025 report it
ilbassa at 2013/09/30 04:35am
Problem: Row without some data

I have a probleb when exporting data from a tabel. If there are rows with null values, the final .csv file doesn't have the double comma ",," that permit to put further data in the right columns.


For example

COLUMN1 - COLUMN2 - COLUMN3 - COLUMN4
data1    - data2   - data3   - data4
data1    - data2   - NULL   - data4

I have

data1,data2,data3,data4
data1,data2,data4

I wanted

data1,data2,,data4

so excel can put data in the right columns

code

Yii::import('ext.ECSVExport');
$dati = Yii::app()->db->createCommand("select ...");
$csv = new ECSVExport($dati);
$output = $csv->toCSV();
$filename="file.csv";
Yii::app()->getRequest()->sendFile($filename, $output, "text/csv", false);

RESOLVED

With

$csv->convertActiveDataProvider=false;

Converting activedata provider to a cdbcommand causes the error and some problem with a filter I have implemented. Is slower but now it works.

#14339 report it
Narender Negi at 2013/08/05 07:00am
Hi,

Thanx for this extension

#13843 report it
nsbucky at 2013/07/01 09:43am
thx timbo

added fix in new download.

#13842 report it
Timbo at 2013/07/01 08:37am
Bug: First page repeated

I have found a problem using a CSQLProvider with pagination: the first page gets repeated for the total number of pages.

To fix:

if($this->_dataProvider instanceof CSqlDataProvider) {            
    if($this->exportFull) {
        (...)
        for($i=1; $i<=$totalPages; $i++) {                    
            (...)
            // Change...
            $_getData = $this->_dataProvider->getData();
            // to 
            $_getData = $this->_dataProvider->getData(true);
            (...)
        }                
    } else {
   (...)
#13614 report it
realtebo at 2013/06/11 10:56am
How to FORCE using enclousure ?

We need to have IN EVERY FIELD used the closure ( ".." or '..' )

We tried this extension only to do this, because native fputcsv only uses enclosure if it 'think' it's needed.

How to force ?!

#13176 report it
aquasite.pl at 2013/05/11 05:01am
StripNewLines

Hi,

thanks for the extension.

My csv files won't import corectly so I changed:

$value = str_replace("\r\n"," ", $value);
//To
$value = str_replace(array("\n", "\r")," ", $value);
//in 
function lambdaFail();
#12899 report it
02xK at 2013/04/19 06:22am
Add some magic
foreach ($this->includeAttributes as $attr) {
    $row[$attr] = $model->{$attr};
}

Maybe add this at line 58 and a public array $includeAttributes to support magic component properties. e.g. to call

$user->getDisplayName(); // $csv->includeAttributes = array('displayName');
#12081 report it
nsbucky at 2013/02/26 01:41pm
try model relations

just noticed a bug with implementing the code mentioned below, download new version and then this should work.

$csv = new ECSVExport($criteria);              
$csv->dontConvertProvider();
// whatever columns you want
$csv->setModelRelations(array('subid'=>'name','campaign'=>'name')); 
// OR $subid->name, $subid->value, $subid->foo, $subid->bar etc.
$csv->setModelRelations(array('subid'=>array('name','value','foo','bar')));
#12080 report it
deez at 2013/02/26 01:22pm
Help with relations

Hey guys, I really like this extension but how do I get related columns into my $csv variable from a CActiveDataProvider?

I am trying to apply code to the search function that saves the filtered CActiveDataProvider in SESSION then use that SESSION variable as the datasource for the CSV.

I see you say you cant use with() unless you call dontConvertProvider(), however that seems to turn off my select and still not use my with().

Any help would be greatly appreciated.

$criteria->with = array(
            'category'=>array(
                'select'=>'category',  
                'together'=>true,               
            ),
            'location'=>array(
                'select'=>'zip_id, street1, unit',
                'together'=>true
            ),
            'location.zip'=>array(
                'select'=>'city, state',
                'together'=>true
            ),
            'post'=>array(
                'select'=>'usr_name, phone_num',
                'together'=>true
            ),
        );      
        $criteria->select=array('submitted', 'status','ordered_by', 'comments', 'rep');
#9578 report it
nsbucky at 2012/08/24 09:22am
sorry!

what a careless error on my part, fixed in the download

#9577 report it
Binc at 2012/08/24 08:22am
Letters mixed up

Hi, thanks for the great work.

It took me a few minutes to work out why the extension didn't want to load. You switched the 'C' and 'S' in the filenames of the latest releases...easy to overlook.

#9307 report it
nsbucky at 2012/08/02 02:37pm
fixed

added fix, thanks for bug report

#9302 report it
lunatic77 at 2012/08/02 01:44pm
1-off bug and non-variable passed by reference error

Great plugin, thank you! I have noticed/fixed a couple of bugs:

  1. I was getting column headers included twice in my output and it was iterating too many times. I changed the for loop on line 430 to start with $i = 1 instead of 0.
  2. Line 433 emitted an error about a non-variable being passed by reference. So I stored the getData() result in a variable then passed it to _loopRows().

Hope that helps.

#8847 report it
nsbucky at 2012/07/03 09:26am
fix coming

I have a fix coming, but until I can upload it (on my phone right now) I just changed the description. Should be up today once I test and verify it.

#8846 report it
marcovtwout at 2012/07/03 07:36am
Requirement PHP 5.3

Why exactly is PHP 5.3 required? Keeping the requirements as low as possible or similar to current Yii version seems pretty important to me. For example, our production environment (and probably many others) still runs on 5.2.

Leave a comment

Please to leave your comment.

Create extension