Amitav Roy

Blog on web and travel

Creating CSV output from database query result in Laravel 4

Posted on 4 Sept 2014 by Amitav Roy

Creating CSV output from database query result in Laravel 4

Generating a CSV file from some records in the database is a very common requirement which a web developer will come across. It will be nice and handy to have a generic function which will take some basic parameters and generate this for us. In this tutorial I will show how we can write a function inside a common utility class which will take a necessary parameters and generate a CSV file output.

The reason

During development, a lot of time we need to provide client with some CSV report of some data coming from the server. And because this is a very common requirement, it is wise to have a utility function which we can put in any project which will do this for us. In this tutorial I will show how we can convert a simple database query object of Laravel into a CSV file ready for download.

The packages

To start with, I have created a workbench package called Utils where I have written the code. The Files class is where I have a method convertToCSV which is taking two parameters – first one being the data and the second one being array of options.

<?php namespace Amitavroy\Utils; use Illuminate\Support\Facades\Response; class Files { function convertToCSV($data, $options) { // setting the csv header if (is_array($options) && isset($options['headers']) && is_array($options['headers'])) { $headers = $options['headers']; } else { $headers = array( 'Content-Type' => 'text/csv',
          'Content-Disposition' => 'attachment; filename="ExportFileName.csv"'
      );
    }
      
    $output = '';
      
    // setting the first row of the csv if provided in options array
    if (isset($options['firstRow']) && is_array($options['firstRow'])) {
      $output .= implode(',', $options['firstRow']);
      $output .= "\n"; // new line after the first line
    }
      
    // setting the columns for the csv. if columns provided, then fetching the or else object keys
    if (isset($options['columns']) && is_array($options['columns'])) {
      $columns = $options['columns'];
    } else {
      $objectKeys = get_object_vars($data[0]);
      $columns = array_keys($objectKeys);
    }
      
    // populating the main output string
    foreach ($data as $row) {
      foreach ($columns as $column) {
        $output .= str_replace(',', ';', $row->$column);
        $output .= ',';
      }
      $output .= "\n";
    }
      
    // calling the Response class make function inside my class to send the response.
    // if our class is not a controller, this is required.
    return Response::make($output, 200, $headers);
  }
}

Code on Github

This class doesn’t extend any other class, but we are using Facade of Response using “use Illuminate\Support\Facades\Response;” to return a CSV output. If we don’t do this, then we have to do a return at the controller level which will mean that this function is not a simple plug and play. Oh, and in my code example, I have not made this a static method; but we can always make this a static method and call it without creating a new instance of the class and I am sure it will be faster.

And for this example, I have created two simple routes: one where we have just a link; clicking on which will start downloading of the CSV file. And the other is the actual page where all the data is actually getting converted into a CSV file. For demonstration purpose, I have written some queries inside the controller, but ideally that part of the code should go inside the model. But to deal with less files in the tutorial, I have taken this shortcut.

public function getCSVLink()
{
  // setting the fields that I want to select
  $arrSelectFields = array(
      'c.ContactName', '.ContactTitle', 'c.Address', 'c.City', 'c.Country', 'c.Phone',
      'o.OrderDate', 'o.OrderId'
  );
     
  // query
  $query = DB::table('Customers as c');
  $query->select($arrSelectFields);
  $query->join('Orders as o', 'c.CustomerID', '=', 'o.CustomerId', 'left');
  $data = $query->get(); // fetched data
     
  // passing the columns which I want from the result set. Useful when we have not selected required fields
  $arrColumns = array('OrderId', 'ContactName', 'ContactTitle', 'Address', 'City', 'Country', 'Phone', 'OrderDate');
     
  // define the first row which will come as the first row in the csv
  $arrFirstRow = array('Order Id', 'Contact Name', 'Contact Title', 'Address', 'City', 'Country', 'Phone', 'Order Date');
     
  // building the options array
  $options = array(
      'columns' => $arrColumns,
      'firstRow' => $arrFirstRow,
  );
  
  // creating the Files object from the Utility package.
  $Files = new Files;
    
  return $Files->convertToCSV($data, $options);
}

Code on Github

You can also download the branch for the complete code. But remember that if you check out this branch, then after your composer update, you will need to do a composer dumpautoload inside the workbench/amitavroy/utils folder.