Export data to a csv file with Symfony

Share Button

Lire la version française
I recently had to export a huge set of data to a csv file. This is easy and fast to do if you don’t care about memory and User Experience. I wanted the memory consumption does not increase with the volume of data.

I got inspiration from this post (in French) but, in spite of what is written in the post, some tests with the memory_get_usage function proved that the memory consumption increased quickly with the number of datas.

That’s why I improved the script by using the StreamedResponse class. This solution has also the advantage of generating/downloading the file little by little so that the user doesn’t have to wait the full process.

Handle a huge set of data, export data, a low memory consumption and a file generated on the fly, objective accomplished.

<?php

namespace Obtao\AcmeBundle\Controller;

use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
use Symfony\Bundle\FrameworkBundle\Controller\Controller
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\StreamedResponse;

class AcmeController extends Controller
{
    /**
     * @Route("/export-jedis", name="export_jedi")
     * @Template()
     */
    public function exportAction()
    {
        // get the service container to pass to the closure
        $container = $this->container;
        $response = new StreamedResponse(function() use($container) {

            $em = $container->get('doctrine')->getManager();

            // The getExportQuery method returns a query that is used to retrieve
            // all the objects (lines of your csv file) you need. The iterate method
            // is used to limit the memory consumption
            $results = $em->getRepository('ObtaoAcmeBundle:Jedi')->getExportQuery()->iterate();
            $handle = fopen('php://output', 'r+');

            while (false !== ($row = $results->next())) {
                // add a line in the csv file. You need to implement a toArray() method
                // to transform your object into an array
                fputcsv($handle, $row[0]->toArray());
                // used to limit the memory consumption
                $em->detach($row[0]);
            }

            fclose($handle);
        });

        $response->headers->set('Content-Type', 'application/force-download');
        $response->headers->set('Content-Disposition','attachment; filename="export.csv"');

        return $response;
    }

Share Button

17 thoughts on “Export data to a csv file with Symfony

  1. Where can I find the contents of getExportQuery()->iterate()? So I get an idea of what you’re doing inside, in order to understand better if I ever have to implement it? Thanks

    • Hi,

      The getExportQuery() function just returns a QueryBuilder. For example :

          // in the Obtao\AcmeBundle\Repository\JediRepository.php class
      
          public function getExportQuery()
          {
              $qb = $this->createQueryBuilder('j');
              $qb
                  ->where('j.isPadawan = true')
              ;
      
              return $qb->getQuery();
          }
      
  2. Hello,

    Can u please send the code used for iterate() method of getExportQuery() used in the following line:
    $results = $em->getRepository(‘ObtaoAcmeBundle:Jedi’)->getExportQuery()->iterate();

    If it is some built-in function then please let me know, how to include this function in my controller.

    • Hi,
      The iterate() is a native Doctrine method. The getExportQuery() is my custom method that just returns a QueryBuilder. You can find an example of code in a comment above.
      If you do not need particular query (no conditions, no sorting, etc…), you can just call the iterate on a default query, for example :

      // in a controller
      $results = $this
          ->get('doctrine')
          ->getManager()
          ->getRepository('ObtaoAcmeBundle:Jedi')
          ->createQueryBuilder('j')
          ->getQuery()
          ->iterate()
      ;
      
      foreach($results as $row){
          $jedi = $row[0];
          echo $jedi->getName();
      }
      
  3. This line causes exception for me:

