Categories

Symfony2 : Export Data to a File in CSV Format

Data extraction from a database is one of the most recurring tasks for a developer. More attention is required when the amount of data is quite large.

In Symfony2, Doctrine is generally used to interact with the database to select, update, or delete data. However, when there is the need to import or export a large amount of data, the use of Doctrine is not recommended in such treatments.

In this article I will present another way of exporting data from a database to a CSV file.

We will not use an ORM because we will have to retrieve all the data from database before the export. To optimize the export, we will use the StreamedResponse class from the HttpFoundation Component that will generate the file.

In addition, the file will not be saved on the disk.

To begin, let’s create our ExportController. To make the controller reusable and independent of the Symfony’s DI container, we will not extend the Controller class from FrameworkBundle, and we will specify manually all the class dependencies.

<?php
namespace Acme\Bundle\ExportBundle\Controller\ExportController;

use Symfony\Component\HttpFoundation\StreamedResponse;
use Doctrine\DBAL\Connection;

class ExportController
{
    private $connection;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    public function generateCsvAction()
    {
       // This method will contain the CSV exporting logic
    }
}

Thereafter, in our Controller let’s create the function that will generate our CSV file. For that, we will use the php function: fputcsv.

public function generateCsvAction()
{
    $response = new StreamedResponse();
    $response->setCallback(function() {
        $handle = fopen('php://output', 'w+');

        // Add the header of the CSV file
        fputcsv($handle, array('Name', 'Surname', 'Age', 'Sex'),';');
        // Query data from database
        $results = $this->connection->query("Replace this with your query");
        // Add the data queried from database
        while($row = $results->fetch()) {
            fputcsv(
                $handle, // The file pointer
                array($row['name'], $row['surname'], $row['age'], $row['sex']), // The fields
                ';' // The delimiter
            );
        }

        fclose($handle);
    });

    $response->setStatusCode(200);
    $response->headers->set('Content-Type', 'text/csv; charset=utf-8');
    $response->headers->set('Content-Disposition', 'attachment; filename="export.csv"');

    return $response;
}

First we create the streamed response object and we add the callback function that will fill out our response object with content. For the connection to database, we use the Doctrine DBAL Connection, the instance of which is injected into our controller.

This code generates a CSV file pretty fast and does not cause any memory problems.

Tags:
  • Jelmer
    Posted at 16:28h, 10 June Reply

    There is an syntax error
    fclose($handle);
    }
    should be
    fclose($handle);
    });

  • Generate and stream a .csv file in Symfony2 - BlogoSfera
    Posted at 18:45h, 24 July Reply

    […] (Kudos to Furculita Alexandru for publishing his/her solution to streaming a CSV file.) […]

  • Hamza Boudhrioua
    Posted at 09:50h, 15 September Reply

    How can i merge csv (excel) cells with symfony2 ?

  • Michael Emerson
    Posted at 13:00h, 24 October Reply

    I’ve implemented the code, but I get the following error:

    Catchable Fatal Error: Argument 1 passed to AppBundle\Controller\ExportController::__construct() must be an instance of Doctrine\DBAL\Connection, none given

    Even though the use statements are correct and it finds the class. Is there something else I need to do that isn’t documented here?

    • marwa
      Posted at 16:07h, 30 January Reply

      hello , did you fixed the error ?

  • keerthivasan
    Posted at 08:59h, 16 December Reply

    this is not working

  • marwa
    Posted at 16:07h, 30 January Reply

    this is not working, Catchable Fatal Error: Argument 1 passed to AppBundle\Controller\ExportController::__construct() must be an instance of Doctrine\DBAL\Connection

  • nountchok
    Posted at 10:50h, 06 June Reply

    i use this and it worked for me:

    $connection = $this->get(‘database_connection’);

    and use this instead of $this->connection

  • nountchok
    Posted at 10:51h, 06 June Reply

    and you have to extend te Controller class of symfony.
    i hope it will help you

  • David
    Posted at 17:51h, 04 July Reply

    Very good article, but there’s a problem when you get a large csv file, the stream does not download all query rows.
    There is the chunk methot In Laravel (but only in Laravel): https://medium.com/@barryvdh/streaming-large-csv-files-with-laravel-chunked-queries-4158e484a5a2

    Actually I can’t stream a large csv file using the Symfony queryBuilder.

    A possible solution is using Batch processing: http://doctrine-orm.readthedocs.io/en/latest/reference/batch-processing.html#iterating-results but there’s a note: “Iterating results is not possible with queries that fetch-join a collection-valued association. The nature of such SQL result sets is not suitable for incremental hydration.”

    Any suggestions are welcome

Post A Comment