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

Post A Comment