csvexport Follow @yiiframework

Create CSV string or write CSV to file from record set

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.

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

#13176

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

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

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

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

nsbucky at 2012/08/24 09:22am

sorry!

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

#9577

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

nsbucky at 2012/08/02 02:37pm

fixed

added fix, thanks for bug report

#9302

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:

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.
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

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

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.

#8765

nsbucky at 2012/06/25 09:39am

thanks for bug fix

I fixed that bug, and added the file output documentation here on this page, it was in the header on the actual file, but I had removed it by accident when I updated this page, thanks for pointing that out.

As for extending from CComponent, other than being able to set public properties, I don't see what advantages there would be. If you can think of something let me know. The other problem I have with just extending from CComponent is that you can't really restrict what those public properties are set to, ie; the setCallback function, which makes sure that you are only setting a callable function. You could check later when it is called, but I find it easier to debug if it fails right when you try to set it.

#8759

marcovtwout at 2012/06/25 06:47am

How to send csv output to browser

And please include something like this in your documentation:

(..)
$content = $csv->toCSV();
Yii::app()->getRequest()->sendFile('export.csv', $content, "text/csv", false);
exit; // note that calling exit does not process log files

#8758

marcovtwout at 2012/06/25 06:27am

Bugreport and quick idea

Bug In 0.5, Exlude is not implemented properly. It correctly excludes the headers, but not the rows. Replace the incorrect code in _writeRow with this:

// remove excluded
if(count($this->_exclude) > 0) {
    foreach($this->_exclude as $e) {
        if(array_key_exists($e, $row)) {
            unset($row[$e]);
        }
    }
}

Additional idea You could extend from CComponent so we can do $csv->headers = array(), $csv->exclude = array(), etc.

#8615

nsbucky at 2012/06/14 09:21am

added new class

old class is still available to download, but I suggest upgrading to the new one, its alot faster, uses less memory, has some more options.

#8586

mariusn at 2012/06/13 02:42am

Re: rewrite

Thumbs up for the rewrite!

Large recordsets are now being handled by passing in a CDbCommand rather than a CSqlDataProvider [i.e. a QueryAll()].

#8581

nsbucky at 2012/06/12 06:58pm

new version

there is a new version in devel for anyone who is interested: http://thebusypixel.com/ECSVExport.zip

I will post as official after I finish more tests, as this new version supports passing CDbcommands and activerecord resultsets.

#8580

nsbucky at 2012/06/12 03:59pm

rewrite

ok im working on a rewrite right now that will help you with large record sets, I just have to finish up the testing today and should have it this week. do you have some sample data sets I can use to test against?

#8577

mariusn at 2012/06/12 11:45am

Out of Memory | 38k Records

I'm using:

$provider = Yii::app()->db->createCommand('SELECT * ....')->QueryAll();

#8576

nsbucky at 2012/06/12 11:29am

38k records

are you pulling back active records or just from csql?

#8574

mariusn at 2012/06/12 11:07am

Out of Memory

First, thanks for the great extension!

I've got a problem where the results from a queryall is too large to deal with in one go (38k+ records). Would it be difficult to rejig the extension to do some kind of paging and perhaps write the file in stages?

Please login to leave your comment.

csvexport Follow @yiiframework

Requirements ¶

Usage ¶

Total 20 comments

Leave a comment

Yii Supporters