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.


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


        $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

  2. 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');
                ->where('j.isPadawan = true')
            return $qb->getQuery();
  3. 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.

  4. 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
    foreach($results as $row){
        $jedi = $row[0];
        echo $jedi->getName();
  5. This line causes exception for me:

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


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

  6. Hi,

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


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

  8. 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(
  9. 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?

  10. 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”‘);


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

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

  12. 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’)
    ->where(‘s.id = :surveyId’)->setParameter(‘surveyId’, $survey->getId())
    ->andWhere(‘r.uploaded_date BETWEEN :fromDate AND :toDate’)->setParameter(‘fromDate’, $fromDate)
    ->setParameter(‘toDate’, $toDate)
    // 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

    // inside Report entity
    * @return array
    public function getCVSTitles($questionColumns)
    $reply = array(
    return array_merge(
    * @return array
    public function toArray($questionColumns)
    $responses = array();
    foreach ($questionColumns as $key) {
    $responses[$key] = “”;
    /** @var ConsumerResponse $response */
    foreach($this->getCResponses() as $response){
    = $response->getResponse();
    = $response->getResponse();
    $reply = array(
    return array_merge(

    I will really appreciate help in this. Thanks

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