    $response = new \StreamedResponse(function () use ($container) {

    }

    FatalErrorException: Error: Class ‘StreamedResponse’ not found in …
    -

    • Hi,

      You must remove the “\” before StreamedResponse(
      The class used here is : Symfony\Component\HttpFoundation\StreamedResponse;
      (In the use section of our class)

      Francois

  4. I know this is an old topic, but can you provide more detail about the toArray() method you use? Where should it be implemented?

    • Hi, the toArray() method is just a method to transform your Jedi object into the array of data your want to export. Here is an example :

      // in Obtao/AcmeBundle/Entity/Jedi.php
      
      public function toArray()
      {
          return array(
              $this->name,
              $this->firstname,
              $this->master->getName(),
              $this->age,
              $this->lightsaberColor,
          );
      }
  5. Hi, I am using Symfony version 2.6, but the response was always returns “File Not Found”. Is there something wrong? I do not need to create a “twig template” because the returned value is the response, right?

    • Hi, I’m not sure because I didn’t test it, but I’ve found this in the Symfony doc. Maybe a clue…
      But I think it should work. As you says, you do not need any template since a Symfony controller just expect the response to be an instance of Response.

  6. If you have an error, use sendContent() in order to show an error:

    $response->headers->set(‘Content-Type’, ‘application/force-download’);
    $response->headers->set(‘Content-Disposition’,'attachment; filename=”export.csv”‘);

    $response->sendContent();

    return $response;
    When you have fixed the error, go back to use send() instead of sendContent

  7. Hi.

    Thanks for the great post. Really helpful except for a persistent problem I have. The querybuilder’s getArrayResults returns all Report entities in my database but $queryBuilder->getQuery()->iterate(); only gets part of the Report entities thus giving an incomplete csv file.

    My entities have collection of ReportResponse entities in them and a Survey Entity that has many Question Entities mapped to it.

    What could be my error?

    //Inside streamed response function
    $queryBuilder = $em->getRepository(‘ConsumerDataBundle:KE\FollowUpReport’)->createQueryBuilder(‘r’)
    ->select(‘r’)
    ->innerJoin(‘r.survey’,'s’)
    ->innerJoin(‘r.user’,'u’)
    ->innerJoin(‘r.consumer’,'c’)
    ->leftJoin(‘c.area’,'a’)
    ->leftJoin(‘c.market’,'m’)
    ->leftJoin(‘c.estate’,'e’)
    ->where(‘s.id = :surveyId’)->setParameter(‘surveyId’, $survey->getId())
    ->andWhere(‘r.uploaded_date BETWEEN :fromDate AND :toDate’)->setParameter(‘fromDate’, $fromDate)
    ->setParameter(‘toDate’, $toDate)
    ->orderBy(‘r.uploaded_date’,'DESC’)
    ->addOrderBy(‘c.name’,'ASC’)
    ;
    // The getExportQuery method returns a query that is used to retrieve
    // all the objects (lines of your csv file) you need. The iterate method
    // is used to limit the memory consumption
    $results = $queryBuilder->getQuery()->iterate();
    $handle = fopen(‘php://output’, ‘r+’);

    $first = true;
    while (false !== ($row = $results->next())) {
    // add a line in the csv file. You need to implement a toArray() method
    // to transform your object into an array
    if($first == true){
    fputcsv($handle, $row[0]->getCVSTitles(array_values($questionColumns)));
    $first = false;
    }
    fputcsv($handle, $row[0]->toArray(array_keys($questionColumns)));
    // used to limit the memory consumption
    $em->detach($row[0]);
    }

    // inside Report entity
    /**
    * @return array
    */
    public function getCVSTitles($questionColumns)
    {
    $reply = array(
    ‘id’,'name’,'gender’,'age’,'phone’,'area’,
    ‘estate’,'market’,'eligible_for_sample’,
    ‘consumer_sampled_date’,
    ‘report_date’,
    ‘uploaded_date’,
    ‘BA’
    );
    return array_merge(
    $reply,
    $questionColumns
    );
    }
    /**
    * @return array
    */
    public function toArray($questionColumns)
    {
    $responses = array();
    foreach ($questionColumns as $key) {
    $responses[$key] = “”;
    }
    /** @var ConsumerResponse $response */
    foreach($this->getCResponses() as $response){
    if($response->getQuestion()->getQuestionType()==SupportedQuestionTypes::MULTIPLE_OPTION){
    $responses['q'.$response->getQuestion()->getId().'o'.$response->getQOption()->getId()]
    = $response->getResponse();
    }else{
    $responses['q'.$response->getQuestion()->getId()]
    = $response->getResponse();
    }
    }
    $reply = array(
    ‘id’=>$this->getId(),
    ‘name’=>$this->consumer->getName(),
    ‘gender’=>$this->consumer->getGender(),
    ‘age’=>$this->consumer->getAge(),
    ‘phone’=>$this->consumer->getPhone(),
    ‘area’=>$this->consumer->getArea()?$this->consumer->getArea()->getName():”",
    ‘estate’=>$this->consumer->getEstate()?$this->consumer->getEstate()->getName():”",
    ‘market’=>$this->consumer->getMarket()?$this->consumer->getMarket()->getName():”",
    ‘eligible_for_sample’=>$this->isEligible()?”YES”:”NO”,
    ‘consumer_sampled_date’=>$this->consumer->getCreatedAt()->format(‘Y-m-d’),
    ‘report_sampled_date’=>$this->getCreatedAt()->format(‘Y-m-d’),
    ‘report_uploaded_date’=>$this->getUploadedAt()->format(‘Y-m-d’),
    ‘BA’=>$this->getUser()->getName()
    );
    return array_merge(
    $reply,
    $responses
    );
    }

    I will really appreciate help in this. Thanks

    • Hi,

      If I understand your question, you only have to change your select in the QueryBuilder to add your other entities.
      Something like that : ->select(‘r’, ‘s’, ‘c’) (to also return the Survey and Consumer information).

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Protected by WP Anti Spam