Large CSV Export

Sometimes you need to export a large amount of data from your database.  Obviously, if you are going to accumulate all data in an array and then write it to a csv file, you will eventually run out of memory.  A better solution is to use streams.  Here is how you can export data from database into a csv file using Eloquent.

First we open a file to write to and query orders from database chunking them in 500s. Using callback function, we write those records to csv file. Headers array contains key – value pairs of the column names and the way we want the to appear in csv. It acts as a filter for the columns we want to export.

$tmpFile = fopen('tmp_export_'. time() .'.csv', w+);
$orders = $query->with('orderItems', 'fulfillments')->chunk(500, function ($orders) use (&$tmpFile) {
	$ordersAndStuff=$this->getItems($orders);
	\Log::info("Writing to csv file ".$orders->count()." order batch");
	foreach($ordersAndStuff as $order){
	$dataArray=[];
	foreach($this->getHeadersArray() as $key=>$value){
	$dataArray[]=$order->$key;
	}
	fputcsv($tmpFile, $dataArray);
	}
});
rewind($tmpFile);

After we have written to the resource file, we rewind the resource, because now we want to push it to Amazon (or other) cloud storage. This is why initially we opened the file for both reading and writing.

//try catch destroy pending record return null.
try{
	$updatedRecord=$this->saveToStorage($tmpFile, $pendingRecord);
        $message = "Records were exported successfully";
}catch(\Exception$e){
	$pendingRecord->destroy();
	\Log::info($e->getMessage());
        $message = "Error exporting records";
}

protected function saveToStorage(&$resource, $pendingRecord)
{
	$csvHandler=new CsvHandler;
	$record=$csvHandler->save($resource,$this->accountId,'orders',$this->request, $pendingRecord);
	return $record;
}

Earlier in the code (this is not necessary) an export record was created with the status pending. If an operation of saving file to a remote storage fails, we simply erase pending record (or we can give it status ‘failed’). CsvHandler class takes care of saving our export file to AWS. It uses stream to do so. That is why we are passing the resource we created by reference.

$exportMessage=new \stdClass;
$exportMessage->entity='orders';
$exportMessage->accountId=$this->accountId;
$exportMessage->message = $message;
event(new ExportComplete($exportMessage));
fclose($tmpFile);
unlink($tmpFilePath);
return $exportMessage;

The above part of code is doing clean up by creating an export message object. Broadcasting export complete event and returning export message class.